How do I do this? - 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

Transact-SQL (2000)

How do I do this?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-06 : 07:45:29

Paul writes "I'm bashing my head against a wall at the moment with a specific SQL Server problem. Can anyone help?

Basically I have two tables - tblUsers and tblOrders. The relevant fields are:

tblUsers.Email
tblUsers.DateJoined
tblUsers.ContactAllowed

tblOrders.Email
tblOrders.Date

Users register but don't all place orders.

I need to know two things. 1. How many users have agreed to be contacted and have not placed orders within a 6 week period.
2. How many users have agreed to be contacted and not placed orders within a 6 week period but have had orders outside of that time period.

I have tried the following but it comes up with the same figure which is unlikely.

SELECT COUNT(DISTINCT tblUsers.Email) AS Expr1
FROM tblUsers LEFT OUTER JOIN
tblOrders ON tblUsers.Email = tblOrders.Email
WHERE (tblUsers.MFIContact = 1) AND (NOT (tblOrders.Date BETWEEN CONVERT(DATETIME, '2004-12-28 00:00:00', 102) AND CONVERT(DATETIME, '2005-02-08 00:00:00', 102))) AND (tblUsers.DateJoined <= CONVERT(DATETIME, '2005-02-08 00:00:00', 102))

SELECT COUNT(DISTINCT tblUsers.Email) AS Expr1
FROM tblUsers LEFT OUTER JOIN
tblOrders ON tblUsers.Email = tblOrders.Email
WHERE (tblUsers.MFIContact = 1) AND (NOT (tblOrders.Date BETWEEN '20041228' AND '20050208')) AND
(tblOrders.Date < CONVERT(DATETIME, '2004-12-28 00:00:00', 102) OR
tblOrders.Date > CONVERT(DATETIME, '2005-02-08 00:00:00', 102)) AND (tblUsers.DateJoined <= CONVERT(DATETIME, '2005-02-08 00:00:00', 102))

Anyone any ideas?!?"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-06 : 09:18:56

quote:
I have tried the following but it comes up with the same figure which is unlikely.

Then post some sample data and the result which is likely

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-07-06 : 09:44:56

Follow the instruction in the link below...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources