Problem with calucaltion 1

[Cpreston]

Hi

I have a query that works ok (see below)

select sum(TotalSellPrice), sum(TotalCostPrice), sum(TotalVolume)
from OrderHeader
WHERE (DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)AND DateTimeCreated < DATEADD(wk, DATEDIFF
(wk, 0, GETDATE()),8))AND OrderStatus <> 0 AND OrderStatus <> 1 AND OrderStatus <> 2 AND OrderStatus <> 7 AND OrderStatus <>8

what I am trying to do is add in a row that calculates
TotalSellPrice - TotalCostPrice / TotalSellPrice AS Margin

I added this in below the select row but I get eroor messages, how can I add it in and then group by DateTimeCreated

Thanks

 

[fredericofonseca]

try

select DateTimeCreated
     , sum(TotalSellPrice)
     , sum(TotalCostPrice)
     , sum(TotalVolume)
     , (sum(TotalSellPrice) - sum(TotalCostPrice)) / sum(TotalCostPrice) as Margin -- overall margin
     , min(TotalSellPrice - TotalCostPrice / TotalSellPrice) as Lowest_Margin
     , max(TotalSellPrice - TotalCostPrice / TotalSellPrice) as Highest_Margin
     , avg(TotalSellPrice - TotalCostPrice / TotalSellPrice) as Average_Margin
from OrderHeader 
WHERE DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
       AND DateTimeCreated < DATEADD(wk, DATEDIFF (wk, 0, GETDATE()),8)
       AND OrderStatus not in (0, 1 , 2 , 7 , 8)
group by DateTimeCreated

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Thanks I just tried this
select DateTimeCreated
, sum(TotalSellPrice)
, sum(TotalCostPrice)
, sum(TotalVolume)
, (sum(TotalSellPrice) - sum(TotalCostPrice)) / sum(TotalCostPrice) as Margin -- overall margin
-- , min(TotalSellPrice - TotalCostPrice / TotalSellPrice) as Lowest_Margin
-- , max(TotalSellPrice - TotalCostPrice / TotalSellPrice) as Highest_Margin
-- , avg(TotalSellPrice - TotalCostPrice / TotalSellPrice) as Average_Margin
from OrderHeader
WHERE DateTimeCreated >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
AND DateTimeCreated < DATEADD(wk, DATEDIFF (wk, 0, GETDATE()),8)
AND OrderStatus not in (0, 1 , 2 , 7 , 8)
group by DateTimeCreated

And I am getting this message

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.


Any ideas please

 

[Cpreston]

Hi

Ok I have managed to get it working but the calculation does not seem correct

SQl Result 798705.73 652930.0707 3306.856434 0.2232

EXCEL Result 798705.73 652930.0707 145775.6593 0.182514853

798705.73 - 652930.0707 = 145775.6593 / 0.182514853 this would be 18.25%

So any ideas why the SQL is coming up with 0.2232 where in fact the calculation is 0.1825.
Also could this be displayed as a % for example 18.25%

Many thanks

 

[Olaf Doschke]

TotalVolume seems to be wrong in the single records, if sum(TotalVolume) is 3306 instead of sum(TotalSellprice-TotalCostprice)
Anyway, since you compute the margin from (sum(TotalSellPrice) - sum(TotalCostPrice)) / sum(TotalCostPrice), this should be 145775.6593/652930.0707, which is 0.2232..., so SQL has it right and Excel wrong.

Bye, Olaf.

 

[Cpreston]

Hi

Sorry getting slightly confused

798705.73 - 652930.0707 = 145775.6593 / 798705.73 = 0.182514853 this would be 18.25%

I did this calculation on a calculator and I still get 0.1825

Thanks

 

[Cpreston]

Hi

The latest figures showing (the database is always changing due to sales being out on)

Sales 145214.33
Costs 115457.50
Margin 25.77

145214.33 - 115457.50 = 29756.88
29756.88 / 145214.33 = 0.2049 (which is 20.49%)

Does any one have any ideas why SQL is giving 25.77 and the figure should be closer to 20.49

Select (sum(TotalSellPrice) - sum(TotalCostPrice)) / sum(TotalCostPrice)* 100 as Margin
from OrderHeader where DateTimeCreated >= DATEADD(day, datediff(day,0,getdate()),0) AND DateTimeCreated <= DATEADD(day, datediff(day,0,getdate()),0) + 1
AND OrderStatus not in (0,1)

Thanks

 

[Cpreston]

Sorry my mistake I was calculating on the incorrect field

Select (sum(TotalSellPrice) - sum(TotalCostPrice)) / sum(TotalCostPrice)* 100 as Margin

and it should have been

Select (sum(TotalSellPrice) - sum(TotalCostPrice)) / sum(TotalSellPrice)* 100 as Margin

All working now

Thanks

 

[Olaf Doschke]

Well spotted, I was just about to say it's simply the wrong calculation.

Bye, Olaf.