Sum Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Sum Query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-28 : 07:42:39

hi guys

i need some help. I need to create a three separate columns

columns need to be called

DI 2011
DI 2012
DI Comparision

To work out DI 2011
it is net Sales 2011-Interchange 2011

DI 2012
it is Netsales 2012-Interchange 2012

DI comparision

Difference between 2012 Di figure and 2011 Di figure

My query so far is

SELECT [hst_merchnum],
SUM(Case when year(hst_date_processed) = '2011' then [Net_Intg] else 0 end) as [Interchange_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Intg] else 0 end) as [Interchange_2012] ,
SUM(Case when year(hst_date_processed) = '2011' then [Net_Sales] else 0 end) as [NetSales_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Net_Sales] else 0 end) as [NetSales_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Scheme_Fees] else 0 end) as [Scheme_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Scheme_Fees] else 0 end) as [Scheme_2012],
SUM(Case when year(hst_date_processed) = '2011' then [Funding_Amt] else 0 end) as [Funding_2011] ,
SUM(Case when year(hst_date_processed) = '2012' then [Funding_Amt] else 0 end) as [Funding_2012]
into #Fin
FROM [FDMS].[dbo].[Fact_Financial_History]
where year(hst_date_processed) > 2010

--where (hst_date_processed >= dateadd(year, datediff(year, 0, getdate()) - 1, 0)
--and hst_date_processed <= dateadd(year, -1, getdate()))
--or hst_date_processed >= dateadd(year, datediff(year, 0, getdate()), 0)

group by hst_merchnum

SELECT [FDMSAccountNo],
SUM(Case when year(Period)= '2011' then [Value] else 0 end) as [ProcessingCost_2011] ,
SUM(Case when year(Period) = '2012' then [Value] else 0 end) as [ProcessingCost_2012]
into #PCost
FROM [FDMS].[dbo].[Fact_ProcessingCost]
where year(period) > 2010
group by [FDMSAccountNo]

--SELECT [FDMSAccountNo],
--Case when year(hst_date_processed) = '2011' then SUM ([Amount]) else 0 end as [2011] ,
--Case when year(hst_date_processed) = '2012' then SUM ([Amount]) else 0 end as [2012]
--Into #Msc
--FROM [Rm_Book_New].[dbo].[MSC]
--where year(hst_date_processed) > 2010
--group by [FDMSAccountNo]
--select FDMSAccountNo,SUM([2011]) as [2011 MSC],SUM([2012]) as [2012 MSC],SUM([2012]) - SUM([2011]) as [Comparision]
--group by FDMSAccountNo

select
FDMSAccountNo,
[Interchange_2011] as [2011 InterChange],
[Interchange_2012] as [2012 Interchange],
[Interchange_2012] - [Interchange_2011] as [Interchange_Comparison],
[Funding_2011] as [2011 Funding_Amt],
[Funding_2012] as [2012 Funding_Amt],
[Funding_2012] - [Funding_2011] as [Funding_Comparison],
[NetSales_2011] as [2011 NetSales],
[NetSales_2012] as [2012 NetSales],
[NetSales_2012] - [NetSales_2011] as [NetSales_Comparison],
[Scheme_2011] as [2011 Scheme_Fees],
[Scheme_2012] as [2012 Scheme_Fees],
[Scheme_2012] - [Scheme_2011] as [Scheme_Comparison],
[ProcessingCost_2011] as [2011_ProcessingCost],
[ProcessingCost_2012] as [2012_ProcessingCost],
[ProcessingCost_2012] - [ProcessingCost_2011] as [ProcessingCost_Comparision]

from #Fin f
full outer join #PCost p on f.hst_merchnum = left(p.FDMSAccountNo,9)

Drop table #Fin , #PCost

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources