question

katie 2 avatar image
katie 2 asked

figure out how to flag "worked 7 consecutive days" in t sql

I have a bunch of time punch records and need to figure out if a person worked 7 consecutive days where the week starts on Monday and ends on Sunday.

data looks like this...

name, date_worked, start_time1,end_time1, start_time2, end_time2, day_worked , total_hours_worked

day_worked and dateworked are filled in whether the person worked or not, so i have all records flagged as 1 if they have a start_time1 not null or total_hours_worked>0 (has_punch_for_day). a person can have total_hours_worked>0 and not have start/end times.

day_worked= "Monday", "Tuesday",...etc. dateworked is datetime.

I have tried using the row_number() over (partition by day_worked order by has_punch_for_day) but that just gives the count of the day for each punch on that day.

I just can seem to think a good way of doing this.

Any help would be greatly appreciated!! :)

t-sqldatetimetime
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image Squirrel 1 commented ·
can you post some sample data and the expected output ?
0 Likes 0 ·

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered

You should use a UserID instead of the Name as this will not work if two employees have the same name

CREATE TABLE TimePunch(            
    [Name] [varchar](50),            
    [Date_Worked] [datetime],            
    [Start_Time1] [datetime] NULL,            
    [End_Time1] [datetime] NULL,            
    [Start_Time2] [datetime] NULL,            
    [End_Time2] [datetime] NULL,            
    [Day_Worked] [varchar](50),            
    [Total_Hours_Worked] [decimal](4, 2),            
    [HAS_Punch_For_Day] [int])            
            
INSERT TimePunch            
SELECT 'Katie','2010-02-19','2010-02-19 08:00:00','2010-02-19 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-19'),8,1 UNION ALL            
SELECT 'Katie','2010-02-20',NULL,NULL,NULL,NULL,DATENAME(dw,'2010-02-20'),0,0 UNION ALL            
SELECT 'Katie','2010-02-21',NULL,NULL,NULL,NULL,DATENAME(dw,'2010-02-21'),0,0 UNION ALL            
SELECT 'Katie','2010-02-22',NULL,NULL,NULL,NULL,DATENAME(dw,'2010-02-22'),8,1 UNION ALL            
SELECT 'Katie','2010-02-23','2010-02-23 08:00:00','2010-02-23 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-23'),8,1 UNION ALL            
SELECT 'Bob','2010-02-24','2010-02-24 08:00:00','2010-02-24 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-24'),8,1 UNION ALL            
SELECT 'Katie','2010-02-24','2010-02-24 08:00:00','2010-02-24 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-24'),8,1 UNION ALL            
SELECT 'Stu','2010-02-25','2010-02-25 08:00:00','2010-02-25 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-25'),8,1 UNION ALL            
SELECT 'Katie','2010-02-25','2010-02-25 08:00:00','2010-02-25 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-25'),8,1 UNION ALL            
SELECT 'Katie','2010-02-26','2010-02-26 08:00:00','2010-02-26 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-26'),8,1 UNION ALL            
SELECT 'Katie','2010-02-27','2010-02-27 08:00:00','2010-02-27 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-27'),8,1 UNION ALL            
SELECT 'Katie','2010-02-28','2010-02-28 08:00:00','2010-02-28 16:00:00',NULL,NULL,DATENAME(dw,'2010-02-28'),8,1 UNION ALL            
SELECT 'Katie','2010-03-01','2010-03-01 08:00:00','2010-03-01 16:00:00',NULL,NULL,DATENAME(dw,'2010-03-01'),8,1 UNION ALL            
SELECT 'Bob','2010-03-01','2010-03-01 08:00:00','2010-03-01 16:00:00',NULL,NULL,DATENAME(dw,'2010-03-01'),8,1 UNION ALL            
SELECT 'Katie','2010-03-02',NULL,NULL,NULL,NULL,DATENAME(dw,'2010-03-02'),0,0            
go            
CREATE FUNCTION dbo.GetXConsecutiveWorkDays(@NumberOfDays int, @Name varchar(50))            
RETURNS @t TABLE (            
    [Name] [varchar](50),            
    [Date_Worked] [datetime],            
    [ConsecutiveDays] [int])            
AS            
BEGIN            
DECLARE @StartDate datetime            
SELECT @StartDate = DATEADD(d,-1,MIN(Date_Worked)) FROM TimePunch            
            
;WITH dates AS            
(            
SELECT TOP ( 54321 )            
DATEADD(d,ROW_NUMBER() OVER (ORDER BY c1.column_id) ,@StartDate) [day]            
FROM master.sys.all_columns c1            
CROSS JOIN master.sys.all_columns c2            
)            
,cte1 AS            
(            
SELECT *,r = ROW_NUMBER() OVER (order by [name],d.[day])            
FROM dates d            
LEFT JOIN TimePunch tp ON (CONVERT(datetime,tp.Date_Worked,112) = d.[day])            
WHERE [day]>@StartDate and [day] <= GETDATE()            
AND Name = @Name            
)            
            
,cte2 AS            
(            
SELECT             
*,[ConsecutiveDays] = 0,1 [d]            
FROM cte1 WHERE r=1            
UNION ALL            
SELECT cte1.[day] ,cte1.[Name] ,            
    cte1.[Date_Worked],            
    cte1.[Start_Time1] ,            
    cte1.[End_Time1] ,            
    cte1.[Start_Time2],            
    cte1.[End_Time2] ,            
    cte1.[Day_Worked],            
    cte1.[Total_Hours_Worked],            
    cte1.[HAS_Punch_For_Day],            
    cte1.R,            
    ConsecutiveDays + CASE WHEN ISNULL(cte1.HAS_Punch_For_Day,0) = 1 THEN 1 ELSE -ConsecutiveDays END [ConsecutiveDays]            
    ,d + 1            
FROM cte2            
JOIN cte1 on cte1.r=cte2.d            
            
            
)            
INSERT @t            
SELECT Name, Date_Worked, ConsecutiveDays FROM cte2             
WHERE ConsecutiveDays >= @NumberOfDays            
ORDER BY [Name],[Day]            
OPTION (MAXRECURSION  0)            
            
RETURN             
END            
go            
            
            
;WITH cte AS            
(            
SELECT DISTINCT Name FROM TimePunch            
)            
SELECT d.* FROM cte            
CROSS APPLY dbo.GetXConsecutiveWorkDays(7, cte.Name) [d]            
            
DROP TABLE TimePunch            
DROP FUNCTION dbo.GetXConsecutiveWorkDays            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.