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.
| Author |
Topic |
|
oahu9872
Posting Yak Master
112 Posts |
 Posted - 2006-04-12 : 07:45:39
|
| I have two tables, a list of customers and a list of orders. In the order table there is a customer ID field that matches up to the ID field in the customer table. I need to run a statement that will tell me which customers have not placed an order in the past 9 months. I know I need to do a join, use a NOT IN function as well as a getDATE() one, but I'm not sure how exactly to set it up. So something like select all from both tables where customer ID NOT IN (select all from order table where date is within 9 months). Any help would be appreciated. Thanks |
|
|
oahu9872
Posting Yak Master
112 Posts |
Posted - 2006-04-12 : 08:08:08
|
| This is the code I have so far. It shows who has placed an order in the past 9 months. I need it to show who has not. SELECT DISTINCT dmg_Customer.[Customer ID], dmg_Customer.[Customer Name] FROM dmg_Customer INNER JOIN dmg_OrderON dmg_Customer.[Customer ID]= dmg_Order.[Customer ID]AND dmg_Order.[Date of Order] > DateADD(MONTH, -9, getDate()) |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-04-12 : 08:14:49
|
| somthing like this .. SELECT DISTINCT dmg_Customer.[Customer ID], dmg_Customer.[Customer Name] FROM dmg_CustomerINNER JOIN dmg_OrderON dmg_Customer.[Customer ID]= dmg_Order.[Customer ID]AND dmg_Order.[Date of Order] Not Between GetDate() And DateADD(MONTH, -9, getDate()) If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
oahu9872
Posting Yak Master
112 Posts |
Posted - 2006-04-12 : 09:44:47
|
| that worked great. thanks |
|
|
|
|
|
|
Join Statement