[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1649
  • Last Modified:

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.
0
WSITechSupport
Asked:
WSITechSupport
  • 8
  • 7
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, 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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now