August 11, 2011 at 5:17 pm
I'm trying to get data based on when a certain user was assigned to an entity using an audit table. The audit table is updated everytime the master table is updated. I need to basically generate the start and end dates for when the user was assigned to the entity. I think I have that now by joining the table to itself. The problem occurs when a user has been assigned, unassigned, and then reassigned later.
--This query returns all the audit records for one master table record.
SELECTAT.AuditTable_id
,AT.user_id
,AT.create_date
FROM AuditTable AS AT
WHERE AT.master_table_id = 1
ORDER BY AT.create_date
--You can see in the results that user 102 is in charge from 11/8/2007-1/14/2009, when 203 takes over and is in charge until 3/12/2009. 147 takes over briefly on 3/12 and then 203 takes over again.
7441022007-11-08 17:32:14.823
72452032009-01-14 13:32:55.690
72562032009-01-15 11:19:03.660
72572032009-01-15 11:19:41.613
72582032009-01-15 11:20:19.707
72622032009-01-15 11:27:07.310
78162032009-01-28 15:26:47.047
78172032009-01-28 15:26:57.987
79232032009-02-12 12:04:04.713
101222032010-02-20 14:51:58.130
105081472010-03-12 10:58:49.033
105092032010-03-12 11:38:25.817
105102032010-03-12 11:38:50.600
--Here is what I have so far. Like I said, it sort of works, but it can't handle 203 changing twice.
SELECTAT.user_id
,MIN(AT.create_date) AS 'StartDate'
,MIN(AT2.create_date) AS 'EndDate'
FROM AuditTable AS AT
INNER JOIN AuditTable AS AT2
ON AT.master_table_id = AT2.master_table_id
AND AT.user_id <> AT2.user_id
AND AT.AuditTable_id < AT2.AuditTable_id
WHERE AT.master_table_id = 1
GROUP BY AT.master_table_id, AT.user_id
ORDER BY 2
--The dates all line up, but I need 203 to show up again at the bottom from 3/12/2010 - NULL (or GETDATE()).
1022007-11-08 17:32:14.8232009-01-14 13:32:55.690
2032009-01-14 13:32:55.6902010-03-12 10:58:49.033
1472010-03-12 10:58:49.0332010-03-12 11:38:25.817
I'd like to take the results returned from this and return related data from the master record based on the dates the user was in charge. I was thinking it would be part of a CTE, so if I run SELECT * FROM CTE WHERE user_id = 203, I should get 2 rows from these results.
Does anybody have any idea how you might get the results I'm expecting?
August 11, 2011 at 9:54 pm
You might find this helpful
http://www.sqlservercentral.com/Forums/Topic267061-176-2.aspx#bm1135582
-- Author: Martin Grape
Be sure to read the comments posted by:
--Comment by Jeff Moden
----The only problem is that the code doesn't recognize missing "SysID" of 1.
-- In fact, it doesn't recognize any gap from 1 to x-1 if all the rows between 1 to x-1 are missing.
In addition to receive tested help, please post table definitions, sample data, expected results in a readily consumable format. To learn how to do so quickly and easily click on the first link in my signature block .. by the way the article includes T-SQL code which you can use to do just what I am requesting.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply