用于检查每周是否少于一个日期的存储过程 [英] Stored procedure to check if less then one date entrance per week

问题描述

我正在研究一个存储过程.输入将是日期和 ID，如果有 4 周且每周输入少于 2 次，则程序会将值设置为 true.

I am working on a stored procedure. The input is going to be a date and ID and the procedure is going to set a value to true if there are 4 weeks with less then 2 inputs per week.

在我的数据中，只有带有星星的周的输入少于两个，如果我通过日期 7-7-2015 会将输出值设置为 true

In my data only the weeks with the stars have less than two inputs and if I pass the date 7-7-2015 is going to set the output value to true

任何帮助将不胜感激.如果少于两个输入，我是否需要遍历每条记录并设置一个计数器，或者有更简单的方法?

Any help will be appreciated. Do I need to iterate through every record and set a counter if less then two inputs or there is an easier way ?

ID      Date

1       7-7-2015    
2       6-23-2015
3       6-12-2015
1       7-8-2015
1       7-14-2015    *
1       7-21-2015    *
1       7-27-2015
1       7-28-2015
1       7-29-2015
1       7-30-2015
1       8-3-2015     *
1       8-11-2015    *

推荐答案

可能有更好的方法，但应该可行的一种方法是在派生表中使用窗口聚合函数(计数)，然后对数字求和计数为 1 的行，如果总和 >= 4，则返回 true.像这样:

There might be better ways, but one way that should work is to use a windowed aggregate function (count) in a derived table, and then sum up the number of rows with count 1 and return true if the sum is >= 4. Something like this:

create proc sp_test (@id int, @d date)
as 
select case when sum(c) >= 4 then 'true' else 'false' end status
from (
    select c = count(*) over (partition by id, datepart(week, date))
    from t -- this is your table 
    where id = @id and date >= @d
) a
where c = 1
go

使用:

exec sp_test @id = 1, @d = '2015-07-07'

这篇关于用于检查每周是否少于一个日期的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！