Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DateAdd - Show past year, 2 years, 3 years Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 2:16 AM
Points: 1, Visits: 0
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
Post #1542426
Posted Tuesday, February 18, 2014 8:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542618
Posted Tuesday, February 18, 2014 7:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 3,590, Visits: 5,096
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542823
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse