DateAdd - Show past year, 2 years, 3 years

Question

DateAdd - Show past year, 2 years, 3 years

tonyabrhm

SSC Enthusiast

Points: 185
More actions
February 18, 2014 at 2:25 am

#289637

Hi
I am trying to create a query which shows data from the past 1, 2, 3 4 and 5 years in seperate columns. I beleive I have the within one year correct but the more I look the more I am confused! Any help would be greatly appreciated!
SELECT TOP (100) StockCode,
(SELECT CASE WHEN DATEADD(y, - 1, GETDATE()) >=
(SELECT TOP (1) MAX(EntryDate) AS EntryDate
FROM InvMovements AS InvMovements_1
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) THEN
(SELECT TOP (1) SUM(TrnValue) AS Value
FROM InvMovements
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) ELSE '0' END AS Expr1) AS [<1Year],
(SELECT CASE WHEN DATEADD(y, - 1, GETDATE()) <=
(SELECT TOP (1) MAX(EntryDate) AS EntryDate
FROM InvMovements AS InvMovements_1
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) THEN
(SELECT TOP (1) SUM(TrnValue) AS Value
FROM InvMovements
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) ELSE '0' END AS Expr1) AS [>1<2Year]
FROM InvWarehouse AS invWH
GROUP BY StockCode

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

tonyabrhm (2/18/2014)
Hi
I am trying to create a query which shows data from the past 1, 2, 3 4 and 5 years in seperate columns. I beleive I have the within one year correct but the more I look the more I am confused! Any help would be greatly appreciated!
SELECT TOP (100) StockCode,
(SELECT CASE WHEN DATEADD(y, - 1, GETDATE()) >=
(SELECT TOP (1) MAX(EntryDate) AS EntryDate
FROM InvMovements AS InvMovements_1
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) THEN
(SELECT TOP (1) SUM(TrnValue) AS Value
FROM InvMovements
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) ELSE '0' END AS Expr1) AS [<1Year],
(SELECT CASE WHEN DATEADD(y, - 1, GETDATE()) <=
(SELECT TOP (1) MAX(EntryDate) AS EntryDate
FROM InvMovements AS InvMovements_1
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) THEN
(SELECT TOP (1) SUM(TrnValue) AS Value
FROM InvMovements
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) ELSE '0' END AS Expr1) AS [>1<2Year]
FROM InvWarehouse AS invWH
GROUP BY StockCode

The ancient and very "Black Art" of using CROSSTABs will likely be your best bet. Please see the following article.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Jeff Moden (2/18/2014)
tonyabrhm (2/18/2014)
Hi
I am trying to create a query which shows data from the past 1, 2, 3 4 and 5 years in seperate columns. I beleive I have the within one year correct but the more I look the more I am confused! Any help would be greatly appreciated!
SELECT TOP (100) StockCode,
(SELECT CASE WHEN DATEADD(y, - 1, GETDATE()) >=
(SELECT TOP (1) MAX(EntryDate) AS EntryDate
FROM InvMovements AS InvMovements_1
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) THEN
(SELECT TOP (1) SUM(TrnValue) AS Value
FROM InvMovements
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) ELSE '0' END AS Expr1) AS [<1Year],
(SELECT CASE WHEN DATEADD(y, - 1, GETDATE()) <=
(SELECT TOP (1) MAX(EntryDate) AS EntryDate
FROM InvMovements AS InvMovements_1
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) THEN
(SELECT TOP (1) SUM(TrnValue) AS Value
FROM InvMovements
WHERE (MovementType = 'S') AND (StockCode = invWH.StockCode)
GROUP BY StockCode) ELSE '0' END AS Expr1) AS [>1<2Year]
FROM InvWarehouse AS invWH
GROUP BY StockCode
The ancient and very "Black Art" of using CROSSTABs will likely be your best bet. Please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63681/

I didn't realize that this was the way of the Dark Side.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St