| Author |
 Topic  |
|
|
masond
Constraint Violating Yak Guru
256 Posts |
 Posted - 11/07/2012 : 05:01:52
|
Hey
Guys really quick random question
i am having problems doing a subtraction and i don’t know why.
This is my 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
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
group by FDMSAccountNo
i want to get my grosssales column – Salesrefund to produce a netfigure
At present my query is giving me an inaccurate figure for eg
gross sales Salesrefund SaleNet
2484691.81 -32893.84 2517585.65
when in fact the salesnet should be 2451797.97
Any ideas why ?
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 11/07/2012 : 05:08:35
|
Can you at least please post the data types?
Too old to Rock'n'Roll too young to die. |
 |
|
|
masond
Constraint Violating Yak Guru
256 Posts |
Posted - 11/07/2012 : 05:19:21
|
Hi Webfred
What do you mean by data types ? string, varchar etc ? |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 11/07/2012 : 05:23:21
|
Yes.
The column Tot_Purch_Amt and the other involved columns - they have a data type we need to know.
Too old to Rock'n'Roll too young to die. |
|
|
|
masond
Constraint Violating Yak Guru
256 Posts |
Posted - 11/07/2012 : 05:30:48
|
Hi webfred
i am unable to find that out at present :( as i dont have rights to the table. :( |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1715 Posts |
Posted - 11/07/2012 : 06:21:30
|
quote:
At present my query is giving me an inaccurate figure for eg
gross sales Salesrefund SaleNet
2484691.81 -32893.84 2517585.65
when in fact the salesnet should be 2451797.97
Do this, u will get 2451797.97
sum([Tot_Purch_Amt])+sum([Refund_Amt]) as SaleNet
--
Chandu |
|
|
|
masond
Constraint Violating Yak Guru
256 Posts |
Posted - 11/07/2012 : 08:03:10
|
HI bandi
I had to do this
Sum([Tot_Purch_Amt])-sum(-[Refund_Amt]) as SaleNet,
this returned the correct value |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1715 Posts |
Posted - 11/07/2012 : 08:07:36
|
quote:
Originally posted by masond
HI bandi
I had to do this
Sum([Tot_Purch_Amt])-sum(-[Refund_Amt]) as SaleNet,
this returned the correct value
okey that is also fine. Why i suggested + means i thought u had Refund_Amt with negative values...............
--
Chandu |
|
|
| |
Topic |
|
Subtraction giving me wrong figure
