Sum Query Help - 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 Help

Author

Topic

FData
Starting Member

24 Posts

Posted - 2012-08-23 : 06:12:07

Hey guys

This is my first post so please be gentle

I have written a query which provides me with the info i need

however i need this summed up and i have hit a brick wall. The final layout should look like this http://s12.postimage.org/uzc77stfh/Final_outcome.jpg

Results returning from sql- look like this
http://s14.postimage.org/canhxj5k1/sql_results.jpg

I basically need to produce 2011 figures, 2012 figures and the difference between the two

The columns which i need are in bold. To get the outcome/results the answers are provided next to the bold,

Net sales = Sales- returns sales

msc
interchange = interchange_cost – return_interhchange_cost

Di = 2011 MSC - 2011 Interchange

Scheme fees = net sales 2011 *0.0003

DIA = DI 2011- 2011 scheme fees

GPM% = DIA 2011 / 2011 net sales

other income =Pci + Joining fee + other

Funding = gross sales

processing cost = net sales 2011 /100*0.06

Repeatable contribution = DIA 2011 + other income 2011 + funding 2011 + processing cost 2011

Contribution % = 2011 repeatable contribution / 2011 net sales

my query so far is
USE [Rm_Book_New]
GO
/****** Object: StoredProcedure [dbo].[RM_Account_Build] Script Date: 08/22/2012 16:48:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[RM_Account_Build]
-- Add the parameters for the stored procedure here

AS
BEGIN

SET NOCOUNT ON;

--Build Accounts

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[Rm_accounts]') AND type in (N'U'))
DROP TABLE dbo.[Rm_accounts]

select FDMSAccountNo,Rm_code
Into Rm_accounts
from fdms.dbo.Dim_Outlet
where RM_Account = 'y'

--Financials

select a.FDMSAccountNo,
f.hst_date_processed,
SUM(hst_sales_amt) As Sales,
SUM(hst_Returns_amt) As [Return sales],
SUM(hst_sales_tran) As Transactions,
SUM(hst_sales_ICG) AS [Interchange_Cost],
SUM(hst_returns_icg) As [Return_Interchange_Cost],
SUM(scheme_Fees) As [Scheme_Fees],
SUM(Funding_Amt) As Funding_Amt
into RM_Financial_History
from fdms.dbo.Fact_Financial_History f inner join dbo.Rm_accounts a on f.hst_merchnum = (left(a.FDMSAccountNo,9))

where (a.FDMSAccountNo = '878020388889' )
and (hst_date_processed > '20090101')

GROUP BY a.FDMSAccountNo, f.hst_date_processed

--Fees
;

--need to use a CTE to group together properly

with fees as (
SELECT
a.FDMSAccountNo,
[Month_end_date] as hst_date_processed,
--One of my famous case statements.
--- I need to split out PCI, Also Auth fees hitting Other
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,
--End Case
Sum([Retail_amount]) As Amount,
Sum([Retail_tran_count]) As Trans_Count
FROM FDMS.dbo.Fact_Fee_History AS f INNER JOIN
dbo.Rm_accounts AS a ON a.FDMSAccountNo = f.FDMSAccountNo
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code

where (a.FDMSAccountNo = '878020388889' )
and ([Month_end_date] > '20090101')
group by a.FDMSAccountNo,
[Month_end_date],SalesMI_Group1,Fee_Code )

select FDMSAccountNo,
hst_date_processed,
description,
SUM(Amount) as Amount,
SUM(Trans_Count) as Trans_Count
into RM_Fee_History
from fees
group by FDMSAccountNo,
hst_date_processed,
description

select distinct(description) from RM_Fee_History

--Processing Costs

select a.FDMSAccountNo,
period,
SUM(p.Value) as value
into RM_Processing_costs
from fdms.dbo.Fact_ProcessingCost p
inner join Rm_accounts A on
p.FDMSAccountNo = a.FDMSAccountNo
where (a.FDMSAccountNo = '878020388889' )
and ([period] > '20090101')
group by a.FDMSAccountNo,
period
order by Period

--Need to include agency payaways

--lets do a test breakdown

select fdmsaccountno,[hst_date_processed],

SUM(CASE WHEN rtrim([description]) = 'MSC' THEN [Amount] ELSE 0 END) AS 'MSC',
SUM(CASE WHEN rtrim([description]) = 'Refund Transaction Charge' THEN [Amount] ELSE 0 END) AS [Refund Transaction Charge],
SUM(CASE WHEN rtrim([description]) = 'PCI' THEN [Amount] ELSE 0 END) AS PCI,
SUM(CASE WHEN rtrim([description]) = 'Joining Fee' THEN [Amount] ELSE 0 END) AS [Joining Fee],
--If its not one of the above then.... (if adding above then remove below >:-s)
SUM(CASE WHEN rtrim([description]) not in ('MSC','Refund Transaction Charge','PCI','Joining Fee') THEN [Amount] ELSE 0 END) AS [Other]
Into dbo.tmpbd1
from dbo.RM_Fee_History
group by FDMSAccountNo,hst_date_processed

--Going to use the Processing costs Period / FDMSaccountno as the assumption is no matter what an account will have a fee where as
--Financial Hist or Fee Hist can be null

SELECT
P.FDMSAccountNo,
P.period,
Fee.MSC,
Fee.[Refund Transaction Charge],
Fee.PCI,
Fee.[Joining Fee],
Fee.Other,
Fin.Sales,
Fin.[Return sales],
Fin.Transactions,
Fin.Interchange_Cost,
Fin.Return_Interchange_Cost,
Fin.Scheme_Fees,
Fin.Funding_Amt,
P.value
FROM RM_Processing_costs AS P FULL OUTER JOIN
RM_Financial_History AS Fin ON P.FDMSAccountNo = Fin.FDMSAccountNo AND P.period = Fin.hst_date_processed FULL OUTER JOIN
tmpbd1 AS Fee ON P.FDMSAccountNo = Fee.fdmsaccountno AND P.period = Fee.hst_date_processed

END

FData
Starting Member

24 Posts

Posted - 2012-08-23 : 06:33:19

on a simlar post to masond

his code was

with cte as (
SELECT [fdmsaccountno],

Case when year(hst_date_processed) = '2011' then SUM ([msc]) else 0 end as [2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([msc]) else 0 end as [2012]
,[MSC]
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other]
FROM [Rm_Book_New].[dbo].[tmpbd1]

group by fdmsaccountno,
[MSC]
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other],hst_date_processed )

select fdmsaccountno,SUM([2011]) as [2011 MSC],SUM([2012]) as [2012 MSC],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by fdmsaccountno

which produced the results he desired , i need this , but for the following above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 10:25:46

you need to give us some details on table columns with some sample data

otherwise we cant make out facts like which column represents returns,interchange etc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources