Query to Track Changes between entries AUDIT

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

General SQL Server Forums

New to SQL Server Programming

Query to Track Changes between entries AUDIT

Author

Topic

abliss
Starting Member

6 Posts

Posted - 2011-10-26 : 21:14:53

Hello I'm currently trying to write a query to report when a service ticket is switched between sections.

The table structure is as below:

CREATE TABLE _Ticket_Audit(
Ticket_Audit_ID int IDENTITY(1,1) NOT NULL,
Ticket_RecID int NULL,
Ticket_Status_RecID int NULL,
Change_Date datetime NULL,
Section int NULL,
Team int NULL,
Priority int NULL
) GO

A sample set of data is as follows:
Ticket_RecID Ticket_Status_RecID Change_Date Section Team Priority
19581 48 7/12/11 8:39 15 32 4
19581 48 7/12/11 8:40 15 32 4
19581 48 7/12/11 11:09 15 32 4
19581 48 7/12/11 11:09 15 32 4
19581 48 7/12/11 23:44 15 32 4
19581 48 7/12/11 23:48 23 0 4
19581 48 7/12/11 23:48 23 39 4
19581 102 7/12/11 23:48 23 39 4
19581 102 7/24/11 9:32 23 39 4
19581 102 7/24/11 9:33 17 0 4
19581 102 7/24/11 9:33 17 34 4
19581 57 7/24/11 9:33 17 34 4
19581 57 8/23/11 10:38 17 34 4
19581 55 8/23/11 10:38 17 34 4

An example of the results I would need the query to return based off of the above information is as follows:
19581 48 7/12/2011 23:44 15 32 4
19581 48 7/12/2011 23:48 23 0 4
19581 102 7/24/2011 9:32 23 39 4
19581 102 7/24/2011 9:33 17 0 4

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 02:41:48

[code]SELECT t.*
FROM Table t
OUTER APPLY (SELECT TOP 1 Section
FROM Table
WHERE Ticket_RecID = t.Ticket_RecID
AND Ticket_Status_RecID = t.Ticket_Status_RecID
AND Change_Date > t.Change_Date
ORDER BY Change_Date ASC) t1
OUTER APPLY (SELECT TOP 1 Section
FROM Table
WHERE Ticket_RecID = t.Ticket_RecID
AND Ticket_Status_RecID = t.Ticket_Status_RecID
AND Change_Date < t.Change_Date
ORDER BY Change_Date DESC) t2
WHERE t1.Section <> t.Section
OR t2.Section <> t.Section
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abliss
Starting Member

6 Posts

Posted - 2011-11-01 : 21:12:22

Thanks for the help Visakh, it worked out really nice. Never used the "Outer" or "Cross" Apply, it works very nice and efficiently.

quote:

Originally posted by visakh16

SELECT t.*
FROM Table t
OUTER APPLY (SELECT TOP 1 Section	
             FROM Table
             WHERE Ticket_RecID	= t.Ticket_RecID	
             AND Ticket_Status_RecID = t.Ticket_Status_RecID	
             AND Change_Date > t.Change_Date
             ORDER BY Change_Date ASC) t1
OUTER APPLY (SELECT TOP 1 Section	
             FROM Table
             WHERE Ticket_RecID	= t.Ticket_RecID	
             AND Ticket_Status_RecID = t.Ticket_Status_RecID	
             AND Change_Date < t.Change_Date
             ORDER BY Change_Date DESC) t2
WHERE t1.Section <> t.Section
OR t2.Section <> t.Section

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:10:13

welcome

see below for understanding uses of APPLY
http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources