SQL Query to add a Change Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query to add a Change Column

Author

Topic

Sadhu
Starting Member

14 Posts

Posted - 2015-01-07 : 01:42:37

I have a Query given below that gives the result of top five Units for that edit date. Now I need to add a COlumn CHANGE to the result set that compares the values from Previous date and specify if the value has Increased or decreased.

Query :
DECLARE @LoBId AS INT = 3;
SELECT TOP 5
BuName
,SUM(CAST(FlaggedForResponse AS INT)) FlaggedEdits
,SUM(CASE WHEN FlaggedForResponse = 0 THEN 1 ELSE 0 END) NotFlaggedEdits

FROM CancelCorrect.Amend
WHERE EditDate = '2014-12-29 00:00:00.000'
AND LoBId=@LobId
GROUP BY
BuName
ORDER BY FlaggedEdits desc,NotFlaggedEdits desc

Output Of the above :

BuName | FlaggedEdits | NotFlaggedEdits
VANILLA IR SWAPS - EMEA, 28, 1
BAC AMRS FX PRIME BROKERAGE, 22, 6
BAC AMRS FX MANAGEMENT, 20, 2,
AMERICAS G10 - FX OPTIONS, 12, 0
AUS SWAP TRADING, 8, 0

Desired Output :

BuName | FlaggedEdits | NotFlaggedEdits | Change
VANILLA IR SWAPS - EMEA, 28, 1, Increased
BAC AMRS FX PRIME BROKERAGE, 22, 6, Same
BAC AMRS FX MANAGEMENT, 20, 2, Decreased
AMERICAS G10 - FX OPTIONS, 12, 0, Increased
AUS SWAP TRADING, 8, 0, Increased

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2015-01-07 : 11:55:47

Group by date and buname using CTE then get top five and compare to previous day

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources