T-SQL: Create record for each day in datediff

sql-server tsql

101 просмотра

2 ответа

1101 Репутация автора

I have the following table structure (myTable):

EmployeeID, StartDate, EndDate, Hours
---------------------------------------
1           1/1/2016   1/8/2016 20
2           1/4/2016   1/6/2016 10
3           1/2/2016   1/3/2016 13

I need to divide hours by datediff of start and end dates and display a record for each day like so:

1           1/1/2016   1/2/2016 2.85
1           1/2/2016   1/3/2016 2.85
...
1           1/7/2016   1/8/2016 2.85
2           1/4/2016   1/5/2016 5
2           1/5/2016   1/6/2016 5
3           1/2/2016   1/3/2016 13   

Hours should be rounded to two decimal places. Assume that start and end dates are never the same.

How can I do this with T-SQL?

EDIT: I'm not a SQL guru, so I haven't tried much to do this as it didn't look like a simple select. I'm thinking I need to use 'partition by'? For the hours, 2.86 is fine too. Rounding up or down doesn't matter so long as it's consistent.

Also, just for clarification, I don't need 3 rows. I need 10 rows. I don't just need a simple

hours / datediff(day, startdate, enddate)
Автор: Mark13426 Источник Размещён: 19.07.2016 07:04

Ответы (2)


1 плюс

63472 Репутация автора

You have to use a tally table for this:

;WITH Tally AS (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
   FROM (VALUES (0), (0), (0), (0), (0), (0)) t1(v)
   CROSS JOIN (VALUES (0), (0), (0), (0), (0), (0)) t2(v)
)
SELECT t1.EmployeeID,
       DATEADD(d, t3.n-1, t1.StartDate), 
       DATEADD(d, t3.n, t1.StartDate), 
       ROUND([Hours] * 1.0 / t2.days, 2)
FROM mytable AS t1
CROSS APPLY (SELECT DATEDIFF(d, t1.StartDate, t1.EndDate)) As t2(days)
JOIN Tally AS t3 ON t3.n <= t2.days
ORDER BY t1.EmployeeID, n, t1.StartDate

The above query uses a CTE in order to create a tally table with 36 rows. It can easily be extended to create more rows.

Автор: Giorgos Betsos Размещён: 19.07.2016 07:32

1 плюс

14925 Репутация автора

Решение

Unpivoting that daterange can be done by joining to list of numbers.
And master..spt_values can be used for that.

To divide the [hours] by the datediff, it's first casted to a float and then truncated via round to 2 decimals.

select t.EmployeeID,
dateadd(d, v.number, t.StartDate) as StartDate, 
dateadd(d, v.number+1, t.StartDate) as EndDate, 
t.DivHours as [Hours]
from ( 
  select EmployeeID, StartDate, EndDate, 
  round(cast([Hours] as float)/datediff(d, StartDate, EndDate),2,1) as DivHours 
  from myTable
  where EndDate > StartDate 
) t
join master..spt_values v 
  on (v.type='P' and v.number >= 0 and v.number < datediff(d, t.StartDate, t.EndDate));

Gives:

EmployeeID StartDate   EndDate     Hours
1          2016-07-01  2016-07-02  2,85
1          2016-07-02  2016-07-03  2,85
1          2016-07-03  2016-07-04  2,85
1          2016-07-04  2016-07-05  2,85
1          2016-07-05  2016-07-06  2,85
1          2016-07-06  2016-07-07  2,85
1          2016-07-07  2016-07-08  2,85
2          2016-07-04  2016-07-05  5
2          2016-07-05  2016-07-06  5
3          2016-07-02  2016-07-03  13

However, that will only work as long the datediff is below 2047.
Because 2047 is the maximum number you get from that system table.
But that's still a daterange of more than 5 years though.

But if you have bigger ranges in that table.
Then you could just generate a table with lots more numbers.
This example puts 1000000 numbers into a table variable:

DECLARE @Numbers TABLE (num int primary key);

-- Who dares to claim that cross joins are always useless?
WITH d AS (select n from (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9))q(n))
insert into @Numbers (num)
select (d6.n*100000+d5.n*10000+d4.n*1000+d3.n*100+d2.n*10+d1.n) as num 
from d d1, d d2, d d3, d d4, d d5, d d6;

select count(*) as total, min(num) as min_num, max(num) as max_num from @Numbers;

You could also use recursion for this.
But that method is a bit of pain if you want to add additional columns.
To add more columns from myTable you could left join myTable to R on the EmployeeID.

WITH R (EmployeeID, StartDate, EndDate, FinalDate, [Hours]) AS
(
    SELECT EmployeeID, StartDate, dateadd(d, 1, StartDate),
    EndDate as FinalDate,
    round(cast([Hours] as float)/datediff(d, StartDate, EndDate),2,1)
    from myTable
    where StartDate < EndDate
    UNION ALL
    SELECT EmployeeID, dateadd(d, 1, StartDate), dateadd(d, 2, StartDate),
    FinalDate, [Hours]
    FROM R WHERE dateadd(d, 1, StartDate) < FinalDate
)
SELECT EmployeeID, StartDate, EndDate, [Hours]
FROM R
ORDER BY EmployeeID, StartDate, EndDate;

If the total sum of the split hours still needs to equal the original hours?
Then it becomes slighty more complicated.

declare @myTable TABLE (EmployeeID int, StartDate date, EndDate date, [Hours] int);

insert into @myTable values 
(0,'2016-1-1','2016-1-4',10),
(1,'2016-1-1','2016-1-8',20);

WITH R (EmployeeID, StartDate, EndDate, FinalDate, [Hours], RemainingHours) AS
(
    SELECT EmployeeID, 
    StartDate, 
    dateadd(d, 1, StartDate),
    EndDate,
    round(cast([Hours] as float)/datediff(d, StartDate, EndDate),2,1),
    round(cast([Hours] as float),2,1)
    from @myTable
    where StartDate < EndDate
    UNION ALL
    SELECT EmployeeID, 
    dateadd(d, 1, StartDate),
    dateadd(d, 1, EndDate),
    FinalDate,
    (case when dateadd(d, 1, EndDate) < FinalDate then [Hours] else (RemainingHours - [Hours]) end),
    (RemainingHours - [Hours])
    FROM R WHERE EndDate < FinalDate
)
SELECT EmployeeID, StartDate, EndDate, [Hours]
FROM R
ORDER BY EmployeeID, StartDate, EndDate;
Автор: LukStorms Размещён: 19.07.2016 08:04
Вопросы из категории :
32x32