CODEWITHSUNDEEP
sqlsql-server
Odie
Odie

Reputation: 375

Distribute Days Between Date Range

I have a table like such:

LeaveRequestID | DateAllocated    | DateFrom    | DateTo     | TotalLeaveDays
1              |  2020-03-02      | 2020-03-02  | 2020-03-05 |  3
2              |  2020-03-02      | 2020-03-02  | 2020-03-04 |  2

And here's my query:

SELECT 
    t1.DateAllocated,
    t2.DateFrom,
    t2.DateTo,
    DATEDIFF(DAY, t2.DateFrom, t2.DateTo) TotalLeaveDays 
FROM 
    tblLeave_Allocation t1
INNER JOIN 
    tblLeave_Requests t2 ON CONVERT(VARCHAR, t1.DateAllocated, 23) = CONVERT(VARCHAR, t2.DateFrom, 23)

Here is my desired output:

LeaveRequestID  | DateAllocated | DateFrom    | DateTo     | TotalLeaveDays | DateDist   | LeaveCount
1               | 2020-03-02    | 2020-03-02  | 2020-03-05 |    3           | 2020-03-03 | 1
1               | 2020-03-02    | 2020-03-02  | 2020-03-05 |    3           | 2020-03-04 | 1
1               | 2020-03-02    | 2020-03-02  | 2020-03-05 |    3           | 2020-03-05 | 1
2               | 2020-03-02    | 2020-03-02  | 2020-03-04 |    2           | 2020-03-03 | 1
2               | 2020-03-02    | 2020-03-02  | 2020-03-04 |    2           | 2020-03-04 | 1

I would like to distribute every count of single TotalLeaveDays for each count starting from a day after DateFrom until DateTo. How to achieve my desired output?

Upvotes: 0

Views: 60

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81970

Here is another option which uses an ad-hoc tally/numbers table

Example

Select A.* 
      ,DateDist = dateadd(DAY,N,DateFrom)
 From  YourTable A
 Join (
         Select Top 10000 N=Row_Number() Over (Order By (Select NULL)) 
          From  master..spt_values n1, master..spt_values n2
      ) B on N<=TotalLeaveDays
 Order By LeaveRequestID,N

Returns

LeaveRequestID  DateAllocated   DateFrom    DateTo      TotalLeaveDays  DateDist
1               2020-03-02      2020-03-02  2020-03-05  3               2020-03-03
1               2020-03-02      2020-03-02  2020-03-05  3               2020-03-04
1               2020-03-02      2020-03-02  2020-03-05  3               2020-03-05
2               2020-03-02      2020-03-02  2020-03-04  2               2020-03-03
2               2020-03-02      2020-03-02  2020-03-04  2               2020-03-04

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

A recursive CTE seems well suited for this:

with cte as (
      select LeaveRequestID, DateAllocated, DateFrom, DateTo, TotalLeaveDays,
             1 as n, dateadd(day, 1, datefrom) as datedist
      from t
      union all
      select LeaveRequestID, DateAllocated, DateFrom, DateTo, TotalLeaveDays,
             n + 1, dateadd(day, 1, datedist) as datedist
      from cte
      where n < TotalLeaveDays
     )
select LeaveRequestID, DateAllocated, DateFrom, DateTo, TotalLeaveDays, datedist, 1
from cte;

Upvotes: 1

CR7SMS
CR7SMS

Reputation: 2584

You can do this by creating/using a calendar table and then joining it to our current result. In SQL Server, you can use the following steps:

Creating the calendar table (If you already don't have one):

DECLARE @MinDate DATE = '20200101',
        @MaxDate DATE = '20210101';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
INTO #Calendar
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

Creating Sample Current Output:

select a.*
into #tableA
from
(select 1 as LeaveRequestID,CAST('2020-03-02' as date) as DateAllocated,
        CAST('2020-03-02' as date) as DateFrom,CAST('2020-03-05' as date) as DateTo,
        3 as TotalLeaveDays union
 select 2 as LeaveRequestID,CAST('2020-03-02' as date) as DateAllocated,
        CAST('2020-03-02' as date) as DateFrom,CAST('2020-03-04' as date) as DateTo,
        2 as TotalLeaveDays) a

Performing the join to get the desired output:

select a.*,
       b.[date] as DateDist 
from #tableA a
left join #Calendar b
on b.[date] between DATEADD(d,1,a.Datefrom) and a.DateTo

Hope this helps.

Upvotes: 1

Related Questions