Checking a table for changes

[Rando]

Hi,

I have a table that gets updated on a weekly basis with the names of active users. I'm trying to make a query that tells me which users are missing from the current week compared to the entire table/all previous weeks. I only want the user to show up once, and it has to be the newest data for that user.

If I do a Select *, this is what the table would look like

Name     Effective_Date
User1	    09/18/10
User2	    09/18/10
User3	    09/18/10
User4	    09/18/10
User5	    09/18/10
User6	    09/18/10
User1	    09/25/10
User2	    09/25/10
User3	    09/25/10
User4	    09/25/10
User5	    09/25/10
User6	    09/25/10
User1	    10/02/10
User2	    10/02/10
User4	    10/02/10
User5	    10/02/10
User6	    10/02/10
User1	    10/09/10
User2	    10/09/10
User4	    10/09/10
User6	    10/09/10

Expected Query Results

Name	    Last_Date
User3	    09/25/10
User5	    10/02/10

How would I write a query that gives me this data? I've read about creating a query that compares the single table as if it were two tables, but I couldn't get that working.

 

[MarkK]

I would be easy to show the Max() date for each user ...

SELECT Name, Max(Effective_Date) As Last_Date
FROM YourTable
GROUP BY Name;

... and whether they are 'missing' or not might be a calculation on Last_Date ...

WHERE Max(Effective_Date) <= Date() - 7

HTH

 

[Rando]

Yeah I've been using Max to display current week, no issues there. Just not sure how to approach listing users that are no longer active compared to previous weeks

 

[MarkK]

Did you notice the GROUP BY clause?

SELECT Name, Max(Effective_Date) AS Last_Date
FROM Table1
GROUP BY Name
HAVING Max(Effective_Date) < Date() - 2;

With your data this SQL returns your expected results.
Cheers,

 

[Rando]

Did you notice the GROUP BY clause?

SELECT Name, Max(Effective_Date) AS Last_Date
FROM Table1
GROUP BY Name
HAVING Max(Effective_Date) < Date() - 2;

With your data this SQL returns your expected results.
Cheers,

Wow, I can't believe I didn't think about that. Thanks a bunch I'll give it a try

 

[Rando]

Alright it works, but it doesn't work if I have more data. It starts creating duplicates because it can't group them. I should have included this in my original post. For example, an associate's position can change from week to week. For this query, I don't really care about that, but I do want to see their last position worked. Here is some more data that is like my table

Name     Effective_Date		Position	
User1	    09/18/10		A
User2	    09/18/10		A
User3	    09/18/10		A
User4	    09/18/10		A
User5	    09/18/10		A
User6	    09/18/10		A
User1	    09/25/10		B
User2	    09/25/10		B
User3	    09/25/10		B
User4	    09/25/10		B
User5	    09/25/10		B
User6	    09/25/10		B
User1	    10/02/10		C
User2	    10/02/10		C
User4	    10/02/10		C
User5	    10/02/10		C
User6	    10/02/10		C
User1	    10/09/10		D
User2	    10/09/10		D
User4	    10/09/10		D
User6	    10/09/10		D

Expected Query Results

Name	    Last_Date		Last_Position
User3	    09/25/10		B
User5	    10/02/10		C

 

[Rando]

Anyone have a suggestion?

 

[MarkK]

SELECT Name, Max(Effective_Date) AS Last_Date, Last(Position) As Last_Position
FROM Table2
GROUP BY Name
HAVING Max(Effective_Date) < Date() - 2;

 

[Rando]

I got a solution;

SELECT T1.UserName, T1.UserDate AS LastDate, T1.UserPosition AS LastPosition
FROM Table1 AS T1 
INNER JOIN (SELECT T2.UserName, Max(T2.UserDate) AS MaxDate 
FROM Table1 AS T2 GROUP BY T2.UserName)  AS T3 
ON (T1.UserName = T3.UserName) AND (T1.UserDate = T3.MaxDate)
WHERE T1.UserDate <>(SELECT Max(T4.UserDate) As LastDate FROM Table1 As T4);

 

[gemma-the-husky]

have a table/query with just the user ids in which you are interested as a start

have a table/query(s) with the results

left join the first to the others, and test/compare as necessary