Previous Decemeber Totals based on date

Question

Previous Decemeber Totals based on date

eseosaoregie

SSCertifiable

Points: 6889
More actions
November 30, 2011 at 1:15 am

#246995

I currently use a query to create a YTD summary report of trade data. I would like to extend it to include the previous Dec gross amounts based on the date month given. I am struggling to write the syntax. I have included some of the code I have now already. I have oversimplified it for example purposes. If the @MyToMonth date is 13/10/2010 then there should be a field showing previous grosd total for previous decmeber which is dec 2009. etc
DECLARE @MyToMonth char(8);
SET @MyToMonth = convert(char(8),getdate(),112) -- or any date specified
SELECT DataSourceName,
CorporateRegion,
sBroker,
AssetClassCode,
AssetClassName,
InstrumentType,
Yr,
MAX(GrossAmount_EUR_Jul) AS GrossAmount_EUR_Jul,
MAX(GrossAmount_EUR_Aug) AS GrossAmount_EUR_Aug,
MAX(GrossAmount_EUR_Sep) AS GrossAmount_EUR_Sep,
SUM(GrossAmount_EUR_YTD) AS GrossAmount_EUR_YTD,
MAX(Commission_EUR_Jul) AS Commission_EUR_Jul,
MAX(Commission_EUR_Aug) AS Commission_EUR_Aug,
MAX(Commission_EUR_Sep) AS Commission_EUR_Sep,
SUM(Commission_EUR_YTD) AS Commission_EUR_YTD
FROM
(SELECT DataSourceName,
CorporateRegion,
sBroker,
AssetClassCode,
AssetClassName,
InstrumentType,
YEAR(TradeDate) AS Yr,
SUM(CASE WHEN MONTH(TradeDate) = 7 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDate) = 8 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDate) = 9 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(GrossAmount_EUR) AS GrossAmount_EUR_YTD,
SUM(CASE WHEN MONTH(TradeDate) = 7 THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDate) = 8 THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDate) = 9 THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(Commission_EUR) AS Commission_EUR_YTD
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN TotalGross END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN TotalCommission END)AS PreviousYear1Commission,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN OrderCount END) AS PreviousYear1Orders,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN TotalGross END) AS PreviousYear2Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN TotalCommission END) AS PreviousYear2Commission,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN OrderCount END) AS PreviousYear2Orders,
FROM #TradeDetail
GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode, AssetClassName,InstrumentType, YEAR(TradeDate)
) rawData
GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode,AssetClassName,InstrumentType, Yr
any ideas?
Apologies for initially posting this in the wrong forum

Viewing 5 posts - 1 through 4 (of 4 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Hello and welcome to SSC!

Unfortunately, it seems that your readily consumable sample data and DDL scripts have fallen off your post. Or perhaps you were unaware of the benefits of providing them? When you have time, please read this article[/url] about the best way to provide us with the necessary scripts to allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.

Thanks.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 2

Apologies I just forgot to paste it in. The code is as follows

CREATE TABLE #TradeDetail

(

DataSourceName varchar(12)

, CorporateRegion char(3)

, TradeDate datetime

, TradeOrderNumber varchar(50)

, sBroker varchar(120)

, AssetClassCode char(1)

, AssetClassName varchar(120)

, InstrumentType varchar(120)

, GrossAmount_EUR numeric(18,6)

, Commission_EUR numeric(18,6)

)

INSERT INTO #TradeDetail

SELECT 'CRTS','EUR','20110701','1323562556','BARCLAYS', 'E','Equity','Common Stock','100','100' UNION ALL

SELECT 'CRTS','EUR','20110801','1323562557','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

SELECT 'CRTS','EUR','20110901','1323562558','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

SELECT 'CRTS','EUR','20110701','1323562559','BARCLAYS', 'E','Equity','Common Stock','100','200' UNION ALL

SELECT 'CRTS','EUR','20110701','1323562560','JP MORGAN', 'E','Equity','Common Stock','50','100' UNION ALL

SELECT 'CRTS','EUR','20110801','1323562561','JP MORGAN', 'E','Equity','Common Stock','50','200' UNION ALL

SELECT 'CRTS','EUR','20110901','1323562562','JP MORGAN', 'E','Equity','Common Stock','100','200'UNION ALL

SELECT 'CRTS','EUR','20110701','1323562563','KEMPEN', 'E','Equity','Common Stock','25','40' UNION ALL

SELECT 'CRTS','EUR','20091201','1323562564','KEMPEN', 'E','Equity','Common Stock','25','50' UNION ALL

SELECT 'CRTS','EUR','20101201','1323562562','JP MORGAN', 'E','Equity','Common Stock','500','250'UNION ALL

SELECT 'CRTS','EUR','20101204','1323562563','KEMPEN', 'E','Equity','Common Stock','25','60' UNION ALL

SELECT 'CRTS','EUR','20091206','1323562564','KEMPEN', 'E','Equity','Common Stock','45','90' UNION ALL

SELECT 'CRTS','EUR','20081201','1323562562','JP MORGAN', 'E','Equity','Common Stock','100','21'UNION ALL

SELECT 'CRTS','EUR','20110701','1323562563','KEMPEN', 'E','Equity','Common Stock','25','50' UNION ALL

SELECT 'CRTS','EUR','20110801','1323562564','KEMPEN', 'E','Equity','Common Stock','25','50' UNION ALL

SELECT 'CRTS','EUR','20110901','1323562565','KEMPEN', 'E','Equity','Common Stock','25','50'

DECLARE @MyToMonth char(8);

SET @MyToMonth = convert(char(8),getdate(),112) -- or any date specified

SELECT DataSourceName,

CorporateRegion,

sBroker,

AssetClassCode,

AssetClassName,

InstrumentType,

Yr,

MAX(GrossAmount_EUR_Jul) AS GrossAmount_EUR_Jul,

MAX(GrossAmount_EUR_Aug) AS GrossAmount_EUR_Aug,

MAX(GrossAmount_EUR_Sep) AS GrossAmount_EUR_Sep,

SUM(GrossAmount_EUR_YTD) AS GrossAmount_EUR_YTD,

MAX(Commission_EUR_Jul) AS Commission_EUR_Jul,

MAX(Commission_EUR_Aug) AS Commission_EUR_Aug,

MAX(Commission_EUR_Sep) AS Commission_EUR_Sep,

SUM(Commission_EUR_YTD) AS Commission_EUR_YTD

FROM

(SELECT DataSourceName,

CorporateRegion,

sBroker,

AssetClassCode,

AssetClassName,

InstrumentType,

YEAR(TradeDate) AS Yr,

SUM(CASE WHEN MONTH(TradeDate) = 7 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,

SUM(CASE WHEN MONTH(TradeDate) = 8 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,

SUM(CASE WHEN MONTH(TradeDate) = 9 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,

SUM(GrossAmount_EUR) AS GrossAmount_EUR_YTD,

SUM(CASE WHEN MONTH(TradeDate) = 7 THEN Commission_EUR END) AS Commission_EUR_Jul,

SUM(CASE WHEN MONTH(TradeDate) = 8 THEN Commission_EUR END) AS Commission_EUR_Aug,

SUM(CASE WHEN MONTH(TradeDate) = 9 THEN Commission_EUR END) AS Commission_EUR_Sep,

SUM(Commission_EUR) AS Commission_EUR_YTD,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDate)) ) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDate)) ) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDate)) BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDate)) BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN Commission_EUR END) AS PreviousYear2Commission

FROM #TradeDetail

GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode, AssetClassName,InstrumentType, YEAR(TradeDate)

) rawData

GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode,AssetClassName,InstrumentType, Yr

Thus based on the date given I sould pull out the sum of previous decmeber total gross and commission figures

larsjessenhh SSC Veteran Points: 280 More actions · Answer 3

Hi,

this will get you all the dates from previous december:

BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0))

Lars

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 4

eseosaoregie

SSCertifiable

Points: 6889

November 30, 2011 at 3:57 am

#1414544

thanks. that produced my output.