SQLTeam.com | Weblogs | Forums

SQL DateDiff and transpose Days?

I have a table that contains 4 columns EmployeeId, PeriodBeginDate, PeriodEndDate, HoursWorked. I can't change the structure of the table as it is from a packaged Payroll Software application. This table stores only 1 row for Salaried Person for the entire pay period. The data looks like this:

EmployeeId, PeriodBeginDate, PeriodEndDate, HoursWorked
1121 6/27/2016 7/11/2016 80
1304 6/27/2016 7/11/2016 80

What I need to do is get the average number of hours per day and then have the average per day appear on a row for that day. It would ideally look like this:

EmployeeId, PeriodDay, HoursWorked
1121 6/27/2016 8
1121 6/28/2016 8
1121 6/29/2016 8
and so on...

Any ideas on how to something like this? Thinking coalesce, pivot or something but I can't seem to figure out how to get all days from the period range in a row.

You could apply/join a tally table.

But first you'd need to consider whats the max number of days in a periode, and it seems you have a 6 days working week (probably excluding sundays).

Please elaborate.

Bitsmed,

Good point regarding the working week. I would exclude Sat and Sun in this case which I think SQL views as day 6 and 7 if using the dayweek function.

Not sure I understand the tally table comment? I don't want to have to create a table of days in each month or days for a given period, the table that has the data in it already contains the periodbegindate and periodenddate so I'm hoping to derive each numeric date from this range.

Make sense?

Counting days in period 2016-06-27 till 2016-07-11 excluding saturdays and sundays, gives me 11 days, so in your example the 80 hoursworked should result in 11 records with 7.27 (or 7) hoursworked - true or false?

The tally table could be numbers from 1 till (lets say) 60 (almost 2 months), and the calculation could then be done like this:

create table dbo.tally(n int);
go
insert into dbo.tally(n)
 select row_number() over(order by (select null))-1
   from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
              ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
              ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
              ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
              ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
              ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
        ) as tally(n)
;

select a.employeeid
      ,dateadd(day,b.n,a.periodbegindate) as periodday
      ,a.hoursworked*1./count(*) over(partition by a.employeeid,a.periodbegindate) as hoursworked
  from yourtable as a
       cross apply tally as b
 where a.periodenddate>=dateadd(day,b.n,a.periodbegindate)
   and datepart(weekday,dateadd(day,b.n,a.periodbegindate)) in (2,3,4,5,6)
 order by a.employeeid
         ,periodday
;

If you don't want a tally table (hanging around), you could create one on the fly:

with tally(n)
  as (select row_number() over(order by (select null))-1 as n
        from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                   ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                   ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                   ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                   ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                   ,(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
             ) as tally(n)
     )
select a.employeeid
      ,dateadd(day,b.n,a.periodbegindate) as periodday
      ,a.hoursworked*1./count(*) over(partition by a.employeeid,a.periodbegindate) as hoursworked
  from yourtable as a
       cross apply tally as b
 where a.periodenddate>=dateadd(day,b.n,a.periodbegindate)
   and datepart(weekday,dateadd(day,b.n,a.periodbegindate)) in (2,3,4,5,6)
 order by a.employeeid
         ,periodday
;