November 30, 2011 at 1:15 am
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
November 30, 2011 at 1:26 am
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.
November 30, 2011 at 1:41 am
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
November 30, 2011 at 2:15 am
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
November 30, 2011 at 3:57 am
thanks. that produced my output.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply