summing columns - 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

summing columns

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-28 : 11:10:50

hey guys

i am having problems with one of my queries

My 1st query is this

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

---------------------ProcessingCost -----------------

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]

------------------------MSC ------------------------
SELECT
FDMSAccountNo,
[Month_end_date] as hst_date_processed,
Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
When Fee_Code = '00Y' Then 'Refund Transaction Charge'
Else salesMI_Group1 end as description,
Sum([Retail_amount]) As Amount
Into #MscCount
FROM FDMS.dbo.Fact_Fee_History f
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code
where year (f.Month_end_date) > 2010
Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1

--drop table #MscCount

SELECT [FDMSAccountNo],
Case when year(hst_date_processed) = '2011' then SUM ([Amount]) else 0 end as [MSC_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Amount]) else 0 end as [MSC_2012]
Into #Msc
FROM #MscCount
where year(hst_date_processed) > 2010
group by [FDMSAccountNo], hst_date_processed

select FDMSAccountNo,SUM([MSC_2011]) as [MSC 2011],SUM([MSC_2012]) as [MSC 2012],SUM([MSC_2012]) - SUM([MSC_2011]) as [MSC Comparison]
into #msc1
from #Msc
group by [FDMSAccountNo]

-----------------------Results Layout------------------------
select
p.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],
[MSC 2011] As [MSC 2011],
[MSC 2012] as [MSC 2012],
[MSC 2012] - [MSC 2011] as [MSC Comparison]

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

--where [NetSales_2012] - [NetSales_2011] = 0

Drop table #Fin , #PCost , #Msc

which returns the following http://postimage.org/image/xt5aekil1/

my 2nd query is this

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

---------------------ProcessingCost -----------------

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]

------------------------MSC ------------------------
SELECT
FDMSAccountNo,
[Month_end_date] as hst_date_processed,
Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
When Fee_Code = '00Y' Then 'Refund Transaction Charge'
Else salesMI_Group1 end as description,
Sum([Retail_amount]) As Amount
Into #MscCount
FROM FDMS.dbo.Fact_Fee_History f
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code
where year (f.Month_end_date) > 2010
Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1

--drop table #MscCount

SELECT [FDMSAccountNo],
Case when year(hst_date_processed) = '2011' then SUM ([Amount]) else 0 end as [MSC_2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([Amount]) else 0 end as [MSC_2012]
Into #Msc
FROM #MscCount
where year(hst_date_processed) > 2010
group by [FDMSAccountNo], hst_date_processed

select FDMSAccountNo,SUM([MSC_2011]) as [MSC 2011],SUM([MSC_2012]) as [MSC 2012],SUM([MSC_2012]) - SUM([MSC_2011]) as [MSC Comparison]
into #msc1
from #Msc
group by [FDMSAccountNo]

--drop table #msc1,#Msc

-----------------------Results Layout------------------------
select
p.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],
[MSC 2011] As [MSC 2011],
[MSC 2012] as [MSC 2012],
[MSC 2012] - [MSC 2011] as [MSC Comparison],
[MSC 2011]-[Interchange_2011] as [DI 2011],
[MSC 2012]-[Interchange_2012] as [DI 2012],
SUM ([MSC 2012]-[Interchange_2012]) - SUM ([MSC 2011]-[Interchange_2011]) as [DI Comparison],

[MSC 2011]-[Interchange_2011] -[Scheme_2011] as [DIA 2011],
[MSC 2011]-[Interchange_2011] -[Scheme_2011] as [DIA 2012],
SUM ([MSC 2011]-[Interchange_2011] - [Scheme_2011]) - SUM ([MSC 2012]-[Interchange_2012]-[Scheme_2012]) as [DIA Comparison]

from #Fin f
full outer join #PCost p on f.hst_merchnum = left(p.FDMSAccountNo,9)
Full outer join #msc1 m on p.FDMSAccountNo = m.FDMSAccountNo
Group by p.FDMSAccountNo,
Interchange_2011,
Interchange_2012,
Funding_2011,
Funding_2012,
NetSales_2011,
NetSales_2012,
Scheme_2011,
Scheme_2012,
ProcessingCost_2011,
ProcessingCost_2012,
[MSC 2011],
[MSC 2012]
--where [NetSales_2012] - [NetSales_2011] = 0

Drop table #Fin , #PCost , #Msc, #MscCount

and its returning this http://s15.postimage.org/i0r7lg0xn/2ndquery.jpg

Any ideas ?

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-28 : 16:12:10

hey is there any update on this ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-28 : 21:50:26

Can you explain what is the problem ?

Also we can't see the last image

KH
[spoiler]Time is always against us[/spoiler]

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-29 : 02:25:28

Hi khtan

The problem is that it is given me nulls everwhere. What I am trying to do is creat a column called di, di is the 2011 msc - 2011 interchange. I have already created the msc and interchange columns, I just need the values from them columns unto a new 1 to create a new column

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 12:53:32

quote:
Originally posted by masond

Hi khtan

The problem is that it is given me nulls everwhere. What I am trying to do is creat a column called di, di is the 2011 msc - 2011 interchange. I have already created the msc and interchange columns, I just need the values from them columns unto a new 1 to create a new column

are you sure the accountnos are matching across tables?
also didnt understand reason why you're grouping on aggregted columns from earlier queries. it doesnt make much sense to me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources