How would I write a sql query which would calculate consecutive days an employee has worked

I am trying to figure out the best way to write a sql query in order to find out how many consecutive days an employee has worked during a designated timeframe .... The sql table, "TIme"  that I am working with is made up of rows which contain a PayRoll Ending Date Field, "prenddate" as well as fields for each day of the week, "Mon", "Tues", "Weds", etc ...  The day fields contains the number of hours worked on that day.  If the employee has not worked that day then the field contain a zero.  Each employee has a new row of data for each week of the year.  Weekends and holidays are worked and will therefore need to be considered as part of the consecutive days.   Consecutive days can cross weeks in the given timeframe.  Assistance with this would be very much appreciated.
WSITechSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
You could maintain a Time Coordinate table of all days.  Then, doing a left join with that table and the time dimension table finding the date of the first null greater than the first non-null value.

Some additional tweaking would need done in the datediff part to account for the non-work days such as weekends.

This uses table names are make sense logically, not necessarily your table names.
declare @DateStart datetime,
        @DateEnd   datetime,
        @FirstWork datetime
 
set @DateStart = '1/1/2008'
set @DateEnd   = '2/1/2008'
 
select @FirstWork = min(theDate) from employeeTime
where theDate >= @DateStart 
and theDate < @DateEnd
 
select datediff(d, @FirstWork,min(td.TheDate)) as 'ConsecutiveDaysWorked' from TimeDimension td
left outer join employeeTime et
on td.theDate = et.theDate
where td.TheDate >= @FirstWork 

Open in new window

0
BrandonGalderisiCommented:
Something like this.
declare @DateStart datetime,
        @DateEnd   datetime,
        @FirstWork datetime,
        @NotWorked datetime
 
set @DateStart = '1/1/2008'
set @DateEnd   = '2/1/2008'
 
select @FirstWork = min(theDate) from employeeTime
where theDate >= @DateStart 
and theDate < @DateEnd
 
select @NotWorked= min(td.TheDate)) from TimeDimension td
left outer join employeeTime et
on td.theDate = et.theDate
where td.TheDate >= @FirstWork 
 
select count(*) as 'ConsecutiveDays' from timeDimension
where IsWorkDay=1
and theDate >= @FirstWork
and theDate < @NotWorked

Open in new window

0
WSITechSupportAuthor Commented:
Not quite sure if I set the Time Coordinate table up as one would have expected .... but if I use the one that I setup along with the query above, I get "0" consecutive days.  I know that most employees should have at least 5 consecutive days and others should have more.  Any suggestions ....
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BrandonGalderisiCommented:
Upon reading further, you have a endDate and m, t, w, th, f, sa, su columns correct?  If so, you will probably have to pivot the data.  

What about this.. So if your end date is friday...

MORE TO COME
create function dbo.WorkTimePivot (@EmployeeId int, @WeekDate datetime)
returns @work Table (employeeId int, theDate datetime, HoursWorked int)
as
begin
insert into @Work
select employeeId, dateadd(d,+1,@WeekDate),Saturday
from EmployeeTime
where prenddate = @WeekDate
insert into @Work
select employeeId, @WeekDate,Friday
from EmployeeTime
where prenddate = @WeekDate
insert into @Work
select employeeId, dateadd(d,-1,@WeekDate),Thursday
from EmployeeTime
where prenddate = @WeekDate
insert into @Work
select employeeId, dateadd(d,-2,@WeekDate),Wednesday
from EmployeeTime
where prenddate = @WeekDate
insert into @Work
select employeeId, dateadd(d,-3,@WeekDate),Tuesday
from EmployeeTime
where prenddate = @WeekDate
insert into @Work
select employeeId, dateadd(d,-4,@WeekDate),Monday
from EmployeeTime
where prenddate = @WeekDate
insert into @Work
select employeeId, dateadd(d,-5,@WeekDate),Sunday
from EmployeeTime
where prenddate = @WeekDate
 
return
end

Open in new window

0
BrandonGalderisiCommented:
You won't need a time dimension table based on the new information

Try this... it'll be closer I bet
create function dbo.WorkTimePivot (@EmployeeId int
returns @work Table (employeeId int, theDate datetime, HoursWorked int)
as
begin
insert into @Work
select employeeId, dateadd(d,+1,prenddate ),Saturday
from EmployeeTime
 
insert into @Work
select employeeId, prenddate ,Friday
from EmployeeTime
 
insert into @Work
select employeeId, dateadd(d,-1,prenddate ),Thursday
from EmployeeTime
 
insert into @Work
select employeeId, dateadd(d,-2,prenddate ),Wednesday
from EmployeeTime
 
insert into @Work
select employeeId, dateadd(d,-3,prenddate ),Tuesday
from EmployeeTime
 
insert into @Work
select employeeId, dateadd(d,-4,prenddate ),Monday
from EmployeeTime
 
insert into @Work
select employeeId, dateadd(d,-5,prenddate ),Sunday
from EmployeeTime
 
 
return
end
GO
declare @DateStart datetime,
        @DateEnd   datetime,
        @FirstWork datetime,
        @NotWorked datetime
 
set @DateStart = '1/1/2008'
set @DateEnd   = '2/1/2008'
 
select @FirstWork = min(wtp.theDate) from dbo.WorkTimePivot(EmployeeId) wtp
join employeetime ET
on wtp.employeeid = et.employeeid
where wtp.theDate >= @DateStart 
and wtp.theDate < @DateEnd
and hoursWorked>0
 
select @NotWorked= min(wtp.theDate) from dbo.WorkTimePivot(EmployeeId) wtp
join employeetime ET
on wtp.employeeid = et.employeeid
where wtp.theDate > @FirstWork 
and hoursWorked=0
 
select ConsecutiveDays = datediff(d, @FirstWork, @NotWorked)

Open in new window

0
WSITechSupportAuthor Commented:
After making a few modifications to your code in order to use the actual column names and table names that I have, I am getting the following error messages ....

Server: Msg 155, Level 15, State 1, Line 9
'EmpNo' is not a recognized OPTIMIZER LOCK HINTS option.
Server: Msg 155, Level 15, State 1, Line 16
'EmpNo' is not a recognized OPTIMIZER LOCK HINTS option.
Server: Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'in'.


Here is the actual code that I am trying to use ....
create function dbo.WorkTimePivot (@EmpNo int)
returns @work Table (EmpNo int, PREnding datetime, HoursWorked int)
as
begin
insert into @Work
select EmpNo, dateadd(d,-1,PREnding ),HoursSat
from Time
 
insert into @Work
select EmpNo, prending ,HoursSun
from Time
 
insert into @Work
select EmpNo, dateadd(d,-3,PREnding ),HoursThu
from Time
 
insert into @Work
select EmpNo, dateadd(d,-4,PREnding),HoursWed
from Time
 
insert into @Work
select EmpNo, dateadd(d,-5,PREnding ),HoursTue
from Time
 
insert into @Work
select EmpNo, dateadd(d,-6,PREnding ),HoursMon
from Time
 
insert into @Work
select EmpNo, dateadd(d,-2,PREnding ),HoursFri
from Time
 
 
return
end
GO
declare @DateStart datetime,
        @DateEnd   datetime,
        @FirstWork datetime,
        @NotWorked datetime
 
set @DateStart = '1/1/2008'
set @DateEnd   = '2/1/2008'
 
select @FirstWork = min(wtp.PREnding) from dbo.WorkTimePivot(EmpNo) wtp
join Time ET
on wtp.EmpNo = et.EmpNo
where wtp.PREnding >= @DateStart
and wtp.PREnding < @DateEnd
and hoursWorked>0
 
select @NotWorked= min(wtp.theDate) from dbo.WorkTimePivot(EmpNo) wtp
join time ET
on wtp.EmpNo = et.EmpNo
where wtp.prending > @FirstWork
and hoursWorked=0
 
select ConsecutiveDays = datediff(d, @FirstWork, @NotWorked)
Open in New Window Accept Multiple Solutions

Do anyone know why I might be getting the error messages above?
0
BrandonGalderisiCommented:
Try this.
create function dbo.WorkTimePivot ()
returns @work Table (EmpNo int, PREnding datetime, HoursWorked int)
as
begin
insert into @Work
select EmpNo, dateadd(d,-1,PREnding ),HoursSat
from Time
 
insert into @Work
select EmpNo, prending ,HoursSun
from Time
 
insert into @Work
select EmpNo, dateadd(d,-3,PREnding ),HoursThu
from Time
 
insert into @Work
select EmpNo, dateadd(d,-4,PREnding),HoursWed
from Time
 
insert into @Work
select EmpNo, dateadd(d,-5,PREnding ),HoursTue
from Time
 
insert into @Work
select EmpNo, dateadd(d,-6,PREnding ),HoursMon
from Time
 
insert into @Work
select EmpNo, dateadd(d,-2,PREnding ),HoursFri
from Time
 
 
return
end
GO
declare @DateStart datetime,
        @DateEnd   datetime,
        @FirstWork datetime,
        @NotWorked datetime
 
set @DateStart = '1/1/2008'
set @DateEnd   = '2/1/2008'
 
select @FirstWork = min(wtp.PREnding) from dbo.WorkTimePivot() wtp
join Time ET
on wtp.EmpNo = et.EmpNo
where wtp.PREnding >= @DateStart
and wtp.PREnding < @DateEnd
and hoursWorked>0
 
select @NotWorked= min(wtp.theDate) from dbo.WorkTimePivot() wtp
join time ET
on wtp.EmpNo = et.EmpNo
where wtp.prending > @FirstWork
and hoursWorked=0
 
select ConsecutiveDays = datediff(d, @FirstWork, @NotWorked)
Open in New Window Accept Multiple Solutions

Open in new window

0
WSITechSupportAuthor Commented:
I have tried for a couple of days to figure out what is going on but when I run the code above I only get 1 row in the table with "NULL" as the value for the Consecutive Days .... Any ideas?
0
BrandonGalderisiCommented:
Do you want it to return for all employees or just one?

This will return ONE... it'll need tweaked to return all.
/*
For one
*/
declare @DateStart datetime,
        @DateEnd   datetime,
        @FirstWork datetime,
        @NotWorked datetime,
        @EmpNO     int
 
set @DateStart = '1/1/2008'
set @DateEnd   = '2/1/2008'
set @EmpNO     = 1    -- change here
select @FirstWork = min(wtp.PREnding) from dbo.WorkTimePivot() wtp
join Time ET
on wtp.EmpNo = et.EmpNo
and wtp.empNo = @EmpNO
where wtp.PREnding >= @DateStart
and wtp.PREnding < @DateEnd
and hoursWorked>0
 
select @NotWorked= min(wtp.theDate) from dbo.WorkTimePivot() wtp
join time ET
on wtp.EmpNo = et.EmpNo
and wtp.empNo = @EmpNO
where wtp.prending > @FirstWork
and hoursWorked=0
 
select ConsecutiveDays = datediff(d, @FirstWork, @NotWorked)
Open in New Window Accept Multiple Solutions

Open in new window

0
WSITechSupportAuthor Commented:
I haven't had that much time to play around with the code above to see if I can change it a little to make it work but if I tried to use it as mentioned above, I still get a table with one row and one column which contains "NULL" as the value ....
0
BrandonGalderisiCommented:
What happens when you run this?
select * from dbo.WorkTimePivot() 

Open in new window

0
WSITechSupportAuthor Commented:
I get see the employee numbers, the preending date and the hours that they worked for the days in that week ...
0
BrandonGalderisiCommented:
Ok good.  So the pivot function is working.

Try removing the variable assignments from the first query and see if it returns any data.
/*
For one
*/
declare @DateStart datetime,
        @DateEnd   datetime,
        @FirstWork datetime,
        @NotWorked datetime,
        @EmpNO     int
 
set @DateStart = '1/1/2008'
set @DateEnd   = '2/1/2008'
set @EmpNO     = 1    -- change here
 
select min(wtp.PREnding) from dbo.WorkTimePivot() wtp
join Time ET
on wtp.EmpNo = et.EmpNo
and wtp.empNo = @EmpNO
where wtp.PREnding >= @DateStart
and wtp.PREnding < @DateEnd
and hoursWorked>0

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WSITechSupportAuthor Commented:
I am so sorry.  I just realized that I was using the wrong time table.  I have two tables one that stores the current week and one that stores all the history.  I was using the current week when I needed to use the history.  After I changed it to the history table, I was then able to get the consecutive days for different employees.  Thanks so much for your time and help!!!!!
0
WSITechSupportAuthor Commented:
Thanks for your time and help!!!!!! It was much appreciated ....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.