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)
 previous december date

Author  Topic 

sauce1979
Starting Member

47 Posts
Posted - 2011-11-30 : 01:18:58
I current;y 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. 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


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
        FROM #TradeDetail
        GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode, AssetClassName,InstrumentType, YEAR(TradeDate)
        ) rawData
GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode,AssetClassName,InstrumentType, Yr



any ideas would be much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-30 : 01:28:17
does #TradeDetail
table have previous december data also? i dont see any other filter being applied so if its there then that should already been included.
if not you need to tweak logic which loads #TradeDetail to include it also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sauce1979
Starting Member

47 Posts
Posted - 2011-11-30 : 02:54:22
#TradeDetail does contains data from the last 6 years. Initially the query only required july - Aug from the current year and YTD totals. Now requirements have been expanded
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-30 : 03:25:51
ok. so now you need to add similar columns for prev dec values also?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sauce1979
Starting Member

47 Posts
Posted - 2011-11-30 : 03:45:30
Hi sorry i think i have been a little unclear. Based on a given date I would like to produce the previous december dates. I have provided some test data


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




If the @MyToMonth variable is 13/10/2010 then there should be a field showing previous gross totals for previous decmeber which is dec 2009. etc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-30 : 11:17:41
do you mean the below?


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,GrossAmount_Last_Dec,Commission_Last_Dec
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,
			r1.*
        FROM #TradeDetail r
        CROSS APPLY (SELECT SUM(GrossAmount_EUR) AS GrossAmount_Last_Dec,SUM(Commission_EUR) AS Commission_Last_Dec
             FROM #TradeDetail
             WHERE DataSourceName = r.DataSourceName
             AND CorporateRegion = r.CorporateRegion
             AND sBroker = r.sBroker
             AND AssetClassCode = r.AssetClassCode
             AND AssetClassName = r.AssetClassName
             AND InstrumentType = r.InstrumentType
             AND TradeDate < DATEADD(yy,DATEDIFF(yy,0,r.TradeDate),0)
             AND TradeDate >= DATEADD(mm,-1,DATEADD(yy,DATEDIFF(yy,0,r.TradeDate),0))
             )r1
        GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode, AssetClassName,InstrumentType, YEAR(TradeDate),GrossAmount_Last_Dec,Commission_Last_Dec

        ) rawData
GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode,AssetClassName,InstrumentType, Yr,GrossAmount_Last_Dec,Commission_Last_Dec





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sauce1979
Starting Member

47 Posts
Posted - 2011-12-02 : 07:31:51
yes thats what i mean. Thanks a lot.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-12-02 : 07:48:53
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site