How to write query to illustrate new/removed ID on a daily basis?

[jimmydean25]

Good day PHPFreaks!

 

I may have a simple Q for you freaks;

 

I have a MySQL 5 db which is populated by a loads of information regarding user ids/authorities from our mainframe. My task is to have a very quick way of showing he creation or deletion of any user id on the system.

 

I have two main fields; DATE and a USERID which are being queried and inserted into a mySQL table on a daily basis.

 

Example;

 

DATE          UserID

7/10/2007  jimmydean

7/10/2007  louis

7/11/2007  jimmydean

7/11/2007  louis

7/12/2007  maddy

7/12/2007  louis

 

 

So the magical questions are how do I show the creation of maddy on the 7/11/2007 on that days report and  how do I show the absence of jimmydean on 7/12/2007's report? I have thousands of userIDs on the system, so a simple query showing the diff would be very helpful! Just a list of the changes would be perfecto! (though it must explicitly specify if a userid was created or deleted)

 

 

Any help would be appreciated!

 

 

[Wildbug]

mysql> SELECT * FROM temp;
+------------+-----------+
| date       | userid    |
+------------+-----------+
| 2007-07-10 | jimmydean |
| 2007-07-10 | louis     |
| 2007-07-11 | jimmydean |
| 2007-07-11 | louis     |
| 2007-07-12 | maddy     |
| 2007-07-12 | louis     |
+------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT @maxdate:=MAX(date) FROM temp;
+---------------------+
| @maxdate:=MAX(date) |
+---------------------+
| 2007-07-12          |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT
userid,
MIN(date) AS created,
IF(MAX(date)=@maxdate AND MAX(date) <> MIN(date),MAX(date),"---") AS deleted,
IF(MAX(date)=@maxdate AND MAX(date) <> MIN(date),"deleted",'current') AS status
FROM temp
GROUP BY userid;
+-----------+------------+------------+---------+
| userid    | created    | deleted    | status  |
+-----------+------------+------------+---------+
| jimmydean | 2007-07-10 | ---        | current |
| louis     | 2007-07-10 | 2007-07-12 | deleted |
| maddy     | 2007-07-12 | ---        | current |
+-----------+------------+------------+---------+
3 rows in set (0.00 sec)

[jimmydean25]

Hmm..

 

Thanks for the quick reply. I am not sure if it works like it should yet.

 

In the example;

 

+------------+-----------+

| 2007-07-10 | jimmydean |

| 2007-07-10 | louis        |

| 2007-07-11 | jimmydean |

| 2007-07-11 | louis        |

| 2007-07-12 | maddy      |

| 2007-07-12 | louis        |

+------------+-----------+

 

and the result from your query

 

+-----------+------------+------------+---------+

| userid    | created    | deleted    | status  |

+-----------+------------+------------+---------+

| jimmydean | 2007-07-10 | ---            | current |

| louis        | 2007-07-10 | 2007-07-12 | deleted |

| maddy      | 2007-07-12 | ---            | current |

 

"jimmydean" is supposed to be shown as Deleted which he does not appear to be. Also, Louis is not deleted he is current -

 

I tried this on my own temp table and it does the same thing -

 

???

 

 

[Wildbug]

You're absolutely right.  I shouldn't code when I'm tired. 

 

Here's a better solution:

 

SELECT @maxdate:=MAX(date) FROM temp;
SELECT
t1.userid,
t1.date,
IF(t1.date=t2.created,"Created","Deleted") AS status
FROM
temp AS t1 JOIN
(SELECT userid,MIN(date) AS created,MAX(date) AS last FROM temp GROUP BY userid) AS t2
	ON t1.userid=t2.userid AND (t1.date=t2.created OR t1.date=t2.last AND t1.date!=@maxdate);

mysql> SELECT @maxdate:=MAX(date) FROM temp;
+---------------------+
| @maxdate:=MAX(date) |
+---------------------+
| 2007-07-12          |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT t1.userid,t1.date,IF(t1.date=t2.created,"Created","Deleted") AS status FROM temp AS t1 JOIN (SEL
ECT userid,MIN(date) AS created,MAX(date) AS last FROM temp GROUP BY userid) AS t2 ON t1.userid=t2.userid AND
(t1.date=t2.created OR t1.date=t2.last AND t1.date!=@maxdate);
+-----------+------------+---------+
| userid    | date       | status  |
+-----------+------------+---------+
| jimmydean | 2007-07-10 | Created |
| louis     | 2007-07-10 | Created |
| jimmydean | 2007-07-11 | Deleted |
| maddy     | 2007-07-12 | Created |
+-----------+------------+---------+
4 rows in set (0.00 sec)

 

(edit)

 

Oh, note that you can do the whole thing in one query if you replace the session variable with a subquery.

 

SELECT
t1.userid,
t1.date,
IF(t1.date=t2.created,"Created","Deleted") AS status
FROM
temp AS t1 JOIN
(SELECT userid,MIN(date) AS created,MAX(date) AS last FROM temp GROUP BY userid) AS t2
	ON t1.userid=t2.userid AND (t1.date=t2.created OR t1.date=t2.last AND t1.date!=(SELECT MAX(date) FROM temp));

[jimmydean25]

That works much better ! I like the use of the subquery.

 

 

Now how to display only the users that were created or deleted on the current (max) date?

 

Thanks,

 

J

 

 

[Wildbug]

You can't check for deleted users until the day after since you make that judgement based on the user's absence, so this following query will work for changes at the most recent date:

 

SELECT
    userid,
    CASE
        WHEN MIN(date)=(SELECT MAX(date) FROM temp) THEN "Created"
        WHEN MAX(date)=(SELECT MAX(date) FROM temp) - INTERVAL 1 DAY THEN "Deleted"
        ELSE ""
    END AS Status
FROM temp
WHERE date >= (SELECT MAX(date) FROM temp) - INTERVAL 1 DAY
GROUP BY userid
HAVING Status <> "";


+-----------+---------+
| userid    | Status  |
+-----------+---------+
| jimmydean | Deleted |
| maddy     | Created |
+-----------+---------+
2 rows in set (0.00 sec)

[jimmydean25]

Very cool! Thanks alot!

 

Since you are exceptional, could I hit upon you for one more?

 

 

I would also like to track changes to authorities. For example, adding a special or object authority to a user id. I am still capturing and placing the info on a daily basis into separate fields , just like the user ids.

 

Is there a way to find the change from the previous day and then list the User ID and the item that changed? (Maybe even show the previous day value then the new one if fanciness is a possibility).

 

Example;

 

DATE | USERID | GROUP  | SPCLATH

 

2007/7/12 | jimmydean | admin | obj1

2007/7/13 | jimmydean | admin | obj1 obj2 obj3

2007/7/14 | jimmydean | user  | obj1 obj2  obj3

 

1.  Ideally the query  run on 7/13 would show the addition of obj2 and ob3 to user id jimmydean (and possibly print the 7/12/2007 SPCLATH value and the 7/13/2007 value with the user id.

2.  Ideally the  query run on 7/14 would show the group field change for user id jimmydean on the 14th from admin to user (and show both the new and the old value).

 

Thanks for all the help!

 

J

 

[Wildbug]

Since you are exceptional, could I hit upon you for one more?

 

Sure, as long as you keep saying nice things.... 

 

I would also like to track changes to authorities. For example, adding a special or object authority to a user id. I am still capturing and placing the info on a daily basis into separate fields , just like the user ids.

 

SELECT
t1.date,
t1.userid,
IF(t1.group!=t2.group,CONCAT('"',t2.group,'" changed to "',t1.group,'"'),'') AS "Group changes",
IF(t1.spclath!=t2.spclath,CONCAT('"',t2.spclath,'" changed to "',t1.spclath,'"'),'') AS "spclath changes"
FROM
temp t1
JOIN temp t2 ON (t1.userid=t2.userid AND t2.date=t1.date - INTERVAL 1 DAY)
ORDER BY date,userid;

 

Just add a WHERE clause with the date or the max date subquery to limit the query to those results.

[jimmydean25]

Very nice!

 

I am back from a brief vacation and will start looking at it.

 

Much Obliged!

 

J