CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Dealing with Date Ranges Crossing Periods

I have a table with a Date Range specified across DateFrom and DateTo fields and a TotalValue field.

Depending on the date range specified, i.e. this:

DECLARE @ReportStartDate datetime = '31 May 2019'
DECLARE @ReportEndDate datetime = '2 Jun 2019'

It would pick up the NULL values, and also the 2019-05-31-2019-06-07 row, but the TotalValue would be (48 / 7) * 2 as it is only 2 days of the range.

Expected Result.

enter image description here

If the below is specified:

DECLARE @ReportStartDate datetime = '04 Jun 2019'
DECLARE @ReportEndDate datetime = '14 Jun 2019'

It would pick up the NULL values, and also the 2019-05-31-2019-06-05 row, but the TotalValue would be (48 / 7) * 3 as it is only 3 days of the range.

Expected Result

enter image description here

Anyone know how this could be approached? I have the following query so far which solves Use Case 1, but I'm not sure how to approach Use Case 2

select 
    *,
    CASE WHEN DateFrom is null Then TotalValue Else (TotalValue/7) * DateDiff(dd,DateFrom,@ReportEndDate) END as CalculatedValue
from 
    #TestData
where DateFrom is null
or DateFrom >=@ReportStartDate

Sample Data

CREATE TABLE #TestData
(
    DateFrom date NULL,
    DateTo date NULL,
    TotalValue money
)
INSERT INTO #TestData
(
    DateFrom,
    DateTo,
    TotalValue
)
SELECT
    NULL,
    NULL,
    250
UNION ALL
SELECT
    '2019-05-31',
    '2019-06-07',
    48
UNION ALL
SELECT
    '2019-05-24',
    '2019-05-31',
    336
UNION ALL
SELECT
    NULL,
    NULL,
    134 
UNION ALL
SELECT
    '2019-04-19',
    '2019-04-26',
    336

select * from #TestData

drop table #TestData

Upvotes: 1

Views: 68

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Use a case expression, datediff and a couple of iif to get the CalculatedValue.

The case expression has two branches:

  1. If the either of the DateFrom or DateTo columns has null instead of a value, you simply return the TotalValue.

  2. This is where it gets interesting: If the date ranges overlap, calculate the number of days they overlap, and then divide the TotalValue by 7 and multiply it by the number of days.

I've added some comments to the code, hope it's clear enough:

DECLARE @ReportStartDate date = '2019-05-31', @ReportEndDate date = '2019-06-02'

SELECT  DateFrom, 
        DateTo, 
        TotalValue,
        CASE 
            -- dates are null
            WHEN DateFrom IS NULL OR DateTo IS NULL THEN TotalValue

            -- devide by 7 and multiply be the number of days within range
            ELSE TotalValue / 7 * DATEDIFF(DAY,
                                           -- latest of start dates
                                           IIF(DateFrom > @ReportStartDate, DateFrom, @ReportStartDate),
                                           -- earliest of end dates
                                           IIF(DateTo < @ReportEndDate, DateTo, @ReportEndDate)
                                           )
        END  As CalculatedValue
FROM #TestData
-- Get only date ranges that overlap or the date columns are null
WHERE (DateFrom IS NULL OR DateFrom < @ReportEndDate)
AND (DateTo IS NULL OR DateTo > @ReportStartDate)

Upvotes: 1

Related Questions