Correct use of CTE?

Question

Correct use of CTE?

eyejay

Mr or Mrs. 500

Points: 542
More actions
June 7, 2011 at 9:54 am

#239575

I have the following query:
WITH
Starts as
(SELECT id, date_time
FROM events
WHERE on_code = 1),
Ends as
(SELECT id, date_time
FROM events
WHERE off_code = 2)
SELECT DISTINCT a.id, a.date_time
FROM tbl_1 a
INNER JOIN Starts
ON a.id = Starts.id
INNER JOIN Ends
ON a.id = Ends.id
WHERE
a.date_time
BETWEEN
Starts.date_time
AND
Ends.date_time
ORDER BY a.date_time
which I use to retrieve an ID and date/time field from an events table to allow me to then cross reference other fields in different tables that occur only between the on_code and off_code times. This use of a CTE works perfectly for me if there is only one entry per ID field. However, on occassions the events table has multiple on and off events which means that the CTE tables "Starts" and "Ends" have multiple start and stop date/time fields for the one ID field. If I then use this query to cross reference other tables, based on the ID field, in the "where date_time between..." clause I get all records between the first start time record in "Starts" and the last stop time on "Ends". What I actually want in these cases of multiple start/stop events is just those records that occur between all of the multiple start/stop times.
I'm not sure if the use of a CTE is appropriate in this scenario but if so how do I start re-writing the code. I'm wondering if I need to put a loop in but I'm stumped about where to begin.
Thank you for any help you can offer.
Ian.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Please post DDL for your tables, DML to add some test data that lets your query demonstrate the problem, and your expected results. If you're not sure what I mean please refer to this article:

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I agree with OPC - we need the things he requested so we can better assess and test the issue and solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 3

eyejay

Mr or Mrs. 500

Points: 542

June 7, 2011 at 10:30 am

#1335156

OK will do, thanks.

Ian.

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 4

Apologies for my delay but here is my DDL and DML which I hope I have presented correctly. I have also re-written the CTE at the end of the code which needed a slight modification.

If I need to supply any further information please let me know and I'll happily oblige. I'm sorry that I can't offer any solutions I've tried as I don't know where to start with this.

Thanks again for looking at my problem.

Ian

---- If the tables exists drop them

IF OBJECT_ID('events') IS NOT NULL

DROP TABLE events

IF OBJECT_ID('tbl_1') IS NOT NULL

DROP TABLE tbl_1

---- Create test tables with

CREATE TABLE events

(

PK INT PRIMARY KEY CLUSTERED,

ID INT,

DATE_TIME DATETIME,

EV_CODE INT

)

CREATE TABLE tbl_1

(

PK INT PRIMARY KEY CLUSTERED,

ID INT,

DATE_TIME DATETIME

)

--- This table, tbl_1 has more fields but are not required to

--- demonstrate my problem

----- Insert the test data into the test tables

INSERT INTO events

(PK, ID, DATE_TIME, EV_CODE)

SELECT '1', '1', '2011-06-07 10:10:30.000', '1' UNION ALL

SELECT '2', '1', '2011-06-07 10:20:30.000', '2' UNION ALL

SELECT '3', '1', '2011-06-07 10:25:30.000', '1' UNION ALL

SELECT '4', '1', '2011-06-07 10:40:30.000', '2' UNION ALL

SELECT '5', '1', '2011-06-07 10:45:30.000', '1' UNION ALL

SELECT '6', '1', '2011-06-07 10:55:30.000', '2' UNION ALL

SELECT '7', '2', '2011-06-07 12:10:30.000', '1' UNION ALL

SELECT '8', '2', '2011-06-07 12:50:30.000', '2'

---- EV_CODE 1 = ON; EV_CODE 2 = OFF

---- MULTIPLE ON/OFF EVENTS CAN OCCUR FOR ONE ID eg: ID = 1

INSERT INTO tbl_1

(PK, ID, DATE_TIME)

SELECT '1', '1', '2011-06-07 10:12:30.000' UNION ALL

SELECT '2', '1', '2011-06-07 10:16:30.000' UNION ALL

SELECT '3', '1', '2011-06-07 10:22:30.000' UNION ALL

SELECT '4', '1', '2011-06-07 10:24:30.000' UNION ALL

SELECT '5', '1', '2011-06-07 10:28:30.000' UNION ALL

SELECT '6', '1', '2011-06-07 10:34:30.000' UNION ALL

SELECT '7', '1', '2011-06-07 10:38:30.000' UNION ALL

SELECT '8', '1', '2011-06-07 10:42:30.000' UNION ALL

SELECT '9', '1', '2011-06-07 10:44:30.000' UNION ALL

SELECT '10', '1', '2011-06-07 10:48:30.000' UNION ALL

SELECT '11', '1', '2011-06-07 10:52:30.000' UNION ALL

SELECT '12', '1', '2011-06-07 10:54:30.000' UNION ALL

SELECT '13', '2', '2011-06-07 12:08:30.000' UNION ALL

SELECT '14', '2', '2011-06-07 12:12:30.000' UNION ALL

SELECT '15', '2', '2011-06-07 12:20:30.000' UNION ALL

SELECT '16', '2', '2011-06-07 12:30:30.000' UNION ALL

SELECT '17', '2', '2011-06-07 12:40:30.000' UNION ALL

SELECT '18', '2', '2011-06-07 12:52:30.000'

---- FOR ID = 1 THERE ARE 12 RECORDS THAT OCCUR BETWEEN

---- THE FIRST ON EVENT AND THE LAST OFF EVENT BUT I ONLY

---- WANT THE RECORDS THAT FALL DURING AN ON PERIOD WHICH

---- IS JUST 8 RECORDS (PK: 1,2,5,6,7,10,11 AND 12 IN tbl_1)

---- FOR ID = 2 THERE ARE 6 TOTAL RECORDS BUT ONLY 4

---- ARE INCLUSIVE OF THE ON/OFF EVENTS AND THIS IS THE

---- EXPECTED RESULT.

WITH

Starts as

(SELECT id, date_time

FROM events

WHERE ev_code = 1),

Ends as

(SELECT id, date_time

FROM events

WHERE ev_code = 2)

SELECT DISTINCT a.id, a.date_time

FROM tbl_1 a

INNER JOIN Starts

ON a.id = Starts.id

INNER JOIN Ends

ON a.id = Ends.id

WHERE

a.date_time

BETWEEN

Starts.date_time

AND

Ends.date_time

ORDER BY a.date_time

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

eyejay (6/7/2011)
If I then use this query to cross reference other tables, based on the ID field, in the "where date_time between..." clause I get all records between the first start time record in "Starts" and the last stop time on "Ends". What I actually want in these cases of multiple start/stop events is just those records that occur between all of the multiple start/stop times.

Could you clarify this for me? I'm reading those sentences to mean the same thing.

If you could provide sample output, that would be great.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I read through your notes in the script and think I may have something that will work.

WITH timeframes AS

(SELECT es.id, es.date_time StartTime,EE.date_time AS EndTime

FROM events ES

CROSS APPLY (SELECT PK,id, date_time

FROM events

WHERE ev_code = 2

) EE

WHERE ES.ev_code = 1

AND ES.id = EE.id

AND EE.date_time > es.date_time

AND ES.PK = EE.pk - 1

)

SELECT DISTINCT a.id, a.date_time

FROM tbl_1 a

INNER JOIN timeframes tf

ON a.id = tf.id

WHERE a.date_time BETWEEN tf.StartTime AND tf.EndTime

ORDER BY a.date_time

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 7

Thanks for getting back to me and I will post my expected output, but to try and clarify, currently in a multiple on/off scenario I get all records that have a time between the first start event and the last stop event including those records when the events timer is off. I only want the records when the timer is on, so only between an on then off ev_code and not the records between the off and subsequent on ev_code. Hope this is a bit clearer and will post some expected output but it will be tomorrow now before I get chance (UK bedtime 🙂 ).

Many thanks,

Ian

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

While working on that, please check the last script I posted and see if that works.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 9

Try this

WITH StartAndEnds(id,Start_date_time,End_date_time) AS (

SELECT s.id,s.date_time,MIN(e.date_time)

FROM events s

INNER JOIN events e ON e.id=s.id AND e.date_time>=s.date_time AND e.ev_code = 2

WHERE s.ev_code = 1

GROUP BY s.id,s.date_time)

SELECT t.pk,t.id,t.date_time

FROM tbl_1 t

WHERE EXISTS (SELECT * FROM StartAndEnds s

WHERE s.id=t.id

AND t.date_time BETWEEN s.Start_date_time AND s.End_date_time)

ORDER BY t.date_time;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

eyejay Mr or Mrs. 500 Points: 542 More actions · Answer 10

Jason and Mark,

Both of your solutions work perfectly, thank you so much for taking time to look at my problem and help out. I've learnt something new.

Cheers,

Ian.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Most excellent. Glad it works.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events