query question - 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)

query question

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2002-10-23 : 14:30:01

Hi,
I have a tough (at least I think so) query. I have three tables. tblUser (user info), tblEmailLog (log of emails sent to user), tblUpdateLog (log of update by user). We have a list of users. When there is an update available we send them an email (we record it in the email log). When they get the email they go to our site and get the update (we record it in update log).

I'm writing a report that shows users who have been sent emails but have not yet done an update. I also include the day difference betweent two events and the total number of updates they have completed. This part works ;-)

***My employer now wants to see the number of emails sent to the user since the user's last update. (ugh, I can't get this to work)***

CREATE TABLE tblUser (
cust_key int,
cust_name varchar(50)
)
GO

CREATE TABLE tblEmailLog (
emlg_key int,
cust_key int,
emlg_date datetime
)
GO

CREATE TABLE tblUpdateLog (
updlg_key int,
cust_key int,
updlg_date datetime
)
GO

INSERT INTO tblUser VALUES(1,'Joe')
GO
INSERT INTO tblUser VALUES(2,'Mary')
GO
INSERT INTO tblEmailLog VALUES(1,1,'1/1/2002')
GO
INSERT INTO tblEmailLog VALUES(2,1,'2/1/2002')
GO
INSERT INTO tblEmailLog VALUES(3,1,'3/1/2002')
GO
INSERT INTO tblEmailLog VALUES(4,2,'1/2/2002')
GO
INSERT INTO tblEmailLog VALUES(5,2,'2/2/2002')
GO
INSERT INTO tblEmailLog VALUES(6,2,'3/2/2002')
GO
INSERT INTO tblUpdateLog VALUES(1,1,'1/3/2002')
GO
INSERT INTO tblUpdateLog VALUES(2,1,'2/3/2002')
GO
INSERT INTO tblUpdateLog VALUES(3,2,'1/4/2002')
GO

This is a working query for the first part
SELECT
a.cust_key
,a.cust_name
,MAX(b.emlg_date) AS emlg_date
,MAX(c.updlg_date) AS update_date
,DATEDIFF(day, MAX(c.updlg_date), MAX(b.emlg_date)) AS DateDifference
,COUNT(DISTINCT c.updlg_key) AS TotalUpdateCount
FROM
tblUser a INNER JOIN tblEmailLog b ON a.cust_key = b.cust_key
LEFT OUTER JOIN tblUpdateLog c ON a.cust_key = c.cust_key

GROUP BY
a.cust_key
,a.cust_name

I don't know how to also display (per user) the number of emails they have been sent since thier last update. Do I need to make a derived table or something??? I'm stuck.... Any help would be appreciated.

Nic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-23 : 14:58:24

How about this:


SELECT 
a.cust_key 
,a.cust_name 
,MAX(b.emlg_date) AS emlg_date 
,MAX(c.updlg_date) AS update_date 
,DATEDIFF(day, MAX(c.updlg_date), MAX(b.emlg_date)) AS DateDifference 
,COUNT(DISTINCT c.updlg_key) AS TotalUpdateCount
,(SELECT COUNT(*) FROM #tblEmailLog b1 INNER JOIN #tblUser a1 on a1.cust_key = b1.cust_key WHERE b1.emlg_date > MAX(c.updlg_date)) as EmailsSinceLastUpdate
FROM 
tblUser a INNER JOIN tblEmailLog b ON a.cust_key = b.cust_key 
LEFT OUTER JOIN tblUpdateLog c ON a.cust_key = c.cust_key 

GROUP BY 
a.cust_key 
,a.cust_name

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Edited by - michaelp on 10/23/2002 14:59:33

Edited by - michaelp on 10/23/2002 15:01:09

nic
Posting Yak Master

209 Posts

Posted - 2002-10-23 : 15:22:55

Thanks but it doesn't give correct results. (can't exactly tell why.)

The problem I'm having is getting the email count per user's last update date. (I can't just take max(updlg_date), it needs to be per user.) Anyway here is a query that seems to work. I sure there is a better way, but here is my next stab at the problem.

SELECT
a.cust_key
,a.cust_name
,MAX(b.emlg_date) AS emlg_date
,MAX(c.updlg_date) AS update_date
,DATEDIFF(day, MAX(c.updlg_date), MAX(b.emlg_date)) AS DateDifference
,COUNT(DISTINCT c.updlg_key) AS TotalUpdateCount
,dt1.EmailCount
FROM
tblUser a INNER JOIN tblEmailLog b ON a.cust_key = b.cust_key
LEFT OUTER JOIN tblUpdateLog c ON a.cust_key = c.cust_key
INNER JOIN
(
SELECT
dt2.cust_key
,COUNT(dt2.emlg_key) AS EmailCount
FROM
tblEmailLog dt2 INNER JOIN
(
SELECT
cust_key
,MAX(updlg_date) AS LastUpdate
FROM
tblUpdateLog
GROUP BY
cust_key
) AS dt3 ON dt2.cust_key = dt3.cust_key
WHERE
dt2.emlg_date > dt3.LastUpdate
GROUP BY
dt2.cust_key
) AS dt1 ON b.cust_key = dt1.cust_key
GROUP BY
a.cust_key
,a.cust_name
,dt1.EmailCount

Is there a better way to do this??

Nic

nic
Posting Yak Master

209 Posts

Posted - 2002-10-23 : 15:29:28

Side Note: Is there an easy way to format sql statements on this site, so tabs display properly?? My code is difficult to read (mostly due to content...)

Nic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-23 : 20:33:09

I made a stupid mistake. I guess that's what I get for trying to beat Rob to an answer :)

This should work. Joe should have 1 EmailsSinceLastUpdate, and Mary should have 2 EmailsSinceLastUpdate.



SELECT 
a.cust_key 
,a.cust_name 
,MAX(b.emlg_date) AS emlg_date 
,MAX(c.updlg_date) AS update_date 
,DATEDIFF(day, MAX(c.updlg_date), MAX(b.emlg_date)) AS DateDifference 
,COUNT(DISTINCT c.updlg_key) AS TotalUpdateCount 
,(SELECT COUNT(*) FROM tblEmailLog b1 WHERE a.cust_key = b1.cust_key AND b1.emlg_date > MAX(c.updlg_date)) as EmailsSinceLastUpdate
FROM 
tblUser a INNER JOIN tblEmailLog b ON a.cust_key = b.cust_key 
LEFT OUTER JOIN tblUpdateLog c ON a.cust_key = c.cust_key 

GROUP BY 
a.cust_key 
,a.cust_name

Michael

PS. To format your code, click on the "#" button at teh top of the input form That shoudl put two [code] things in your post. Put your SQL between those to tags.

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources