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 - 2004-10-06 : 12:04:15

This will return the person who took the order and the number of products ordered for the month of October. Boss wants to only count first time orders, so if the customer ordered the product before October 1, they would not get counted.

How can I make this happen?

SELECT SUM(OELINHST_SQL.qty_ordered) AS ComfortTotal2, OEHDRHST_SQL.User_Def_Fld_1
FROM OELINHST_SQL, OEHDRHST_SQL
WHERE OEHDRHST_SQL.ord_no = OELINHST_SQL.ord_no
AND OEHDRHST_SQL.Ord_dt >= '20041001'
AND OEHDRHST_SQL.Ord_dt <= '20041031'
AND OELINHST_SQL.item_no = '98165'
GROUP BY OEHDRHST_SQL.User_Def_Fld_1

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 12:12:45

well isn't that what you're getting??
this:
AND OEHDRHST_SQL.Ord_dt >= '20041001'
AND OEHDRHST_SQL.Ord_dt <= '20041031'
will limit your query only to october so no records before october 1st will be selected...

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-06 : 12:31:56

Yes, it will limit it to only October, but if they have ordered that product in the past, then it can not be counted.

Make sense?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 13:00:14

not to me...

you want to sum orederd items in october, that's what you're doing.
and how are first time orders defined???
so you want to sum also the ones in september...???

Go with the flow & have fun! Else fight the flow

Bitz
Starting Member

19 Posts

Posted - 2004-10-06 : 13:06:00

Try this:

SELECT SUM(CASE WHEN OEHDRHST_SQL.Ord_dt >= '20041001' THEN OELINHST_SQL.qty_ordered ELSE 0 END) AS ComfortTotal2,
OEHDRHST_SQL.User_Def_Fld_1,
SUM(CASE WHEN OEHDRHST_SQL.Ord_dt < '20041001' THEN 1 ELSE 0 END) AS PrevOrders
FROM OELINHST_SQL, OEHDRHST_SQL
WHERE OEHDRHST_SQL.ord_no = OELINHST_SQL.ord_no
--AND OEHDRHST_SQL.Ord_dt >= '20041001'
AND OEHDRHST_SQL.Ord_dt <= '20041031'
AND OELINHST_SQL.item_no = '98165'
GROUP BY OEHDRHST_SQL.User_Def_Fld_1
HAVING PrevOrders = 0

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-06 : 13:14:16

Bitz,

Yes, that is what I am meaning. I get invalid name 'PrevOrders'
with this part HAVING PrevOrders = 0

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-06 : 13:22:39

replace
PrevOrders = 0
with
SUM(CASE WHEN OEHDRHST_SQL.Ord_dt < '20041001' THEN 1 ELSE 0 END) = 0

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-06 : 13:28:43

Thanks Spirit1!

That did it. Thanks to both of you

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-10-06 : 20:49:55

After testing it more, I'm forgeting to test wheather or not a customer has ordered this product before Oct. This promotion is only for new customers that order in October. Is it possible to work OEHDRHST_SQL.cus_no into this statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources