SQL DateDiff and transpose Days?

Transact-SQL
1

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.

2

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.

3

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?

4

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
;