I have a somewhat complex query, but what I'm trying to accomplish is this:
I have 3 tables with a User_ID column:
- a User table [Users]
- a table for when a user enters a record [Tagged]
- and a table for when a user modifies a record [Tagged_Mod]
The result that I am looking for is a list of actions (entered/modified) and
the users who did the action. Right now, I am getting the actions for records created
or modified, but the user always show the user who ENTERED the record, even on
the "modified" lines.
Here is the query:
Select tg.Tag_ID, tg.USER_ID, tmo.User_ID, u.First_Name+' '+u.Last_Name AS UserName, tg.Proj_Prefix, tg.Proj_Number, tm.Name AS Template, tg.Payee, tg.Payor,
CASE WHEN tmo.Mod_Date IS NOT NULL THEN 'Update' ELSE 'Tag' END AS User_Action,
CASE WHEN tmo.Mod_Date IS NOT NULL THEN tmo.Mod_Date ELSE tg.Date_Created END AS Action_Date
FROM Tagged tg INNER JOIN Users u ON tg.User_ID = u.User_ID
INNER JOIN Files_Split fp ON tg.Project_ID = fp.Project_ID AND tg.Proj_Number = fp.Proj_Number
INNER JOIN Templates tm ON tg.Temp_ID = tm.Temp_ID
INNER JOIN Projects p ON tg.Project_ID = p.Project_ID
FULL OUTER JOIN Tagged_Mod tmo on tmo.Tag_ID = tg.Tag_ID
WHERE u.First_Name NOT LIKE '%test%' AND u.Last_Name NOT LIKE '%test%' AND p.Name NOT LIKE '%temp%' AND p.Name NOT LIKE '%xml%' AND p.Name NOT LIKE '%train%' AND
fp.AttachWith IS NULL AND (tg.Date_Created >= '2021-02-28' OR tmo.Mod_Date >= '2021-02-28')
ORDER BY UserName, Action_Date, Proj_Prefix, Proj_Number;
Tagged and Tagged_Mod also share a Tag_ID that is unique to each record.
A lot of the other stuff in the query is irrelevant.
For example, I have a record that was entered:
And then later on it was modified:
Tag_ID | User_ID | Mod_Date | Description
569420 | 115 | 2014-6-5 | Update
The result of my query will be two rows, both with that Tag_ID, one as Entered and one as Modified. However, it will show the same User both times, when there are two different user ID's. The row showing the "Modified" action needs to have the Payee and Payor as well.
How do I fix that?
Thanks in advance, I have been stuck on this for a few days now.