Improving SQL Code - 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

Improving SQL Code

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-21 : 05:26:36

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 06:16:53

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-21 : 07:36:55

Bandi

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 07:39:04

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-21 : 08:52:14

Hi Bandi

i thought that might be the case.

i double checked the figures and its massively out via doing it your way

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 09:10:13

means what will be the optimized way? yours or mine?

--
Chandu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-21 : 09:16:46

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-21 : 09:26:46

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources