Querying an Audit trail - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Querying an Audit trail

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-10-27 : 07:02:02

Hi all, I have 2 tables.. tblwork tblaudit
tblwork is the main table that contains records based on a ref_number.
tblaudit is an audit trail table that also contains ref_number,status,date_status_changed.
I have bot htables joined based on ref_number but now I need to query based on the status from the audit table..
ie: I want columns from tblwork but I also want columns returned from audit table if status is say finished , inprocess

ie: in the audit table..if status column contains the text "finished" or "inprocess" then return the date_status_changed.. if the status is neither then return null...

My question is..in the audit trail a record could have status of finished AND inprocess.. I need both dates returned..

So I need derived date columns ?
date_status_finished,date_status_inprocess..

How do I write the query so that I only return one row ?
Thx,
Rya..

Sachin.Nand

2937 Posts

Posted - 2009-10-27 : 08:48:29

Please post some sample data & also read more about the partition functions introduced in SQL 2005 in BOL.

PBUH

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-10-27 : 08:56:43

Hiya and thx for replying..
Basically my audit trail table has a datechanged column and a new status column..
I want to query this table and return a datechangedstat1 column where new status = stat1 or return a datechangedstat2 column where new status = stat2

Sachin.Nand

2937 Posts

Posted - 2009-10-27 : 09:04:13

What datatype is status column?

PBUH

Sachin.Nand

2937 Posts

Posted - 2009-10-27 : 09:05:32

Because u mentioned the word "contains" here

"if status column contains the text "finished" or "inprocess""

PBUH

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-10-27 : 10:38:47

Hiya,Status column is just a nvarchar..

Just to explain more..

data from audit can look like below

ref -- status -- datechanged
223 -- inprocess -- 27/08/2009
223 -- finished -- 28/08/2009
224 -- inprocess -- 20/08/2009

I want a query that will display the following
ref -- datechangedTOInprocess -- datechangedTOFinished
223 -- 27/08/2009 -- 28/08/2009

parody
Posting Yak Master

111 Posts

Posted - 2009-10-27 : 11:12:50

You need to pivot the table. Assuming you are using SQL2000 in a worst case scenario you have to code it yourself (see below). If 2005 or 2008 have a read up on the PIVOT and UNPIVOT functions.

I dummied it up so you can run as-is:

CREATE TABLE #mytable(
ref smallint
,[status] varchar(20)
,datechanged char(10))

INSERT INTO #mytable
SELECT '223','inprocess','27/08/2009'
UNION
SELECT '223','finished','28/08/2009'
UNION
SELECT '224','inprocess','20/08/2009'

SELECT * FROM #mytable

SELECT
ref
,max(CASE WHEN status = 'inprocess' THEN datechanged ELSE null END) as datechangedTOInprocess
,max(CASE WHEN status = 'finished' THEN datechanged ELSE null END) as datechangedTOFinished
FROM #mytable
GROUP BY ref

parody
Posting Yak Master

111 Posts

Posted - 2009-10-27 : 11:24:20

I was bored, here is the PIVOT version!

SELECT *
FROM #mytable
PIVOT (max(datechanged) FOR status IN ([inprocess],[finished])) p

parody
Posting Yak Master

111 Posts

Posted - 2009-10-27 : 11:26:55

By the way, SQL only does a case in the background when using PIVOT. Look at the properties of the system aggregate node of your execution plan, the defined values should show you the case statement it uses.

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-10-27 : 11:30:35

Thank you so much parody this is just what I need !!!

can I ask the significance of using MAX ??

also I am using SQL 2005 so should I still go ahead and lookup pivot or just use your code ?

Thx,
Ray..

parody
Posting Yak Master

111 Posts

Posted - 2009-10-27 : 11:36:57

I posted the pivot syntax too... if 2005 then you may as well use the tools available. Makes the code much neater. Always good to read up though rather than use a suggestion blind.

You have to group the result to remove the nulls. If you group you have to aggregate... using max() ensures you get the datechanged value and not the null from the case statement. null is always ordered lower than anything else i.e. will be at the top of ASC and bottom of DESC.

Try running this to understand what it would look like if you didnt group:

SELECT
ref
,CASE WHEN status = 'inprocess' THEN datechanged ELSE null END as datechangedTOInprocess
,CASE WHEN status = 'finished' THEN datechanged ELSE null END as datechangedTOFinished
FROM #mytable

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-10-27 : 12:15:15

Hiya,
I tried the pivot query but I get the error
Incorrect syntax near '('

parody
Posting Yak Master

111 Posts

Posted - 2009-10-27 : 12:36:49

are you sure you are using sql2005. This is the exact error you would get trying to using pivot syntax on 2000.

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-10-27 : 12:51:42

Hiya,
yep defo SQL server 2005..

PIVOT is also grey so I assume that this means sql knows its a special command ?
any other ideas ?

parody
Posting Yak Master

111 Posts

Posted - 2009-10-27 : 13:03:45

works fine for me!! can you run select @@version and send me the results

parody
Posting Yak Master

111 Posts

Posted - 2009-10-27 : 13:05:17

are you using my example or have you altered it to for your own tables? if so ensure it is exactly the same...

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-10-28 : 10:48:08

Thx ,
I have pivot now working but I think I will go with original code as I need to use more than select * as in the Pivot command .
I need to name specific columns and cast as date time..

Thx so much for your help
Ray..

parody
Posting Yak Master

111 Posts

Posted - 2009-10-29 : 05:23:30

You don't have to use select *, i just did that for simplicity... you can alias the pivoted columns, convert them or whatever, join to another table on the pivoted value even.

example:

SELECT
ref
,[inprocess] AS datechangedTOInprocess
,[finished] AS datechangedTOFinished
,CONVERT(char(6),[inprocess]) AS test
FROM #mytable
PIVOT (max(datechanged) FOR status IN ([inprocess],[finished])) p

This is the first result on google for "SQL PIVOT" have a read:
http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources