| Author |
 Topic  |
|
|
masond
Posting Yak Master
241 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
1442 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
Posting Yak Master
241 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 * (2-5) 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
1442 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 * (2-5) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] ) t2
ON t1.[FDMSAccountNo] = t2.[FDMSAccountNo]
--
Chandu |
|
|
|
masond
Posting Yak Master
241 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 * (2-5) as funding FROM [FDMS].[dbo].[Fact_Omnipay_Profitability] ) t2
ON t1.[FDMSAccountNo] = t2.[FDMSAccountNo]
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1442 Posts |
Posted - 11/07/2012 : 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 fdmsaccountno
Note: post sample data of yourtable......
--
Chandu |
Edited by - bandi on 11/07/2012 08:54:48 |
|
|
|
masond
Posting Yak Master
241 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
1442 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 * (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 |
|
|
| |
Topic |
|
|
|
query help
