help to optimize query (sample data provided)

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

help to optimize query (sample data provided)

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-10-29 : 03:39:25

Hi all,

can below query be optimized ?

SELECT
a1.event_id,
cast(a1.Detail_Text as nvarchar(max)) as ReportName,
cast(a2.Detail_Text as nvarchar(max)) as Folder,
a1.detail_id as A1, a1.detail_type_id as A2,
a2.detail_id as B1, a2.detail_type_id AS B2,
d.User_Name AS UserName,
d.Start_Timestamp AS StartTimeStamp,
e.Event_Type_Description AS EventTypeDescription

FROM AUDIT_DETAIL a1

LEFT JOIN AUDIT_DETAIL a2
ON a2.Event_ID=a1.Event_ID

left join detail_type b --reportname
on b.detail_type_id=a1.detail_type_id

left join detail_type c --folder
on c.detail_type_id=a2.detail_type_id

left join audit_event d
on d.event_ID=a1.event_ID

left join event_type e
on e.event_type_id=d.event_type_id

where
b.detail_type_description ='Object Name'
and c.detail_type_description ='Object Folder Path'

and a1.detail_id in (
select distinct detail_id
from audit_detail
where detail_text like '%PCR874%')

and a2.detail_id in (
select distinct detail_id
from audit_detail
where detail_text like '%PCR874%')

AND d.Start_Timestamp BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0 , GETDATE())-(3000), 0) AND GETDATE()
ORDER BY CAST(a1.Detail_Text as nvarchar(max)), d.Start_Timestamp

---SAMPLE DATA---
declare @audit_detail table(event_id varchar(20), detail_id int, detail_type_id int, detail_text nvarchar(50))
insert into @audit_detail
select '12345', 2, 3, 'Inventory Report - PCR874' union all
select '12345', 3, 43, '/KMSCOM/User Groups/PCR874' union all
select '78910', 1, 3, 'Non Report' union all
select '78910', 2, 43, '/KLSP/New Folder/'
select * from @audit_detail

declare @detail_type table(detail_type_id int, detail_type_description varchar(50))
insert into @detail_type
select 2, 'Universe Name' union all
select 3, 'Object Name' union all
select 43, 'Object Folder Path'
select * from @detail_type

declare @audit_event table(event_id varchar(20), event_type_id decimal(10), user_name varchar(10), start_timestamp datetime )
insert into @audit_event
select '12345', '131073', 'admin', '2009-07-01 02:42:28.000' union all
select '12345', '131073', 'admin', '2009-12-01 12:32:28.000' union all
select '78910', '65542', 'admin','2009-01-11 11:56:28.000' union all
select '78910', '65542', 'admin','2009-10-21 01:42:28.000'
select * from @audit_event

declare @event_type table(event_type_id decimal(10), event_type_description varchar(50) )
insert into @event_type
select '131073', 'Report Viewed Successfully' union all
select '65542', 'Object is Created' union all
select '65544', 'Object is Modified' union all
select '196609', 'Report Viewed Successfully'
select * from @event_type

----EXPECTED RESULT WHICH I NEED TO OPtimize-----
SELECT
a1.event_id,
cast(a1.Detail_Text as nvarchar(max)) as ReportName,
cast(a2.Detail_Text as nvarchar(max)) as Folder,
a1.detail_id as A1, a1.detail_type_id as A2,
a2.detail_id as B1, a2.detail_type_id AS B2,
d.User_Name AS UserName,
d.Start_Timestamp AS StartTimeStamp,
e.Event_Type_Description AS EventTypeDescription

FROM @AUDIT_DETAIL a1

LEFT JOIN @AUDIT_DETAIL a2
ON a2.Event_ID=a1.Event_ID

left join @detail_type b --reportname
on b.detail_type_id=a1.detail_type_id

left join @detail_type c --folder
on c.detail_type_id=a2.detail_type_id

left join @audit_event d
on d.event_ID=a1.event_ID

left join @event_type e
on e.event_type_id=d.event_type_id

where
b.detail_type_description ='Object Name'
and c.detail_type_description ='Object Folder Path'

and a1.detail_id in (
select distinct detail_id
from @audit_detail
where detail_text like '%PCR874%')

and a2.detail_id in (
select distinct detail_id
from @audit_detail
where detail_text like '%PCR874%')

AND d.Start_Timestamp BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0 , GETDATE())-(3000), 0) AND GETDATE()
ORDER BY CAST(a1.Detail_Text as nvarchar(max)), d.Start_Timestamp

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Kristen
Test

22859 Posts

Posted - 2010-10-29 : 04:54:25

"can below query be optimized ?"

Using EXISTS instead of IN will probably help (as you can get rid of the DISTINCT).

You might also be able to change the query to pre-prepare a list of ID so that you only make one pass of the "like '%PCR874%'" clause - which is probably the slowest element of the query.

What is the actual maximum length of Detail_Text? If it is less than 8,000 characters then CAST to that, rather than NVARCHAR(MAX) (acutally, I think the cutoff is 4,000 for Nvarchar and 8,000 for Varchar - do you have 2-byte characters in your data? Chinese or somesuch characters? If not you should be able to stick to varchar

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-10-29 : 05:26:02

beware that: your left joins on the "b" & "c" aliased tables will be implicitly converted to inner joins because of the "where clause" involving those tables.

post the execution plan for the query and we can have a look at seeing if your tables are setup correctly

shaggy
Posting Yak Master

248 Posts

Posted - 2010-10-29 : 08:22:02

stuff this DATEADD(DAY, DATEDIFF(DAY, 0 , GETDATE())-(3000), 0) AND GETDATE() in a variable and use
or else for every statement the function will be executed

waht is the index for all tables

Kristen
Test

22859 Posts

Posted - 2010-10-29 : 09:31:19

"stuff this DATEADD(DAY, DATEDIFF(DAY, 0 , GETDATE())-(3000), 0) AND GETDATE() in a variable and use
or else for every statement the function will be executed"

SQL Query Plan will optimise these out of the query so that they are only executed once - unless I am very much mistaken - so no advantage to you doing it, and maybe even the possibility that if the query planner cannot see the actual values it may choose a sub-optimal query plan as a result.

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-11-01 : 00:01:43

Thank you for all the advice, but i still did not get it.
Below is the sample data/temp table for the table structure.
It has 4 tables, my expected result is the join query, but i need to optimize it for huge data...
The goal is to get all data from @audit_detail which contain the report name (attribute 'Object Name' which event_type_id=3 from table @detail_type) and report folder (attribute 'Object Folder Path' which event_type_id=43 from table @detail_type) AND detail_id must be 2 & 3 (get from distinct detail_id in @audit detail detail_text like '%PCR874%')

declare @audit_detail table(event_id varchar(20), detail_id int, detail_type_id int, detail_text nvarchar(50))
insert into @audit_detail
select '12345', 2, 3, 'Inventory Report - PCR874' union all
select '12345', 3, 43, '/KMSCOM/User Groups/PCR874' union all
select '78910', 1, 3, 'Non Report' union all
select '78910', 2, 43, '/KLSP/New Folder/'
select * from @audit_detail

declare @detail_type table(detail_type_id int, detail_type_description varchar(50))
insert into @detail_type
select 2, 'Universe Name' union all
select 3, 'Object Name' union all
select 43, 'Object Folder Path'
select * from @detail_type

declare @audit_event table(event_id varchar(20), event_type_id decimal(10), user_name varchar(10), start_timestamp datetime )
insert into @audit_event
select '12345', '131073', 'admin', '2009-07-01 02:42:28.000' union all
select '12345', '131073', 'admin', '2009-12-01 12:32:28.000' union all
select '78910', '65542', 'admin','2009-01-11 11:56:28.000' union all
select '78910', '65542', 'admin','2009-10-21 01:42:28.000'
select * from @audit_event

declare @event_type table(event_type_id decimal(10), event_type_description varchar(50) )
insert into @event_type
select '131073', 'Report Viewed Successfully' union all
select '65542', 'Object is Created' union all
select '65544', 'Object is Modified' union all
select '196609', 'Report Viewed Successfully'
select * from @event_type

----EXPECTED RESULT WHICH I NEED TO OPtimize-----
SELECT
a1.event_id,
cast(a1.Detail_Text as nvarchar(max)) as ReportName,
cast(a2.Detail_Text as nvarchar(max)) as Folder,
a1.detail_id as A1, a1.detail_type_id as A2,
a2.detail_id as B1, a2.detail_type_id AS B2,
d.User_Name AS UserName,
d.Start_Timestamp AS StartTimeStamp,
e.Event_Type_Description AS EventTypeDescription

FROM @AUDIT_DETAIL a1

LEFT JOIN @AUDIT_DETAIL a2
ON a2.Event_ID=a1.Event_ID

left join @detail_type b --reportname
on b.detail_type_id=a1.detail_type_id

left join @detail_type c --folder
on c.detail_type_id=a2.detail_type_id

left join @audit_event d
on d.event_ID=a1.event_ID

left join @event_type e
on e.event_type_id=d.event_type_id

where
b.detail_type_description ='Object Name'
and c.detail_type_description ='Object Folder Path'

and a1.detail_id in (
select distinct detail_id
from @audit_detail
where detail_text like '%PCR874%')

and a2.detail_id in (
select distinct detail_id
from @audit_detail
where detail_text like '%PCR874%')

AND d.Start_Timestamp BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0 , GETDATE())-(3000), 0) AND GETDATE()
ORDER BY CAST(a1.Detail_Text as nvarchar(max)), d.Start_Timestamp

~~~who controls the past controls the future, who controls the present controls the past. ¯\(º_o)/¯ ~~~

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-01 : 01:41:33

quote:
Originally posted by Kristen

"can below query be optimized ?"

Using EXISTS instead of IN will probably help (as you can get rid of the DISTINCT).

Exists and In perform the same in most simple cases. The distinct can be removed regardless of which is used, having multiple of the same values inside an IN subquery does not make it return duplicate values.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-01 : 04:27:36

Maya, take the distincts out of the subqueries (used with IN) they're unnecessary.

How long does the query currently take? What are the IO statistics? What's the required execution time?
What indexes exist?

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 04:42:06

Here is a rewrite. But first compare statistics with your original query

Table '#1380ED52'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#1198A4E0'. Scan count 2, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#10A480A7'. Scan count 4, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#128CC919'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

with the statistics of my rewritten query


Table '#1380ED52'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#128CC919'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#1198A4E0'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#10A480A7'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seems a lot better, right? Here is my rewritten query


;WITH cteReportFolder(Event_ID, ReportName, Folder, A1, A2, B1, B2)
AS (
	SELECT		ad.Event_ID,
			MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN CAST(ad.Detail_Text AS NVARCHAR(MAX)) ELSE NULL END) AS ReportName,
			MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN CAST(ad.Detail_Text AS NVARCHAR(MAX)) ELSE NULL END) AS Folder,
			MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN ad.Detail_ID ELSE NULL END) AS A1,
			MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN ad.Detail_Type_ID ELSE NULL END) AS A2,
			MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN ad.Detail_ID ELSE NULL END) AS B1,
			MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN ad.Detail_Type_ID ELSE NULL END) AS B2
	FROM		@Audit_Detail AS ad
	INNER JOIN	@Detail_Type AS dt ON dt.Detail_Type_ID = ad.Detail_Type_ID
				AND dt.Detail_Type_Description IN ('Object Name', 'Object Folder Path')
	WHERE		ad.Detail_Text LIKE '%PCR874%'
	GROUP BY	ad.Event_ID
), cteEventDetails(Event_ID, UserName, StartTimeStamp, EventTypeDescription)
AS (
	SELECT		ae.Event_ID,
			ae.[User_Name] AS UserName,
			ae.Start_Timestamp AS StartTimeStamp,
			et.Event_Type_Description AS EventTypeDescription
	FROM		@Audit_Event AS ae
	LEFT JOIN	@Event_Type AS et ON et.Event_Type_ID = ae.Event_Type_ID
	WHERE		ae.Start_Timestamp >= DATEADD(DAY, DATEDIFF(DAY, 3000, GETDATE()), 0)
			AND ae.Start_Timestamp <= GETDATE()
)
SELECT		rf.Event_ID,
		rf.ReportName,
		rf.Folder,
		rf.A1,
		rf.A2,
		rf.B1,
		rf.B2,
		et.UserName,
		et.StartTimeStamp,
		et.EventTypeDescription
FROM		cteReportFolder AS rf
LEFT JOIN	cteEventDetails AS et ON et.Event_ID = rf.Event_ID
ORDER BY	rf.ReportName,
		et.StartTimeStamp

Enjoy!

N 56°04'39.26"
E 12°55'05.63"

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-11-01 : 04:57:19

hello peter!

wow, does this create any temp table...

~~~who controls the past controls the future, who controls the present controls the past. ¯\(º_o)/¯ ~~~

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-01 : 05:04:11

No temp tables. A CTE is essentially a named subquery.

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 05:17:05

Maya, have you had the time to timetest my suggestion yet?

N 56°04'39.26"
E 12°55'05.63"

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-11-01 : 05:22:49

tq Gila Monster.. im not sure if Crystal report can support this query...

peter, it worked pretty fast! but it didnt cover some spec...

(it should return reportname & folder location of all reports with detail_type_id = 3 & 43 not just %PCR874%), the %PCR874% is for me to filter only detail_id= 2 & 3)...
i added the scenario data :-

declare @audit_detail table(event_id varchar(20), detail_id int, detail_type_id int, detail_text nvarchar(50))
insert into @audit_detail
select '12345', 2, 3, 'Inventory Report - PCR874' union all
select '12345', 3, 43, '/KMSCOM/User Groups/PCR874' union all
select '53254', 2, 3, 'Discovery Report' union all
select '53254', 3, 43, '/KMSDIC/User Groups/' union all
select '78910', 1, 3, 'Non Report' union all
select '78910', 2, 43, '/KLSP/New Folder/'
select * from @audit_detail

declare @detail_type table(detail_type_id int, detail_type_description varchar(50))
insert into @detail_type
select 2, 'Universe Name' union all
select 3, 'Object Name' union all
select 43, 'Object Folder Path'
select * from @detail_type

declare @audit_event table(event_id varchar(20), event_type_id decimal(10), user_name varchar(10), start_timestamp datetime )
insert into @audit_event
select '12345', '131073', 'admin', '2009-07-01 02:42:28.000' union all
select '12345', '131073', 'admin', '2009-12-01 12:32:28.000' union all
select '78910', '65542', 'admin','2009-01-11 11:56:28.000' union all
select '53254', '65542', 'admin','2009-01-11 11:56:28.000' union all
select '53254', '65542', 'admin','2009-01-11 11:56:28.000' union all
select '78910', '65542', 'admin','2009-10-21 01:42:28.000'
select * from @audit_event

declare @event_type table(event_type_id decimal(10), event_type_description varchar(50) )
insert into @event_type
select '131073', 'Report Viewed Successfully' union all
select '65542', 'Object is Created' union all
select '65544', 'Object is Modified' union all
select '196609', 'Report Viewed Successfully'
select * from @event_type

----EXPECTED RESULT WHICH I NEED TO OPtimize-----
SELECT
a1.event_id,
cast(a1.Detail_Text as nvarchar(max)) as ReportName,
cast(a2.Detail_Text as nvarchar(max)) as Folder,
a1.detail_id as A1, a1.detail_type_id as A2,
a2.detail_id as B1, a2.detail_type_id AS B2,
d.User_Name AS UserName,
d.Start_Timestamp AS StartTimeStamp,
e.Event_Type_Description AS EventTypeDescription

FROM @AUDIT_DETAIL a1

LEFT JOIN @AUDIT_DETAIL a2
ON a2.Event_ID=a1.Event_ID

left join @detail_type b --reportname
on b.detail_type_id=a1.detail_type_id

left join @detail_type c --folder
on c.detail_type_id=a2.detail_type_id

left join @audit_event d
on d.event_ID=a1.event_ID

left join @event_type e
on e.event_type_id=d.event_type_id

where
b.detail_type_description ='Object Name'
and c.detail_type_description ='Object Folder Path'

and a1.detail_id in (
select detail_id
from @audit_detail
where detail_text like '%PCR874%')

and a2.detail_id in (
select detail_id
from @audit_detail
where detail_text like '%PCR874%')

AND d.Start_Timestamp BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0 , GETDATE())-(3000), 0) AND GETDATE()
ORDER BY CAST(a1.Detail_Text as nvarchar(max)), d.Start_Timestamp

---PETER's
;WITH cteReportFolder(Event_ID, ReportName, Folder, A1, A2, B1, B2)
AS (
SELECT ad.Event_ID,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN CAST(ad.Detail_Text AS NVARCHAR(MAX)) ELSE NULL END) AS ReportName,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN CAST(ad.Detail_Text AS NVARCHAR(MAX)) ELSE NULL END) AS Folder,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN ad.Detail_ID ELSE NULL END) AS A1,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN ad.Detail_Type_ID ELSE NULL END) AS A2,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN ad.Detail_ID ELSE NULL END) AS B1,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN ad.Detail_Type_ID ELSE NULL END) AS B2
FROM @Audit_Detail AS ad
INNER JOIN @Detail_Type AS dt ON dt.Detail_Type_ID = ad.Detail_Type_ID
AND dt.Detail_Type_Description IN ('Object Name', 'Object Folder Path')
WHERE ad.Detail_Text LIKE '%PCR874%'
GROUP BY ad.Event_ID
), cteEventDetails(Event_ID, UserName, StartTimeStamp, EventTypeDescription)
AS (
SELECT ae.Event_ID,
ae.[User_Name] AS UserName,
ae.Start_Timestamp AS StartTimeStamp,
et.Event_Type_Description AS EventTypeDescription
FROM @Audit_Event AS ae
LEFT JOIN @Event_Type AS et ON et.Event_Type_ID = ae.Event_Type_ID
WHERE ae.Start_Timestamp >= DATEADD(DAY, DATEDIFF(DAY, 3000, GETDATE()), 0)
AND ae.Start_Timestamp <= GETDATE()
)
SELECT rf.Event_ID,
rf.ReportName,
rf.Folder,
rf.A1,
rf.A2,
rf.B1,
rf.B2,
et.UserName,
et.StartTimeStamp,
et.EventTypeDescription
FROM cteReportFolder AS rf
LEFT JOIN cteEventDetails AS et ON et.Event_ID = rf.Event_ID
ORDER BY rf.ReportName,
et.StartTimeStamp

~~~who controls the past controls the future, who controls the present controls the past. ¯\(º_o)/¯ ~~~

Kristen
Test

22859 Posts

Posted - 2010-11-01 : 06:19:11

quote:
Originally posted by GilaMonster

quote:
Originally posted by Kristen

"can below query be optimized ?"

Using EXISTS instead of IN will probably help (as you can get rid of the DISTINCT).

Exists and In perform the same in most simple cases.

Unless there are cases where EXISTS performs worse then IN? then IMHO it would be better practice to use EXISTS so that solution is scalable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 06:40:29

I tried to find such cases here http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 07:00:00

[code];WITH cteReportFolder(Event_ID, ReportName, Folder, A1, A2, B1, B2)
AS (
SELECT ad.Event_ID,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN CAST(ad.Detail_Text AS NVARCHAR(MAX)) ELSE NULL END) AS ReportName,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN CAST(ad.Detail_Text AS NVARCHAR(MAX)) ELSE NULL END) AS Folder,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN ad.Detail_ID ELSE NULL END) AS A1,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Name' THEN ad.Detail_Type_ID ELSE NULL END) AS A2,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN ad.Detail_ID ELSE NULL END) AS B1,
MAX(CASE WHEN dt.Detail_Type_Description = 'Object Folder Path' THEN ad.Detail_Type_ID ELSE NULL END) AS B2
FROM @Audit_Detail AS ad
INNER JOIN @Detail_Type AS dt ON dt.Detail_Type_ID = ad.Detail_Type_ID
AND dt.Detail_Type_Description IN ('Object Name', 'Object Folder Path')
WHERE EXISTS (SELECT * FROM @Audit_Detail AS x WHERE x.Detail_ID = ad.Detail_ID AND x.Detail_Text LIKE '%PCR874%')
GROUP BY ad.Event_ID
), cteEventDetails(Event_ID, UserName, StartTimeStamp, EventTypeDescription)
AS (
SELECT ae.Event_ID,
ae.[User_Name] AS UserName,
ae.Start_Timestamp AS StartTimeStamp,
et.Event_Type_Description AS EventTypeDescription
FROM @Audit_Event AS ae
LEFT JOIN @Event_Type AS et ON et.Event_Type_ID = ae.Event_Type_ID
WHERE ae.Start_Timestamp >= DATEADD(DAY, DATEDIFF(DAY, 3000, GETDATE()), 0)
AND ae.Start_Timestamp <= GETDATE()
)
SELECT rf.Event_ID,
rf.ReportName,
rf.Folder,
rf.A1,
rf.A2,
rf.B1,
rf.B2,
et.UserName,
et.StartTimeStamp,
et.EventTypeDescription
FROM cteReportFolder AS rf
LEFT JOIN cteEventDetails AS et ON et.Event_ID = rf.Event_ID
WHERE rf.ReportName IS NOT NULL
AND rf.Folder IS NOT NULL
ORDER BY rf.ReportName,
et.StartTimeStamp[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources