Search code examples
sql-serverrollup

Get monthy and yearly count value from mm/dd/yyyy in SQL Server using Rollup

I have a database table as shown below with over 100000 records:

enter image description here

I am trying to implement rollup query on this so that I can retrieve records for a particular item and display the quantity in a monthly basis.

enter image description here

I have Over 100000 records with 15 columns. Currently it is retrieving based on select query iterative by year and then month to calculate the sum. I want to use rollup to speed up retrieval.

Solution

  • You just need to do conditional aggregation:

    DECLARE @fromYear   INT = 2010,
        @toYear     INT = 2014

DECLARE @fromDate   DATE,
        @toDate     DATE

-- Generate date range based on @fromYear and @toYear
SELECT
    @fromDate = DATEADD(YEAR, @fromYear - 1900, 0),
    @toDate   = DATEADD(YEAR, @toYear - 1900 + 1, 0)

SELECT
    YEAR(soldDate), 
    [Jan] = SUM(CASE WHEN MONTH(soldDate) = 1  THEN quantity ELSE 0 END),
    [Feb] = SUM(CASE WHEN MONTH(soldDate) = 2  THEN quantity ELSE 0 END),
    [Mar] = SUM(CASE WHEN MONTH(soldDate) = 3  THEN quantity ELSE 0 END),
    [Apr] = SUM(CASE WHEN MONTH(soldDate) = 4  THEN quantity ELSE 0 END),
    [May] = SUM(CASE WHEN MONTH(soldDate) = 5  THEN quantity ELSE 0 END),
    [Jun] = SUM(CASE WHEN MONTH(soldDate) = 6  THEN quantity ELSE 0 END),
    [Jul] = SUM(CASE WHEN MONTH(soldDate) = 7  THEN quantity ELSE 0 END),
    [Aug] = SUM(CASE WHEN MONTH(soldDate) = 8  THEN quantity ELSE 0 END),
    [Sep] = SUM(CASE WHEN MONTH(soldDate) = 9  THEN quantity ELSE 0 END),
    [Oct] = SUM(CASE WHEN MONTH(soldDate) = 10 THEN quantity ELSE 0 END),
    [Nov] = SUM(CASE WHEN MONTH(soldDate) = 11 THEN quantity ELSE 0 END),
    [Dec] = SUM(CASE WHEN MONTH(soldDate) = 12 THEN quantity ELSE 0 END)
FROM tbl
WHERE
    item = 'basketball'
    AND soldDate >= @fromDate
    AND soldDate < @toDate
GROUP BY YEAR(soldDate)

    If you want to show all years, including those with 0 sold items, you need to generate all those years first. You can do that using a tally table. Then the result will be LEFT JOINed to the original query above:

    DECLARE @fromYear   INT = 2010,
        @toYear     INT = 2014

DECLARE @fromDate   DATE,
        @toDate     DATE

-- Generate date range based on @fromYear and @toYear
SELECT
    @fromDate = DATEADD(YEAR, @fromYear - 1900, 0),
    @toDate   = DATEADD(YEAR, @toYear - 1900 + 1, 0)

;WITH E1(N) AS(
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
CteTally(N) AS(
    SELECT TOP(@toYear - @fromYear) 
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteAgg AS(
    SELECT
        [Yr]  = YEAR(soldDate),
        [Jan] = SUM(CASE WHEN MONTH(soldDate) = 1  THEN quantity ELSE 0 END),
        [Feb] = SUM(CASE WHEN MONTH(soldDate) = 2  THEN quantity ELSE 0 END),
        [Mar] = SUM(CASE WHEN MONTH(soldDate) = 3  THEN quantity ELSE 0 END),
        [Apr] = SUM(CASE WHEN MONTH(soldDate) = 4  THEN quantity ELSE 0 END),
        [May] = SUM(CASE WHEN MONTH(soldDate) = 5  THEN quantity ELSE 0 END),
        [Jun] = SUM(CASE WHEN MONTH(soldDate) = 6  THEN quantity ELSE 0 END),
        [Jul] = SUM(CASE WHEN MONTH(soldDate) = 7  THEN quantity ELSE 0 END),
        [Aug] = SUM(CASE WHEN MONTH(soldDate) = 8  THEN quantity ELSE 0 END),
        [Sep] = SUM(CASE WHEN MONTH(soldDate) = 9  THEN quantity ELSE 0 END),
        [Oct] = SUM(CASE WHEN MONTH(soldDate) = 10 THEN quantity ELSE 0 END),
        [Nov] = SUM(CASE WHEN MONTH(soldDate) = 11 THEN quantity ELSE 0 END),
        [Dec] = SUM(CASE WHEN MONTH(soldDate) = 12 THEN quantity ELSE 0 END)
    FROM tbl t
    WHERE
        item = 'basketball'
        AND soldDate >= @fromDate
        AND soldDate < @toDate
    GROUP BY YEAR(soldDate)
)
SELECT
    [Yr] = (@fromYear + t.N - 1),
    [Jan] = ISNULL([Jan], 0),
    [Feb] = ISNULL([Feb], 0),
    [Mar] = ISNULL([Mar], 0),
    [Apr] = ISNULL([Apr], 0),
    [May] = ISNULL([May], 0),
    [Jun] = ISNULL([Jun], 0),
    [Jul] = ISNULL([Jul], 0),
    [Aug] = ISNULL([Aug], 0),
    [Sep] = ISNULL([Sep], 0),
    [Oct] = ISNULL([Oct], 0),
    [Nov] = ISNULL([Nov], 0),
    [Dec] = ISNULL([Dec], 0)
FROM CteTally t
LEFT JOIN CteAgg a
    ON (@fromYear + t.N - 1) = a.Yr
ORDER BY (@fromYear + t.N - 1)
DROP TABLE tbl

    ONLINE DEMO