Author 
Topic 

masond
Constraint Violating Yak Guru
447 Posts 
Posted  11/07/2012 : 05:53:34

Hey
Guys sorry to bother you again
i need to produce a column called Funding.
The funding formula would be ([Tot_Purch_Amt] x 0.01) /365 x (2 – 5) as funding
My query is
SELECT [FDMSAccountNo], SUM([Tot_Purch_Amt]) as GrossSales, FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] group by FDMSAccountNo
can anybody help


arpana patil
Starting Member
India
24 Posts 
Posted  11/07/2012 : 05:59:55

declare @Tot_Purch_Amt float set @Tot_Purch_Amt='20' select (@Tot_Purch_Amt * 0.01) /365 * (2  5) as funding 


bandi
Flowing Fount of Yak Knowledge
India
2224 Posts 
Posted  11/07/2012 : 06:15:34

Use over() clause..
SELECT distinct [FDMSAccountNo],
SUM([Tot_Purch_Amt]) over(partition by FDMSAccountNo)as GrossSales,
([Tot_Purch_Amt] * 0.01) /365 * (2 – 5) as funding
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
 Chandu 
Edited by  bandi on 11/07/2012 06:16:39 


masond
Constraint Violating Yak Guru
447 Posts 
Posted  11/07/2012 : 08:05:03

Hi Bandi
Thank you for your support
this is my current query
SELECT [FDMSAccountNo], SUM([Tot_Purch_Amt]) as GrossSales, SUM([Refund_Amt])as SalesRefunds, Sum([Tot_Purch_Amt])sum([Refund_Amt]) as SaleNet, SUM([DCC_Purch_Amt])as DCC, SUM([DCC_Refund_Amt])as DCCRefunds, SUM([DCC_Purch_Amt])SUM([DCC_Refund_Amt]) as DCCnet, SUM([Adjs]) as adjustments, sum ([Merch_Commiss]) as Commision FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] group by FDMSAccountNo
How can i add your part of your query into the following above ?
SELECT distinct [FDMSAccountNo], SUM([Tot_Purch_Amt]) over(partition by FDMSAccountNo)as GrossSales, ([Tot_Purch_Amt] * 0.01) /365 * (25) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
Desired result
Fdmsaccountno grosssales salesrefund salenet Funding dcc dccrefunds dccnet adjustments commision



bandi
Flowing Fount of Yak Knowledge
India
2224 Posts 
Posted  11/07/2012 : 08:20:54

See once this............
SELECT t1. [FDMSAccountNo], GrossSales, SalesRefunds, SaleNet, DCC, DCCRefunds, DCCnet, adjustments, Commision, funding
FROM (SELECT
[FDMSAccountNo],
SUM([Tot_Purch_Amt]) as GrossSales,
SUM([Refund_Amt])as SalesRefunds,
Sum([Tot_Purch_Amt])sum([Refund_Amt]) as SaleNet,
SUM([DCC_Purch_Amt])as DCC,
SUM([DCC_Refund_Amt])as DCCRefunds,
SUM([DCC_Purch_Amt])SUM([DCC_Refund_Amt]) as DCCnet,
SUM([Adjs]) as adjustments,
sum ([Merch_Commiss]) as Commision
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
GROUP BY FDMSAccountNo
) t1
INNER JOIN
(SELECT [FDMSAccountNo], ([Tot_Purch_Amt] * 0.01) /365 * (25) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] ) t2
ON t1.[FDMSAccountNo] = t2.[FDMSAccountNo]
 Chandu 


masond
Constraint Violating Yak Guru
447 Posts 
Posted  11/07/2012 : 08:42:32

hi bandi
i have adapted the query to (the following below), but i am getting multiply lines for the FDMsaccountno. I think its due to the period which i am using
is there anyway, i can sum this up into one liner per unique fdmsaccountno ?
SELECT t1. [FDMSAccountNo], period,GrossSales, SalesRefunds, SaleNet, DCC, DCCRefunds, DCCnet, adjustments, Commision, funding FROM (SELECT [FDMSAccountNo], period, SUM([Tot_Purch_Amt]) as GrossSales, SUM([Refund_Amt])as SalesRefunds, Sum([Tot_Purch_Amt])sum([Refund_Amt]) as SaleNet, SUM([DCC_Purch_Amt])as DCC, SUM([DCC_Refund_Amt])as DCCRefunds, SUM([DCC_Purch_Amt])SUM([DCC_Refund_Amt]) as DCCnet, SUM([Adjs]) as adjustments, sum ([Merch_Commiss]) as Commision FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] where year (Period) > 2011 GROUP BY FDMSAccountNo,Period ) t1 INNER JOIN (SELECT [FDMSAccountNo], ([Tot_Purch_Amt] * 0.01) /365 * (25) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] ) t2 ON t1.[FDMSAccountNo] = t2.[FDMSAccountNo]



bandi
Flowing Fount of Yak Knowledge
India
2224 Posts 
Posted  11/07/2012 : 08:50:24

you are calculating this value ([Tot_Purch_Amt] * 0.01) /365 * (25)... right??
what is relationship between FDMSAccountNo and Tot_pur_amt..? If this is onetomany (i.e. for each FDMSAccountNo more than one Tot_pur_amt), then u will get multiplied number of records......
If it is onetoone, then only u will get one liner per unique fdmsaccountno
Note: post sample data of yourtable......  Chandu 
Edited by  bandi on 11/07/2012 08:54:48 


masond
Constraint Violating Yak Guru
447 Posts 
Posted  11/07/2012 : 08:55:32

Hi Bandi
Basically every month there will be a tot_purch_amt for every fdmsaccountno
eg data fdmsaccountno period tot_purch_amt 0001 2012101 1000 0001 2012091 1000 0001 2012081 1000
what i am trying to do, is sum all the tot_purch_amt per fdmsaccountno So final outcome would be this fdmsaccountno tot_purch_amt 0001 3000
From here, i would like to work out funding calculation based on that one liner

Edited by  masond on 11/07/2012 08:56:04 


bandi
Flowing Fount of Yak Knowledge
India
2224 Posts 
Posted  11/07/2012 : 09:04:45

one more thing is: I missed out Period....... See results
SELECT t1. [FDMSAccountNo], period,GrossSales, SalesRefunds, SaleNet, DCC, DCCRefunds, DCCnet, adjustments, Commision, funding
FROM (SELECT
[FDMSAccountNo],
period,
SUM([Tot_Purch_Amt]) as GrossSales,
SUM([Refund_Amt])as SalesRefunds,
Sum([Tot_Purch_Amt])sum([Refund_Amt]) as SaleNet,
SUM([DCC_Purch_Amt])as DCC,
SUM([DCC_Refund_Amt])as DCCRefunds,
SUM([DCC_Purch_Amt])SUM([DCC_Refund_Amt]) as DCCnet,
SUM([Adjs]) as adjustments,
sum ([Merch_Commiss]) as Commision
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
where year (Period) > 2011
GROUP BY FDMSAccountNo,Period
) t1
INNER JOIN
(SELECT DISTINCT [FDMSAccountNo], period, ([Tot_Purch_Amt] * 0.01) /365 * (25) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] ) t2
ON t1.[FDMSAccountNo] = t2.[FDMSAccountNo] AND t1.period = t2.period
 If u want to sum up the funding values then no need of JOINING also....
Is it your requirement? confirm it then do simple SELECT statement..
 Chandu 



Topic 


