If there are 5 consecutive dates, return a True. Otherwise, returns a False.
This one would return a True.
| MyID | MyDate |
|---|---|
| 1 | 09-02-2018 |
| 2 | 09-03-2018 |
| 3 | 09-04-2018 |
| 4 | 09-05-2018 |
| 5 | 09-06-2018 |
This one would return a False. It is missing 09-04-2018
| MyID | MyDate |
|---|---|
| 1 | 09-02-2018 |
| 2 | 09-03-2018 |
| 3 | 09-05-2018 |
| 4 | 09-06-2018 |
| 5 | 09-07-2018 |
5 consecutive dates of what ? there must be some sort of grouping or delimiter for these data groups?
WITH t
AS
(SELECT MyID, MyDate
FROM
(VALUES
(1,'09-02-2018')
, (2,'09-03-2018')
, (3,'09-05-2018')
, (4,'09-06-2018')
, (5,'09-07-2018')) a (MyID, MyDate)
)
, n10 AS (SELECT n FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) b (n))
, Calendar AS(SELECT DateAdd(dd, n10.n, (SELECT Min(t.MyDate) FROM t)) d FROM n10)
SELECT CASE WHEN
EXISTS(
SELECT *
FROM t
RIGHT JOIN Calendar
ON Mydate = d
AND d BETWEEN (SELECT Max(MyDate) FROM t) AND (SELECT Min(MyDate) FROM t)
WHERE t.MyID IS NULL) THEN 'False'
ELSE 'True'
END Consecutive;If 5 dates are consecutive, the total days diff from the smallest date to the largest data will be 10. If not, it won't.
;WITH test_data AS (
SELECT *
FROM (VALUES (1,'09-02-2018'), (2,'09-03-2018'), (3,'09-04-2018'),
(4,'09-05-2018'), (5,'09-06-2018')) td (MyID, MyDate)
),
min_dates AS (
SELECT MIN(MyDate) AS MyDateMin
FROM test_data
)
SELECT CASE WHEN SUM(DATEDIFF(DAY, MyDateMin, MyDate)) = 10 THEN 'True' ELSE 'False' END AS are_dates_consecutive
FROM test_data
CROSS JOIN min_datesAs @yosiasz wrote, it would be benefitial to know, what these data represent.
Would this qualify as 5 consecutive dates (true)?
MyID MyDate
1 09-02-2018
2 09-03-2018
3 09-05-2018
4 09-06-2018
5 09-07-2018
6 09-09-2018
7 09-11-2018
8 09-08-2018
If so, this could be close to what you want:
select top(1)
case
when count(distinct b.mydate)=5
and datediff(day,min(b.mydate),max(b.mydate))=4
then 'true'
else 'false'
end as result
from yourtable as a
inner join yourtable as b
on b.mydate>=a.mydate
and b.mydate<dateadd(day,5,a.mydate)
group by a.mydate
order by result desc
;
Btw: I assume mydate field is of type "date".
Hi yosiasz,
Sorry I did not provide enough information on the initial post. I was trying to think of a way how to explain it but the mind is a bit boggled today. 
So basically, I am tasked to find if an associate has worked overtime. If they work a consecutive amount of days back to back, this will qualifies as overtime.
When I query for an associate for a given week, it will give me all the days that he/she has worked for that week. From there, I need to determine the consecutive numbers which determines if they qualify for overtime.
Like always, thank you to Joe and Scott. Would it be possible to make it dynamic basing on your queries? Again, thank you!
Bitsmed, i will give your query a try. Thank You
sounds like my brain every day. any 7 consecutive days or Sun to Saturday or whatever your work week is?
how do you define overtime 40+ hours etc. Not sure what country you are in and what is involved in a work week where you are from.
Very good questions Yosiasz.
Yes, sunday is the start and saturday is the end. For this OverTime rule, it has to be "N" numbers of days, of course less than 8 days since a week only has 7 days.
I have already been successful in calculating overtime for a daily basis as I have mentioned in another thread called "Calculated Fields", using Case statements. It worked.
so it is not really N number of days but rather 0 to 7 days right?
lets say you live in Tatooine and work week there is 2 days, 16 hours work max. but you work 17 hour in one day, that would be overtime.
Your first question. Correct.
Your second question, I believe so as well.