If there are 5 consecutive dates, return a True. Otherwise, returns a False.
This one would return a True.
This one would return a False. It is missing 09-04-2018
5 consecutive dates of what ? there must be some sort of grouping or delimiter for these data groups?
(SELECT MyID, MyDate
, (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
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'
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 (
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
SELECT CASE WHEN SUM(DATEDIFF(DAY, MyDateMin, MyDate)) = 10 THEN 'True' ELSE 'False' END AS are_dates_consecutive
CROSS JOIN min_dates
As @yosiasz wrote, it would be benefitial to know, what these data represent.
Would this qualify as 5 consecutive dates (true)?
If so, this could be close to what you want:
when count(distinct b.mydate)=5
end as result
from yourtable as a
inner join yourtable as b
group by a.mydate
order by result desc
Btw: I assume mydate field is of type "date".
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.