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.
Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 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 fundingMy query is SELECT [FDMSAccountNo],SUM([Tot_Purch_Amt]) as GrossSales,FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]group by FDMSAccountNocan anybody help |
|
arpana patil
Starting Member
24 Posts |
Posted - 2012-11-07 : 05:59:55
|
declare @Tot_Purch_Amt floatset @Tot_Purch_Amt='20'select (@Tot_Purch_Amt * 0.01) /365 * (2 - 5) as funding |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-07 : 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 fundingFROM [FDMS].[dbo].[Fact_Omnipay_Profitability] --Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 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 CommisionFROM [FDMS].[dbo].[Fact_Omnipay_Profitability]group by FDMSAccountNoHow 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 * (2-5) as fundingFROM [FDMS].[dbo].[Fact_Omnipay_Profitability]Desired result FdmsaccountnogrosssalessalesrefundsalenetFundingdccdccrefundsdccnetadjustmentscommision |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-07 : 08:20:54
|
See once this............SELECT t1. [FDMSAccountNo], GrossSales, SalesRefunds, SaleNet, DCC, DCCRefunds, DCCnet, adjustments, Commision, fundingFROM (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 ) t1INNER JOIN (SELECT [FDMSAccountNo], ([Tot_Purch_Amt] * 0.01) /365 * (2-5) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] ) t2 ON t1.[FDMSAccountNo] = t2.[FDMSAccountNo] --Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 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, fundingFROM (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 ) t1INNER JOIN (SELECT [FDMSAccountNo], ([Tot_Purch_Amt] * 0.01) /365 * (2-5) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] ) t2 ON t1.[FDMSAccountNo] = t2.[FDMSAccountNo] |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-07 : 08:50:24
|
you are calculating this value ([Tot_Purch_Amt] * 0.01) /365 * (2-5)... right??what is relationship between FDMSAccountNo and Tot_pur_amt..?If this is one-to-many (i.e. for each FDMSAccountNo more than one Tot_pur_amt), then u will get multiplied number of records......If it is one-to-one, then only u will get one liner per unique fdmsaccountnoNote: post sample data of yourtable......--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-07 : 08:55:32
|
Hi Bandi Basically every month there will be a tot_purch_amt for every fdmsaccountno eg datafdmsaccountno period tot_purch_amt0001 2012101 10000001 2012091 10000001 2012081 1000what i am trying to do,is sum all the tot_purch_amt per fdmsaccountno So final outcome would be this fdmsaccountno tot_purch_amt0001 3000From here, i would like to work out funding calculation based on that one liner |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-07 : 09:04:45
|
one more thing is: I missed out Period.......See resultsSELECT t1. [FDMSAccountNo], period,GrossSales, SalesRefunds, SaleNet, DCC, DCCRefunds, DCCnet, adjustments, Commision, fundingFROM (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 CommisionFROM [FDMS].[dbo].[Fact_Omnipay_Profitability]where year (Period) > 2011GROUP BY FDMSAccountNo,Period) t1INNER JOIN (SELECT DISTINCT [FDMSAccountNo], period, ([Tot_Purch_Amt] * 0.01) /365 * (2-5) 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 |
|
|
|
|
|
|
|