Sql-server – Split value from a date range in to individual values per date

sql serverssrs

I am modifying a SSRS sales report for my customer. I have already calculated the number of days and based on that the daily value. In the software you set the start and end date of the agreement which are visible on the report.

My customer wants to split the agreement value in to individual date values. Is there a way how I can split the date range in to individual dates? Then I would just group all the individual dates values from all agreements in to one daily total.

I am using SQL Server 2017 and Visual Studio 2013

Best Answer

WITH cte AS ( SELECT {columns list}, start_date as single_date 
              FROM source_table
            UNION ALL
              SELECT {columns list}, DATEADD(day, 1, single_date)
              FROM cte
              WHERE single_date  < finish_date )
SELECT *
FROM cte;

Example fiddle

Tables

-- Original table
CREATE TABLE dbo.History
(
    Effective   datetime NOT NULL,      -- When the value became current
    Product     integer NOT NULL,       -- The product
    Kind        tinyint NOT NULL,       -- The price kind (RRP, special, etc)
    Price       smallmoney NOT NULL,    -- The new price

    CONSTRAINT [PK dbo.History Effective, Product, Kind]
        PRIMARY KEY CLUSTERED
            (Effective, Product, Kind)
);

-- Holding area for duplicates that may be needed in future
CREATE TABLE dbo.HistoryDuplicates
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,

    CONSTRAINT [PK dbo.HistoryDuplicates Product, Kind, Effective]
        PRIMARY KEY CLUSTERED
            (Product, Kind, Effective)
);

Initial Data

CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-23T00:23:00', 1234, 1, 1.00),
    ('2013-04-24T00:24:00', 1234, 1, 1.00),
    ('2013-04-25T00:25:00', 1234, 1, 1.50),
    ('2013-04-25T00:25:00', 1234, 2, 2.00),
    ('2013-04-26T00:26:00', 1234, 1, 2.00),
    ('2013-04-27T00:27:00', 1234, 1, 2.00),
    ('2013-04-28T00:28:00', 1234, 1, 1.00);

The first step is to remove any redundancies in the input data, storing the removed data in the new holding table:

-- Remove redundancies in the input set
-- and save in the duplicates table
DELETE NR
OUTPUT
    DELETED.Effective,
    DELETED.Product,
    DELETED.Kind,
    DELETED.Price
INTO dbo.HistoryDuplicates
    (Effective, Product, Kind, Price)
FROM #NewRows AS NR
OUTER APPLY
(
    SELECT TOP (1)
        NR2.Price
    FROM #NewRows AS NR2
    WHERE
        NR2.Product = NR.Product
        AND NR2.Kind = NR.Kind
        AND NR2.Effective < NR.Effective
    ORDER BY
        NR2.Effective DESC
) AS X
WHERE
    EXISTS (SELECT X.Price INTERSECT SELECT NR.Price);

De-duplication execution plan

Classifying input rows

The next step is to decide whether each row in the input table is redundant (w.r.t the History table) or not. The following query sets the Action column of the input set data appropriately:

-- Decide what to do with each row
UPDATE NewRows
SET [Action] = 
    CASE
        WHEN NOT EXISTS (SELECT ExistingRow.Price INTERSECT SELECT NewRows.Price)
            THEN 'I' -- Insert
        WHEN ExistingRow.Price = NewRows.Price
            THEN 'D' -- Duplicate
        ELSE 'X'
    END
FROM #NewRows AS NewRows
OUTER APPLY
(
    SELECT TOP (1)
        H.Price,
        H.Effective
    FROM dbo.History AS H
    WHERE
        H.Product = NewRows.Product
        AND H.Kind = NewRows.Kind
        AND H.Effective <= NewRows.Effective
    ORDER BY
        H.Effective DESC
) AS ExistingRow;

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows 
    ([Action], Product, Kind, Effective) 
WITH (DROP_EXISTING = ON);

Action execution plan

Store Redundant Rows

Now we store the rows identified as redundant to the holding table:

-- Store duplicates
WITH Duplicates AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'D'
)
MERGE dbo.HistoryDuplicates AS HD
USING Duplicates AS NR
    ON NR.Product = HD.Product
    AND NR.Kind = HD.Kind
    AND NR.Effective = HD.Effective
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Product, Kind, Effective, Price) 
    VALUES (Product, Kind, Effective, Price);

Redundant records plan

New History rows

The non-redundant rows are added to the History table:

-- Inserts
WITH Inserts AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'I'
)
MERGE dbo.History AS H
USING Inserts AS NR
    ON NR.Product = H.Product
    AND NR.Kind = H.Kind
    AND NR.Effective = H.Effective
    AND NR.Price = H.Price
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Effective, Product, Kind, Price)
    VALUES (Effective, Product, Kind, Price);

New rows plan

Reinstating redundant records

Adding new records can result in redundant rows needing to be reinstated. The following query identifies qualifying redundant rows and moves them to the History table:

DELETE NextDuplicate
OUTPUT DELETED.Product,
       DELETED.Kind,
       DELETED.Price,
       DELETED.Effective
INTO dbo.History
    (Product, Kind, Price, Effective)
FROM #NewRows AS NR
CROSS APPLY
(
    SELECT TOP (1)
        H.Effective,
        H.Price
    FROM dbo.History AS H
    WHERE
        H.Product = NR.Product
        AND H.Kind = NR.Kind
        AND H.Effective > NR.Effective
    ORDER BY
        H.Effective ASC
) AS NextHistory
CROSS APPLY
(
    SELECT TOP (1)
        HD.Effective,
        HD.Product,
        HD.Kind,
        HD.Price
    FROM dbo.HistoryDuplicates AS HD
    WHERE
        HD.Product = NR.Product
        AND HD.Kind = NR.Kind
        AND HD.Effective > NR.Effective
        AND HD.Effective < NextHistory.Effective
    ORDER BY
        HD.Effective ASC
) AS NextDuplicate
WHERE
    NR.[Action] = 'I'
    AND NOT EXISTS (SELECT NextDuplicate.Price INTERSECT SELECT NR.Price);

Relocate rows plan

Results

History table

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

History Duplicates table

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

Processing new data

The preceding steps are quite general. We can process a new batch of rows using exactly the same code. The next script loads the input table with two sample rows, one of which is a duplicate, and the other an example of needing to reinstate a previously-redundant row:

-- If needed
DROP TABLE #NewRows;

CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-24 00:24:01.000', 1234, 1, 1.00), -- New duplicate
    ('2013-04-26 00:26:30.000', 1234, 1, 5.00); -- Complex new row

Running the rest of the general script produces this final state:

History:

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-26 00:26:30.000 ║    1234 ║    1 ║ 5.00  ║ <- New
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║ <- Recovered
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

History Duplicate:

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-24 00:24:01.000 ║    1234 ║    1 ║ 1.00  ║ <- New duplicate
╚═════════════════════════╩═════════╩══════╩═══════╝

Sql-server – calculate days and equally distribute remaining

hope this works for you. It meets your expected results I believe:

| TASK |      BEGIN |        END | HOURS | WEEKDAYS |             M1H |             M2H |    M3H |    M4H |    M5H |    M6H |
|------|------------|------------|-------|----------|-----------------|-----------------|--------|--------|--------|--------|
|    1 | 2013-11-05 | 2013-11-05 |     5 |        1 |               5 |          (null) | (null) | (null) | (null) | (null) |
|    2 | 2013-10-01 | 2013-11-15 |     2 |       34 |  0.647058823529 |          (null) | (null) | (null) | (null) | (null) |
|    3 | 2013-11-15 | 2013-12-31 |    80 |       33 | 26.666666666667 | 53.333333333333 | (null) | (null) | (null) | (null) |
|    4 | 2013-05-01 | 2013-12-04 |     2 |      156 |  0.269230769231 |  0.038461538462 | (null) | (null) | (null) | (null) |
|    5 | 2013-07-01 | 2013-11-27 |    15 |      108 |  2.638888888889 |          (null) | (null) | (null) | (null) | (null) |
|    6 | 2013-11-01 | 2013-12-10 |    40 |       28 |              30 |              10 | (null) | (null) | (null) | (null) |


DECLARE @periodStart date, @periodEnd date

SET @periodStart = '2013-11-01'
SET @periodEnd   = '2014-05-01'

SELECT
          [task]
        , [begin]
        , [end]
        , [hours]
        , max(task_wd) AS weekdays
        , max( CASE WHEN yrmnth = ( YEAR(@periodStart) * 100 + MONTH(@periodStart) )                                   THEN ([hours] / task_wd) * mnth_wd END ) AS m1h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,1,@periodStart)) * 100 + MONTH(dateadd(MONTH,1,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m2h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,2,@periodStart)) * 100 + MONTH(dateadd(MONTH,2,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m3h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,3,@periodStart)) * 100 + MONTH(dateadd(MONTH,3,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m4h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,4,@periodStart)) * 100 + MONTH(dateadd(MONTH,4,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m5h
        , max( CASE WHEN yrmnth = ( YEAR(dateadd(MONTH,5,@periodStart)) * 100 + MONTH(dateadd(MONTH,5,@periodStart)) ) THEN ([hours] / task_wd) * mnth_wd END ) AS m6h
FROM (
        SELECT
                  t.[task]
                , t.[begin]
                , t.[end]
                , t.[hours]
                , (year(c.d) * 100 + MONTH(c.d))                                 AS yrmnth

                , count(CASE
                         WHEN c.d >= @periodStart AND c.d < @periodEnd THEN c.d
                        END
                       ) over (partition BY t.[task], year(c.d), MONTH(c.d))     AS mnth_wd
                , count(c.d) over (partition BY t.[task]) * 1.0                  AS task_wd
        FROM Calendar AS c
        INNER JOIN tasks AS t ON c.d BETWEEN t.[begin] and t.[end]
        WHERE c.weekday = 1
        AND t.[begin] <= @periodEnd
        AND t.[end] >= @periodStart
     ) AS derived
GROUP BY
          [task]
        , [begin]
        , [end]
        , [hours]

I did this is here http://sqlfiddle.com/#!3/682ff/42 , but I also simulated your calendar table witha CTE so the code there is a bit messier.