Compare data between two date ranges in SQL Server

Question

Given me 2 date ranges (previous dates and current dates), and I wanna compare values of these 2 date ranges.

Comparison method:

• n-th day value of one date range comparing with n-th day value of another date range (e.g. value of 1st day of previous date range will be compared with value of 1st day of current date range, and so on)
• If the date does not exist in the table, display zero for the value.
• If 2 date ranges are not in same length, display NULL and zero to fill the gap.

Example

DB Table "data":

date         | value
-------------|------
2018-01-01   | 3
2018-01-02   | 5
2018-01-03   | 8
2018-01-04   | 6
2018-02-04   | 4
2018-02-05   | 2
2018-02-06   | 7
2018-02-07   | 0

Given date ranges: (Current) 2018-02-04 to 2018-02-07, (Previous) 2018-01-01 to 2018-01-03

Desired output:

curDate      | curValue | preDate     | preValue
-------------|----------|-------------|---------
2018-02-04   | 4        | 2018-01-01  | 3
2018-02-05   | 2        | 2018-01-02  | 5
2018-02-06   | 7        | 2018-01-03  | 8
2018-02-07   | 0        | NULL        | 0

I am now stuck in the join condition, and my current SQL is like:

DECLARE @currentStartDateTime  datetime   = '2018-02-04 00:00:00'
DECLARE @currentEndDateTime    datetime   = '2018-02-07 23:59:59'
DECLARE @previousStartDateTime datetime   = '2018-01-01 00:00:00'
DECLARE @previousEndDateTime   datetime   = '2018-01-03 23:59:59'

SELECT   cur.[date]             AS [curDate]
        ,ISNULL(cur.[total], 0) AS [curTotal]
        ,pre.[date]             AS [preDate]
        ,ISNULL(pre.[total], 0) AS [preTotal]
    FROM (
        SELECT * FROM [data] 
            WHERE [date] BETWEEN @currentStartDateTime AND @currentEndDateTime
    ) cur
    FULL OUTER JOIN (       
        SELECT * FROM [data] 
            WHERE [date] BETWEEN @previousStartDateTime AND @previousEndDateTime
    ) pre
        ON cur.[date] = DATEADD(day, 1, pre.[date]) -- <<< Stuck in this part

Answer 1

The first thing I would do is to create common table expressions for your date ranges, each having two columns: a column with the data value, and a column with row number value. This way, It's going to be very easy to join by n-th value.

So here is my suggested solution:

First, Create and populate sample table (Please save us this step in your future questions)

DECLARE @data As Table
(
    [date] DATE,
    [value] INT
);

INSERT INTO @data
VALUES
('2018-01-01', 3),
('2018-01-02', 5),
('2018-01-03', 8),
('2018-01-04', 6),
('2018-02-04', 4),
('2018-02-05', 2),
('2018-02-06', 7),
('2018-02-07', 0);

Now, I've changed your @currentStartDateTime from 2018-02-04 to 2018-02-03, to make sure I also return rows that are not in the table (Please make sure your sample data covers all requierments)

DECLARE @currentStartDateTime  datetime   = '2018-02-03 00:00:00',
        @currentEndDateTime    datetime   = '2018-02-07 23:59:59',
        @previousStartDateTime datetime   = '2018-01-01 00:00:00',
        @previousEndDateTime   datetime   = '2018-01-03 23:59:59'

Now, my solution seems quite cumbersome because I wanted to show all steps in details. You might be able to simplify it.

First, calculate the max date difference in days.
Then, create a numbers cte from 1 to that difference + 1.
Then, create calendar ctes for each range,
Then a final cte to do a full join between the ranges,
and a select from that final cte left joined twice to the data table.

-- This allows us to use the smallest possible tally cte.
DECLARE @MaxDateDiff int;
SELECT @MaxDateDiff = MAX(d)
FROM (
    VALUES  (DATEDIFF(DAY, @currentStartDateTime, @currentEndDateTime)), 
            (DATEDIFF(DAY, @previousStartDateTime, @previousEndDateTime))
    ) v(d) -- I like table value constructors :-)

;WITH Tally AS 
(
    SELECT TOP (@MaxDateDiff + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As Number
    FROM sys.objects a
    -- if your database is very small (number of tables, procedures ect'), 
    -- you might want to unremark the next row
    -- CROSS JOIN sys.objects b
),
CurrentRange AS  
(
    SELECT DATEADD(DAY, Number-1, @currentStartDateTime) As [Date], Number
    FROM Tally
    -- we need the where clause in case the other range is bigger...
    WHERE DATEADD(DAY, Number-1, @currentStartDateTime) <= @currentEndDateTime
), 
PreviousRange AS 
(
    SELECT DATEADD(DAY, Number-1, @previousStartDateTime) As [Date], Number
    FROM Tally
    WHERE DATEADD(DAY, Number-1, @previousStartDateTime) <= @previousEndDateTime
), 
BothRanges AS 
(        
    SELECT C.Date As CurDate, P.Date As PreDate
    FROM CurrentRange As C
    FULL JOIN PreviousRange As P ON C.Number =  P.Number
)

SELECT CurDate, ISNULL(c.Value, 0) as CurValue, PreDate, ISNULL(p.Value, 0) as PreValue
FROM BothRanges
LEFT JOIN @data AS c ON CurDate = c.[Date]
LEFT JOIN @data AS p ON PreDate = p.[Date]

Results: (Remember that @currentStartDateTime is different than the one on the question)

CurDate                 CurValue    PreDate                 PreValue
03.02.2018 00:00:00     0           01.01.2018 00:00:00     3
04.02.2018 00:00:00     4           02.01.2018 00:00:00     5
05.02.2018 00:00:00     2           03.01.2018 00:00:00     8
06.02.2018 00:00:00     7           NULL                    0
07.02.2018 00:00:00     0           NULL                    0

You can see a live demo on rextester.

Answer 2

It is good practice to include all the DDLs and inserts together when you ask.

DROP TABLE data;
CREATE TABLE data
(
    date DATE,
    value INT
);
GO
INSERT INTO data
VALUES
('2018-01-01', 3),
('2018-01-02', 5),
('2018-01-03', 8),
('2018-01-04', 6),
('2018-02-04', 4),
('2018-02-05', 2),
('2018-02-06', 7),
('2018-02-07', 0);

DECLARE @currentStartDateTime DATETIME = '2018-02-04 00:00:00';
DECLARE @currentEndDateTime DATETIME = '2018-02-07 23:59:59';
DECLARE @previousStartDateTime DATETIME = '2018-01-01 00:00:00';
DECLARE @previousEndDateTime DATETIME = '2018-01-03 23:59:59';

SELECT a.date curDate,
       a.value curValue,
       b.date preDate,
       COALESCE(b.value, 0) preValue
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY date) rn
    FROM data
    WHERE date
    BETWEEN @currentStartDateTime AND @currentEndDateTime
) a
    LEFT JOIN
    (
        SELECT *,
               ROW_NUMBER() OVER (ORDER BY date) rn
        FROM data
        WHERE date
        BETWEEN @previousStartDateTime AND @previousEndDateTime
    ) b
        ON a.rn = b.rn;