dividing - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

dividing

Author

Topic

msugradus
Starting Member

40 Posts

Posted - 2007-06-20 : 14:53:34

I have four sum statements in a procedure and I want to add two more fields that divide thismonthdeclines / thismonthsales, and prevmonthdeclines / premonthsales. I have tried a few ways and I just cant get the syntax right. Can someone help..

ALTER PROCEDURE dbo.sp_MTD_Declines

(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)

as

select

sum(case when O.orderdate between @prevMonthStart and @prevMonthEnd then D.orderlinetotal else 0 end ) as PrevMonthSales,

sum(case when O.orderdate between @thisMonthStart and @thisMonthEnd then D.orderlinetotal else 0 end ) as ThisMonthSales,

sum(case when O.orderdate between @prevMonthStart and @prevMonthEnd then case when O.orderstatus = 'CC Declined' then O.ordertotal end else 0 end) as PrevMonthDeclines,

sum(case when O.orderdate between @thisMonthStart and @thisMonthEnd then case when O.orderstatus = 'CC Declined' then O.ordertotal end else 0 end) as ThisMonthDeclines

from exigo_data_sync.Orders O INNER JOIN
exigo_data_sync.OrderDetail D ON O.OrderID = D.OrderID

where O.orderdate >= @prevMonthStart

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 14:58:13

[code]select 1.0 * w.ThisMonthDeclines / w.ThisMonthSales,
1.0 * w.PrevMonthDeclines / w.PrevMonthSales
from (
select sum(case when O.orderdate < @thisMonthStart then D.orderlinetotal else 0 end) as PrevMonthSales,
sum(case when O.orderdate >= @thisMonthStart then D.orderlinetotal else 0 end) as ThisMonthSales,
sum(case when O.orderdate < @thisMonthStart and O.orderstatus = 'CC Declined' then O.ordertotal else 0 end) as PrevMonthDeclines,
sum(case when O.orderdate >= @thisMonthStart and O.orderstatus = 'CC Declined' then O.ordertotal else 0 end) as ThisMonthDeclines
from exigo_data_sync.Orders As O
INNER JOIN exigo_data_sync.OrderDetail as D ON O.OrderID = D.OrderID
where O.orderdate >= @prevMonthStart
and o.orderdate < 1 + @thisMonthend
) as w[/code]

msugradus
Starting Member

40 Posts

Posted - 2007-06-20 : 15:09:53

ok, it works, kinda. It prints the two divided fields but not the origional 4?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 15:16:01

Just add them to the select list!

select		w.ThisMonthDeclines,
		w.ThisMonthSales,
		w.PrevMonthDeclines,
		w.PrevMonthSales,
		1.0 * w.ThisMonthDeclines / w.ThisMonthSales,
		1.0 * w.PrevMonthDeclines / w.PrevMonthSales
from		(
			select		sum(case when O.orderdate < @thisMonthStart then D.orderlinetotal else 0 end) as PrevMonthSales,
					sum(case when O.orderdate >= @thisMonthStart then D.orderlinetotal else 0 end) as ThisMonthSales, 
					sum(case when O.orderdate < @thisMonthStart and O.orderstatus = 'CC Declined' then O.ordertotal else 0 end) as PrevMonthDeclines,
					sum(case when O.orderdate >= @thisMonthStart and O.orderstatus = 'CC Declined' then O.ordertotal else 0 end) as ThisMonthDeclines
			from		exigo_data_sync.Orders As O
			INNER JOIN	exigo_data_sync.OrderDetail as D ON O.OrderID = D.OrderID 
			where		O.orderdate >= @prevMonthStart
					and o.orderdate < 1 + @thisMonthend
		) as w

Peter Larsson
Helsingborg, Sweden

msugradus
Starting Member

40 Posts

Posted - 2007-06-20 : 16:26:51

Thanks, that works great. Is there any way to format the two percent fields so that the data displays like: 13.6% or .06%

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 16:39:40

Yes, you can use STR function if you want a string to be returned.
If just cutting precision, you can cast as DECIMAL(6, 2).

Peter Larsson
Helsingborg, Sweden

msugradus
Starting Member

40 Posts

Posted - 2007-06-21 : 10:25:41

I guess I am not sure on how to get .13600 to display as 13.6%

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-21 : 10:30:40

quote:
Originally posted by msugradus

I guess I am not sure on how to get .13600 to display as 13.6%

That should be displayed in that format in the front end application you are using.
Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

msugradus
Starting Member

40 Posts

Posted - 2007-06-21 : 10:38:22

I am using access to create a report to display the data...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-21 : 10:40:58