CODEWITHSUNDEEP
sqlsql-server
joe
joe

Reputation: 1473

How to show year in SQL

I was able to group the item counts three years prior and three years after on certain date, but I was not able to break the item counts in three separate years.

SELECT
ITEM,
CASE WHEN DATE >='12/6/02' AND DATE <'12/6/05' AND ITEM ='RICE' THEN 'RICE PRIOR'
CASE WHEN DATE >='12/06/05' AND DATE <'12/6/08' AND ITEM ='RICE' THEN 'RICE POST'
END AS TYPE,
COUNT (QTY) AS RICE
FROM #TEMP

WHERE DATE >='12/6/02'
AND DATE <'12/6/08'
GROUP BY ITEM,
 CASE WHEN DATE >='12/6/02' AND DATE <'12/6/05' AND ITEM ='RICE' THEN 'RICE PRIOR'
CASE WHEN DATE >='12/06/05' AND DATE <'12/6/08' AND ITEM ='RICE' THEN 'RICE POST'
END

How can I break it down into three years? Running this script, my result shows:

Type,     Rice
Rice Prior 444
Rice Post  555

My desired output would be something like:

Year3, Year2, Year1  Year1, Year2, Year3
4      44     400     5      500    50

Upvotes: 1

Views: 174

Answers (3)

Jason W
Jason W

Reputation: 13179

This should match your desired query using a pivot and date parameter so you don't have to hard code it all the dates and conditions. 

DECLARE @Date DATE = '12/6/2005'
SELECT
    ITEM,
    [-3] AS Year3,
    [-2] AS Year2,
    [-1] AS Year1,
    [1] AS Year1,
    [2] AS Year2,
    [3] AS Year3
FROM (
    SELECT
        ITEM,
        DATEDIFF(DD, @Date, [DATE]) / 365
            + CASE WHEN @Date < [DATE] THEN 1 ELSE -1 END  AS [YR],
        QTY
    FROM #TEMP
    ) T
    PIVOT (MAX(QTY) FOR [YR] IN ([-3], [-2], [-1], [1], [2], [3])) PVT

The above approach does not account for leap years. You can apply @JPW's approach of using DATEADD which is aware of leap years. You'll just need to be aware of the border scenarios of which is the first and last day of each region. In this approach, the first match wins, so today's date would be in year "-1".

DECLARE @D DATE = '12/6/2005'
SELECT
    ITEM,
    [-3] AS Year3,
    [-2] AS Year2,
    [-1] AS Year1,
    [1] AS Year1,
    [2] AS Year2,
    [3] AS Year3
FROM (
    SELECT
        ITEM,
        CASE
            WHEN DATE BETWEEN DATEADD(YEAR, -3, @D) AND DATEADD(YEAR, -2, @D) THEN -3
            WHEN DATE BETWEEN DATEADD(YEAR, -2, @D) AND DATEADD(YEAR, -1, @D) THEN -2
            WHEN DATE BETWEEN DATEADD(YEAR, -1, @D) AND DATEADD(YEAR, -0, @D) THEN -1
            WHEN DATE BETWEEN DATEADD(YEAR, +0, @D) AND DATEADD(YEAR, +1, @D) THEN 1
            WHEN DATE BETWEEN DATEADD(YEAR, +1, @D) AND DATEADD(YEAR, +2, @D) THEN 2
            WHEN DATE BETWEEN DATEADD(YEAR, +2, @D) AND DATEADD(YEAR, +3, @D) THEN 3
            ELSE NULL END AS [YR],
        QTY
    FROM #TEMP
    ) T
    PIVOT (MAX(QTY) FOR [YR] IN ([-3], [-2], [-1], [1], [2], [3])) PVT

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17126

So basically you can apply your count logic inside the select portion of PIVOT and then pivot the result set on years

SELECT ITEM, [2002] AS[Year-3],[2003] AS [Year-2], [2004] AS [Year-1],
[2005] AS [Year1],[2006] AS [Year2],[2007] AS [Year3]
FROM (
-- Your logic here
SELECT
ITEM,
YEAR(Date) as Y,
COUNT (quantity) AS RICE
FROM [dbo].[temp]
WHERE DATE >='12/6/01'
AND DATE <'12/6/08'
GROUP BY ITEM,YEAR(Date)
) as s
PIVOT
(
    SUM(RICE)
    FOR Y IN ([2002], [2003], [2004], [2005],[2006],[2007])
)AS p

Sql fiddle link http://sqlfiddle.com/#!6/fa406/2

Upvotes: 0

jpw
jpw

Reputation: 44881

Based on your sample output I think a query like below might be what you want. You might have to tweak the date ranges to fit your needs but the general idea should be clear:

DECLARE @d date = '2005-07-27';
WITH cte AS 
(
    SELECT
       ITEM
       , SUM(CASE WHEN DATE BETWEEN DATEADD(YEAR, -3, @D) AND DATEADD(YEAR, -2, @D) AND ITEM ='RICE' THEN QTY END) 'RICE PRIOR -3'
       , SUM(CASE WHEN DATE BETWEEN DATEADD(YEAR, -2, @D) AND DATEADD(YEAR, -1, @D) AND ITEM ='RICE' THEN QTY END) 'RICE PRIOR -2'
       , SUM(CASE WHEN DATE BETWEEN DATEADD(YEAR, -1, @D) AND DATEADD(YEAR, -0, @D) AND ITEM ='RICE' THEN QTY END) 'RICE PRIOR -1'
       , SUM(CASE WHEN DATE BETWEEN DATEADD(YEAR, +0, @D) AND DATEADD(YEAR, +1, @D) AND ITEM ='RICE' THEN QTY END) 'RICE POST +1'
       , SUM(CASE WHEN DATE BETWEEN DATEADD(YEAR, +1, @D) AND DATEADD(YEAR, +2, @D) AND ITEM ='RICE' THEN QTY END) 'RICE POST +2'
       , SUM(CASE WHEN DATE BETWEEN DATEADD(YEAR, +2, @D) AND DATEADD(YEAR, +3, @D) AND ITEM ='RICE' THEN QTY END) 'RICE POST +3'
    FROM #TEMP
    WHERE DATE >='2002-05-12' AND DATE <'2008-06-12'
    GROUP BY ITEM
)

SELECT * FROM CTE;

Upvotes: 1

Related Questions