July 8, 2008 at 12:08 am
A little help required if possible...
I am trying to create a report that calculates and returns a week-on-week variance both as an integer and as a percentage.
My query looks someting like this:
Select
'Variance' = TodaysTotal - ThisDayLastWeekTotal,
'%Variance' = CAST(((TodaysTotal - ThisDayLastWeekTotal)/ThisDayLastWeekTotal)*100 AS DECIMAL(10,2))
The problem is that while the 'Variance' figure is being calculated correctly,
the '%Variance' is returning as 0.00 where 'Variance' is negative.
It is a requirement for the report to show negative results for both 'Variance' &
'%Variance'
Any suggestions?
July 8, 2008 at 1:28 am
Andy Carroll (7/8/2008)
A little help required if possible...I am trying to create a report that calculates and returns a week-on-week variance both as an integer and as a percentage.
My query looks someting like this:
Select
'Variance' = TodaysTotal - ThisDayLastWeekTotal,
'%Variance' = CAST(((TodaysTotal - ThisDayLastWeekTotal)/ThisDayLastWeekTotal)*100 AS DECIMAL(10,2))
The problem is that while the 'Variance' figure is being calculated correctly,
the '%Variance' is returning as 0.00 where 'Variance' is negative.
It is a requirement for the report to show negative results for both 'Variance' &
'%Variance'
Any suggestions?
Try:
CAST(((TodaysTotal - ThisDayLastWeekTotal)*100)/ThisDayLastWeekTotal AS DECIMAL(10,2))
Regards,
Andras
July 8, 2008 at 1:33 am
Just as an explanation: you are most likely working with integers :), and when you do the division, you loose everything after the decimal point. So even in the "solution" above, do not expect much after the decimal point. You can however convert your data first to decimal like:
select '%Variance' = CAST(((TodaysTotal - ThisDayLastWeekTotal)*100) AS DECIMAL(10,2))/ cast(ThisDayLastWeekTotal AS DECIMAL(10,2))
Regards,
Andras
July 8, 2008 at 5:29 pm
Perfect!!!
Thanks Andras, Ireally appreciate the help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply