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
 Improving SQL Code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/21/2013 :  05:26:36  Show Profile  Reply with Quote

Hey guys

Hope your well

I have produced a query, which is really messy but it works,

I am wondering is there any way i can neaten the code up?



--Calculating Financials Monthly --
SELECT
Dim_Outlet.FDMSAccountNo ,
sum(Fact_Financial_History.hst_sales_amt) as Sales,
sum(Fact_Financial_History.hst_sales_icg)as Interchange,
sum(Fact_Financial_History.Scheme_Fees)as Scheme,
sum(Fact_Financial_History.Funding_Amt) as Funding,
Fact_Financial_History.hst_date_processed
into #Financials
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where fdmsaccountno = '878212363880'
group by Dim_Outlet.FDMSAccountNo ,hst_date_processed

--Calculating Msc Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
month_end_date,
sum(Fact_Fee_History.Retail_amount) as 'MSC'
into #MSC
FROM Dim_Outlet
INNER JOIN Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where SalesMI_Group1 = 'MSC'
and Fee_Code <> 'M01'
and Dim_Outlet.FDMSAccountNo = '878212363880'
group by Dim_Outlet.FDMSAccountNo, Month_end_date


--Calculating Pci Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
month_end_date,
sum(Fact_Fee_History.Retail_amount) as 'PCI'
into #PCI
FROM Dim_Outlet
INNER JOIN Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where fee_Code IN ('42B','42C','42D','42E')
and Dim_Outlet.FDMSAccountNo = '878212363880'
group by Dim_Outlet.FDMSAccountNo, Month_end_date


--Calculating Region Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
Fact_Financial_History.hst_date_processed,
isnull(sum(case when Region = 'UK' Then (hst_sales_amt) else 0 end),0)/ nullif (SUM(hst_sales_amt),0) as [UK%],
isnull(sum(case when Region = 'EU' Then (hst_sales_amt) else 0 end),0)/ nullif (SUM(hst_sales_amt),0) as [EU%],
isnull(sum(case when Region = 'INT' Then (hst_sales_amt) else 0 end),0)/ nullif (SUM(hst_sales_amt),0) as [NT%]
Into #Region
FROM Dim_Outlet
INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
where fdmsaccountno = '878212363880'
group by Dim_Outlet.FDMSAccountNo ,hst_date_processed



-- Joining Financials onto MSC --
select
#Financials.FDMSAccountNo,
#Financials.hst_date_processed,
#Financials.Sales,
#Financials.Funding,
#Financials.Interchange,
#Financials.scheme,
#MSC.MSC
into #Financials2
from #Financials
inner join #MSC on #Financials.FDMSAccountNo = #MSC.FDMSAccountNo
AND #Financials.hst_date_processed = #MSC.Month_end_date

-- Joining PCI onto #fiancials 2 --
select
#Financials2.FDMSAccountNo,
hst_date_processed,
Funding,
Interchange,
Sales,
Scheme,
MSC,
#PCI.PCI,
MSC-Interchange as DI,
MSC-Interchange-Scheme as DIA
into #test
from #Financials2
inner join #PCI on #Financials2.FDMSAccountNo = #PCI.FDMSAccountNo
AND #Financials2.hst_date_processed = #PCI.Month_end_date

-- Joining Region onto PCI --
select
#test.FDMSAccountNo,
#test.hst_date_processed,
Funding,
Interchange,
Sales,
Scheme,
MSC,
PCI,
DI,
DIA,
[UK%],
[EU%],
[NT%]
Into #Results
from #test
inner join #Region on #test.FDMSAccountNo = #Region.FDMSAccountNo
AND #test.hst_date_processed = #Region.hst_date_processed


--Calculating Credit Msc Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
[Month_end_date],
sum(Fact_Fee_History.[Retail_amount]) as 'Credit MSC'
into #CreditMSC
FROM Dim_Outlet
INNER JOIN Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where SalesMI_Group1 = 'MSC'
and Fee_Code <> 'M01'
and [Card_Type] = 'Credit'
and Dim_Outlet.FDMSAccountNo = '878212363880'
group by Dim_Outlet.FDMSAccountNo,[Month_end_date]

--Calculating Debit Msc Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
[Month_end_date],
sum(Fact_Fee_History.[Retail_amount]) as 'Debit MSC'
into #DebitMSC
FROM Dim_Outlet
INNER JOIN Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where SalesMI_Group1 = 'MSC'
and Fee_Code <> 'M01'
and [Card_Type] = 'Debit'
and Dim_Outlet.FDMSAccountNo = '878212363880'
group by Dim_Outlet.FDMSAccountNo,[Month_end_date]

-- Joining debit and credit msc --
Select
#CreditMSC.FDMSAccountNo,
#CreditMSC.[Month_end_date],
[Credit MSC],
[Debit MSC]
Into #DebitCreditMSc
from #CreditMSC
inner join #DebitMSC on #CreditMSC.FDMSAccountNo = #DebitMSC.FDMSAccountNo
and #DebitMSC.Month_end_date =#CreditMSC.Month_end_date


-- Gathering Joining table --
Select
#Results.FDMSAccountNo,
#Results.hst_date_processed,
Funding,
Interchange,
Sales,
Scheme,
MSC,
[Credit MSC],
[Debit MSC],
PCI,
DI,
DIA,
[UK%],
[EU%],
[NT%]
into #Gathering
from #Results
inner join #DebitCreditMSc on #Results.FDMSAccountNo = #DebitCreditMSc.FDMSAccountNo
AND #Results.hst_date_processed = #DebitCreditMSc.Month_end_date




--Calculating Processing Costs Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
[Period],
SUM(Fact_ProcessingCost.[Value]) As 'Processing'
into #Processing
FROM Dim_Outlet
INNER JOIN Fact_ProcessingCost ON Dim_Outlet.FDMSAccountNo = Fact_ProcessingCost.FDMSAccountNo
where Dim_Outlet.FDMSAccountNo = '878212363880'
group by Dim_Outlet.FDMSAccountNo, Period


-- Joining Processing into Gathering table
select
#gathering.FDMSAccountNo,
hst_date_processed,
Funding,
Interchange,
Processing,
Sales,
Scheme,
MSC,
[Credit MSC],
[Debit MSC],
PCI,
DI,
DIA,
[UK%],
[EU%],
[NT%]
Into #Final2
from #Gathering
inner join #Processing on #Gathering.FDMSAccountNo = #Processing.FDMSAccountNo
and #Gathering.hst_date_processed = #Processing.Period


--Calculating Repeatable Income Costs Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
[Month_end_date],
sum(Fact_Fee_History.Retail_amount) as 'Repeatable Income'
into #Repeatableincome
FROM Dim_Outlet INNER JOIN Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where salesMI_Group1 IN ('other', 'N/A')
and repeat_msc = 'Y'
and Dim_Outlet.FDMSAccountNo = '878212363880'
group by Dim_Outlet.FDMSAccountNo,[Month_end_date]


--joining Tables together --
select
#Final2.FDMSAccountNo,
hst_date_processed,
Funding,
Interchange,
Processing,
Sales,
Scheme,
[Repeatable Income],
MSC,
[Credit MSC],
[Debit MSC],
PCI,
DI,
DIA,
[UK%],
[EU%],
[NT%],
MSC-Interchange-Scheme+[Repeatable Income]+Funding-Processing as [Repeatable Contribution]
Into #Results2
from #Final2
Inner join #Repeatableincome on #Final2.FDMSAccountNo = #Repeatableincome.FDMSAccountNo
and #Final2.hst_date_processed = #Repeatableincome.Month_end_date



--Calculating Sales Credit Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
sum(Fact_Financial_History.hst_sales_amt) as [Credit Sales],
Fact_Financial_History.hst_date_processed
into #CreditSales
FROM Dim_Outlet
INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
where Credit_Debit = 'CREDIT CARD'
and FDMSAccountNo = '878212363880'
group by FDMSAccountNo , hst_date_processed

--Calculating Sales Debit Monthly --
SELECT
Dim_Outlet.FDMSAccountNo,
sum(Fact_Financial_History.hst_sales_amt) as [Debit Sales],
Fact_Financial_History.hst_date_processed
into #DebitSales
FROM Dim_Outlet
INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
where Credit_Debit = 'DEBIT CARD'
and FDMSAccountNo = '878212363880'
group by FDMSAccountNo , hst_date_processed

--Joining Debit / Credit tables together --
select
#CreditSales.FDMSAccountNo,
[Credit Sales],
[Debit Sales],
#CreditSales.hst_date_processed
into #DebitCreditSales
from #CreditSales
inner join #DebitSales on #CreditSales.FDMSAccountNo = #DebitSales.FDMSAccountNo
and #DebitSales.hst_date_processed = #CreditSales.hst_date_processed


--Joining Debit / Credit tables Onto Results --
select
#Results2.FDMSAccountNo,
#results2.hst_date_processed,
Funding,
Interchange,
Processing,
Sales,
[Credit Sales],
[Debit Sales],
Scheme,
[Repeatable Income],
MSC,
[Credit MSC],
[Debit MSC],
PCI,
DI,
DIA,
[UK%],
[EU%],
[NT%],
[Repeatable Contribution]
from #Results2
inner join #DebitCreditSales on #Results2.FDMSAccountNo = #DebitCreditSales.FDMSAccountNo
and #DebitCreditSales.hst_date_processed = #Results2.hst_date_processed


bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 08/21/2013 :  06:16:53  Show Profile  Reply with Quote
We can query as follows for the first 7 statements:

--Calculating Financials Monthly --
SELECT
Dim_Outlet.FDMSAccountNo ,
sum(Fact_Financial_History.hst_sales_amt) as Sales,
sum(Fact_Financial_History.hst_sales_icg)as Interchange,
sum(Fact_Financial_History.Scheme_Fees)as Scheme,
sum(Fact_Financial_History.Funding_Amt) as Funding,
Fact_Financial_History.hst_date_processed,
sum(CASE WHEN Dim_Fee_Codes.Fee_Code <> 'M01' AND SalesMI_Group1 = 'MSC' THEN Fact_Fee_History.Retail_amount) as 'MSC',
sum(CASE WHEN Dim_Fee_Codes.fee_Code IN ('42B','42C','42D','42E') THEN Fact_Fee_History.Retail_amount) as 'PCI'
isnull(sum(case when Region = 'UK' Then (hst_sales_amt) else 0 end),0)/ nullif (SUM(hst_sales_amt),0) as [UK%],
isnull(sum(case when Region = 'EU' Then (hst_sales_amt) else 0 end),0)/ nullif (SUM(hst_sales_amt),0) as [EU%],
isnull(sum(case when Region = 'INT' Then (hst_sales_amt) else 0 end),0)/ nullif (SUM(hst_sales_amt),0) as [NT%]
into #test
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
where Dim_Outlet.fdmsaccountno = '878212363880'
AND Fact_Financial_History.hst_date_processed = Fact_Fee_History.Month_end_date
group by Dim_Outlet.FDMSAccountNo ,hst_date_processed

Like the above try other statements
Let me whether the above query working fine or not?

--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/21/2013 :  07:36:55  Show Profile  Reply with Quote
Bandi

I get the following message "Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
"
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 08/21/2013 :  07:39:04  Show Profile  Reply with Quote
sum(CASE WHEN Dim_Fee_Codes.Fee_Code <> 'M01' AND SalesMI_Group1 = 'MSC' THEN Fact_Fee_History.Retail_amount END) as 'MSC',
sum(CASE WHEN Dim_Fee_Codes.fee_Code IN ('42B','42C','42D','42E') THEN Fact_Fee_History.Retail_amount END) as 'PCI'


--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/21/2013 :  08:52:14  Show Profile  Reply with Quote
Hi Bandi

i thought that might be the case.

i double checked the figures and its massively out via doing it your way
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 08/21/2013 :  09:10:13  Show Profile  Reply with Quote
means what will be the optimized way? yours or mine?

--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/21/2013 :  09:16:46  Show Profile  Reply with Quote
when i run the query with the code you provided. it produces incorrect results. Not one figure tallies back, When i run my query the figures match up exact
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 08/21/2013 :  09:26:46  Show Profile  Reply with Quote
quote:
Originally posted by masond

when i run the query with the code you provided. it produces incorrect results. Not one figure tallies back, When i run my query the figures match up exact


Might be the incorrect results caused depends on tables relationship...



--
Chandu
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.23 seconds. Powered By: Snitz Forums 2000