SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 summing columns		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Posting Yak Master

241 Posts
Posted - 08/28/2012 :  11:10:50  Show Profile  Reply with Quote
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 ?

Edited by - masond on 08/28/2012 16:38:56

masond
Posting Yak Master

241 Posts
Posted - 08/28/2012 :  16:12:10  Show Profile  Reply with Quote
hey is there any update on this ?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 08/28/2012 :  21:50:26  Show Profile  Reply with Quote
Can you explain what is the problem ?

Also we can't see the last image

KH
Time is always against us
Go to Top of Page

masond
Posting Yak Master

241 Posts
Posted - 08/29/2012 :  02:25:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 08/29/2012 :  12:53:32  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000