SQL Help - 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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-04-11 : 13:48:37

This query returns the sales, the quantity ordered by month for a particular product. How can I subtract the "returns or credits"
from qty_ordered? The Sls_Amt is already done, it has a negative in front of the number.

SELECT SUM(Sls_Amt) AS SalesAmount, SUM(cost_amt) AS cost, DATENAME([MONTH], Inv_Dt) AS pMonth, MONTH(Inv_Dt) AS MonthOrder, SUM(Qty_Ordered) AS qty
FROM dash_product_sales
WHERE (Inv_Dt BETWEEN '20050101' AND '20051231')
AND Prod_Cat = 'k57'
GROUP BY DATENAME([MONTH], Inv_Dt), MONTH(Inv_Dt)
ORDER BY MONTH(Inv_Dt)

If it Orig_Ord_Type = 'O' then it shows all of the orders.
Orig_Ord_Type = 'C' would show the credits.

Make sense?

I'm sure I need a case statment, but I'm not sure how I would work it in to this. This is incorrect, but its something like this...

SELECT SUM(Sls_Amt) AS SalesAmount, SUM(cost_amt) AS cost, DATENAME([MONTH], Inv_Dt) AS pMonth, MONTH(Inv_Dt) AS MonthOrder, CASE when Orig_Ord_Type = 'O' SUM(Qty_Ordered) ELSE SUM(Qty_Ordered * -1) END AS qty
FROM dash_product_sales
WHERE (Inv_Dt BETWEEN '20050101' AND '20051231')
AND Prod_Cat = 'k57'
GROUP BY DATENAME([MONTH], Inv_Dt), MONTH(Inv_Dt)
ORDER BY MONTH(Inv_Dt)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 14:13:06

oooh, you were so close.


SELECT	SUM(Sls_Amt) AS SalesAmount
	,SUM(cost_amt) AS cost
	,DATENAME([MONTH], Inv_Dt) AS pMonth
	,MONTH(Inv_Dt) AS MonthOrder
	,SUM(	case 
		when Orig_Ord_Type = 'O' then Qty_Ordered
		else Qty_Ordered * -1
		end
	 ) AS qty 
FROM	dash_product_sales 
WHERE	(Inv_Dt BETWEEN '20050101' AND '20051231')
AND	Prod_Cat = 'k57'
GROUP BY DATENAME([MONTH], Inv_Dt), MONTH(Inv_Dt) 
ORDER BY MONTH(Inv_Dt)

Be One with the Optimizer
TG

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-04-11 : 14:31:20

Thank you so much! I understand. I was close! Next time I will be able to get it on my own.
Thank you again.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 14:37:59

That's the spirit! That's what we like to hear!

btw, I noticed most of your posts have the same generic subject "Sql Help". You should be more specific like:
Sql Help 1
Sql Help 2 ;)

Be One with the Optimizer
TG

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-04-11 : 15:30:24

Yes, you are right I will make the subjects more specific. This is really a great web site to learn SQL, I have learned so much from this place!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-12 : 03:44:14