May 19, 2015 at 8:11 am
I need to get a Contiguous time slots for the cancelled session.
If the session get cancelled I need to get all contiguous sessions that associated wit h the instructor all the car. I have the query but very first session that I am getting is not contiguous by any means.. Please advise. The contiguous session is the session within 30 minutes from start and end date
SELECT s.sessionKey,
s.locationKey,
su.sessionStart,
su.sessionEnd,
su.instructorKey,
su.btwSeatsOverride AS Flag,
s.carKey
FROM session s
JOIN sessionunit su ON su.sessionKey = s.sessionKey
JOIN product p ON p.productKey = s.productKey AND p.productTypeKey IN (2,4)
OUTER APPLY ( SELECT TOP 1
s1.sessionKey
FROM session s1
JOIN sessionunit su1 ON su1.sessionKey = s1.sessionKey
JOIN product p1 ON p1.productKey = s1.productKey AND p1.productTypeKey IN (2,4)
WHERE
su1.instructorKey = su.instructorKey
AND s1.carKey = s.carKey
AND su1.sessionEnd BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
ORDER BY su1.SessionEnd ) t1
OUTER APPLY ( SELECT TOP 1
s2.sessionKey AS NextKey
FROM session s2
JOIN sessionunit su2 ON su2.sessionKey = s2.sessionKey
JOIN product p2 ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
WHERE
su2.instructorKey = su.instructorKey
AND s2.carKey = s.carKey
AND su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
ORDER BY su2.SessionEnd ) t2
WHERE
(su.instructorKey = 39097 OR s.carKey = 133)
AND CONVERT(date, su.sessionStart) = '7/1/2015'
this query returns the following data:
164192NULL2015-07-01 06:00:00.0002015-07-01 08:00:00.00036754NULL133
164196342015-07-01 09:45:00.0002015-07-01 11:45:00.00013783NULL133
164195342015-07-01 11:45:00.0002015-07-01 13:45:00.00013783NULL133
164085132015-07-01 14:00:00.0002015-07-01 16:00:00.00039097NULL133
164096132015-07-01 16:15:00.0002015-07-01 18:15:00.00039097NULL133
164133132015-07-01 18:15:00.0002015-07-01 20:15:00.00039097NULL133
Web programmer
May 19, 2015 at 9:55 am
I wandered into the SQL Server Newbies area to see what kinds of questions are showing up here.
If this is 'Newbie', then I'm in trouble as a full time sql admin, because I can't even read your code.
I could learn something here. I've never seen code with mulitple 'FROM' clauses.... how does it work?!? Do others use this technique as well, anyone?
If I am understanding your question.... you have a long list of entries in a table, and you want to find all entries before and after and entry 'x' which had problems, yes/no?
just based on time?
so... just using words to talk out the problem....
FROM table a
INNER JOIN table a AS a1 ON a.timestamp + 30 minutes> a1.timestamp
AND a.timestamp - 30 minutes< a1.timestamp
You need to finish translating this into proper sql of course.
I'm sure it's doable some way or another....
May 19, 2015 at 10:36 am
DataTherapist (5/19/2015)
I wandered into the SQL Server Newbies area to see what kinds of questions are showing up here.If this is 'Newbie', then I'm in trouble as a full time sql admin, because I can't even read your code.
I could learn something here. I've never seen code with mulitple 'FROM' clauses.... how does it work?!? Do others use this technique as well, anyone?
If I am understanding your question.... you have a long list of entries in a table, and you want to find all entries before and after and entry 'x' which had problems, yes/no?
just based on time?
so... just using words to talk out the problem....
FROM table a
INNER JOIN table a AS a1 ON a.timestamp + 30 minutes> a1.timestamp
AND a.timestamp - 30 minutes< a1.timestamp
You need to finish translating this into proper sql of course.
I'm sure it's doable some way or another....
There is only one FROM clause in the main query. The other FROM clauses are in sub-queries. Here is the code reformatted for better readability:
SELECT
s.sessionKey,
s.locationKey,
su.sessionStart,
su.sessionEnd,
su.instructorKey,
su.btwSeatsOverride AS Flag,
s.carKey
FROM
dbo.session s
INNER JOIN dbo.sessionunit su
ON su.sessionKey = s.sessionKey
INNER JOIN dbo.product p
ON p.productKey = s.productKey AND
p.productTypeKey IN (2,4)
OUTER APPLY ( SELECT TOP 1
s1.sessionKey
FROM
dbo.session s1
INNER JOIN dbo.sessionunit su1
ON su1.sessionKey = s1.sessionKey
INNER JOIN dbo.product p1
ON p1.productKey = s1.productKey AND
p1.productTypeKey IN (2,4)
WHERE
su1.instructorKey = su.instructorKey
AND s1.carKey = s.carKey
AND su1.sessionEnd BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
ORDER BY
su1.SessionEnd ) t1
OUTER APPLY ( SELECT TOP 1
s2.sessionKey AS NextKey
FROM
dbo.session s2
INNER JOIN dbo.sessionunit su2
ON su2.sessionKey = s2.sessionKey
INNER JOIN dbo.product p2
ON p2.productKey = s2.productKey AND
p2.productTypeKey IN (2,4)
WHERE
su2.instructorKey = su.instructorKey
AND s2.carKey = s.carKey
AND su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
ORDER BY
su2.SessionEnd ) t2
WHERE
(su.instructorKey = 39097 OR s.carKey = 133)
AND CONVERT(date, su.sessionStart) = '2015-07-01';
May 19, 2015 at 11:25 am
eirk tsomik (5/19/2015)
I need to get a Contiguous time slots for the cancelled session.If the session get cancelled I need to get all contiguous sessions that associated wit h the instructor all the car. I have the query but very first session that I am getting is not contiguous by any means.. Please advise. The contiguous session is the session within 30 minutes from start and end date
SELECT s.sessionKey,
s.locationKey,
su.sessionStart,
su.sessionEnd,
su.instructorKey,
su.btwSeatsOverride AS Flag,
s.carKey
FROM session s
JOIN sessionunit su ON su.sessionKey = s.sessionKey
JOIN product p ON p.productKey = s.productKey AND p.productTypeKey IN (2,4)
OUTER APPLY ( SELECT TOP 1
s1.sessionKey
FROM session s1
JOIN sessionunit su1 ON su1.sessionKey = s1.sessionKey
JOIN product p1 ON p1.productKey = s1.productKey AND p1.productTypeKey IN (2,4)
WHERE
su1.instructorKey = su.instructorKey
AND s1.carKey = s.carKey
AND su1.sessionEnd BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
ORDER BY su1.SessionEnd ) t1
OUTER APPLY ( SELECT TOP 1
s2.sessionKey AS NextKey
FROM session s2
JOIN sessionunit su2 ON su2.sessionKey = s2.sessionKey
JOIN product p2 ON p2.productKey = s2.productKey AND p2.productTypeKey IN (2,4)
WHERE
su2.instructorKey = su.instructorKey
AND s2.carKey = s.carKey
AND su2.sessionStart BETWEEN DATEADD(m, -29, su.sessionStart) AND DATEADD(m, 29, su.sessionEnd)
ORDER BY su2.SessionEnd ) t2
WHERE
(su.instructorKey = 39097 OR s.carKey = 133)
AND CONVERT(date, su.sessionStart) = '7/1/2015'
this query returns the following data:
164192NULL2015-07-01 06:00:00.0002015-07-01 08:00:00.00036754NULL133
164196342015-07-01 09:45:00.0002015-07-01 11:45:00.00013783NULL133
164195342015-07-01 11:45:00.0002015-07-01 13:45:00.00013783NULL133
164085132015-07-01 14:00:00.0002015-07-01 16:00:00.00039097NULL133
164096132015-07-01 16:15:00.0002015-07-01 18:15:00.00039097NULL133
164133132015-07-01 18:15:00.0002015-07-01 20:15:00.00039097NULL133
It would help if you could post the DDL for the table(s), some sample data (as INSERT INTO statements) that reflects the problem domain, and expected results based on the sample data you provide.
May 19, 2015 at 11:33 am
I have posted a sample data. The only thing I can not figure out how does the first record get in there.Because is not contiguous with any other sessions
Web programmer
May 19, 2015 at 11:36 am
There is only one FROM clause in the main query. The other FROM clauses are in sub-queries. Here is the code reformatted for better readability:
ah yes, I didn't press into it enough....my bad.
There is a lot about this query that I have a hard time getting my head around.
What are the benefits to the OUTER APPLY...
The second OUTER APPLY seems to only duplicate the first one.
May 19, 2015 at 11:41 am
I just use outer apply but can be changed to CROSS APPLY.
The second outer apply is not the same as the first one. this condition is different
AND su2.sessionStart BETWEEN DATEADD(N, -29, su.sessionStart) AND DATEADD(N, 29, su.sessionEnd)
Web programmer
May 19, 2015 at 11:45 am
eirk tsomik (5/19/2015)
I have posted a sample data. The only thing I can not figure out how does the first record get in there.Because is not contiguous with any other sessions
Actually, no you didn't. You posted what your query returns as indicated from your original post:
...
this query returns the following data:
164192 NULL 2015-07-01 06:00:00.000 2015-07-01 08:00:00.000 36754 NULL 133
164196 34 2015-07-01 09:45:00.000 2015-07-01 11:45:00.000 13783 NULL 133
164195 34 2015-07-01 11:45:00.000 2015-07-01 13:45:00.000 13783 NULL 133
164085 13 2015-07-01 14:00:00.000 2015-07-01 16:00:00.000 39097 NULL 133
164096 13 2015-07-01 16:15:00.000 2015-07-01 18:15:00.000 39097 NULL 133
164133 13 2015-07-01 18:15:00.000 2015-07-01 20:15:00.000 39097 NULL 133
Please read the first article I reference below in my signature block. It will walk you through what you need to post and how to post it to get better answers to your SQL questions.
The benefit of doing this extra work is that you will get better answers in return plus tested code.
May 19, 2015 at 11:52 am
The whole Idea is. One session get cancelled i need to know if there are any contiguous sessions that associated with this sessions. The link is done on the start time and endTime. if for example, one contiguous session is found I need to know if there are any contiguous sessions for that session and so on. I hope I made it clear
Web programmer
May 19, 2015 at 12:03 pm
eirk tsomik (5/19/2015)
The whole Idea is. One session get cancelled i need to know if there are any contiguous sessions that associated with this sessions. The link is done on the start time and endTime. if for example, one contiguous session is found I need to know if there are any contiguous sessions for that session and so on. I hope I made it clear
Not really. The unfortunate part is we can't see what you see. More than willing to take shots in the dark but the likely hood my guesses would help are slim and none. Please post the DDL of the table(s), sample data, and expected results based on the sample data.
May 19, 2015 at 12:11 pm
eirk tsomik (5/19/2015)
I have posted a sample data. The only thing I can not figure out how does the first record get in there.Because is not contiguous with any other sessions
Sorry, I am not seeing the sample data. I see a sample of the desired output, but no supporting data or tables to help figure out what you are seeing.
If you could post enough data and DDL to help us repro the problem, then somebody should be able to find an answer for you a lot faster.
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
May 19, 2015 at 12:32 pm
One other question, what version of SQL Server are you running?
May 19, 2015 at 1:23 pm
sql 2012
Web programmer
May 19, 2015 at 1:27 pm
eirk tsomik (5/19/2015)
sql 2012
Any luck on the sample data?
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
May 19, 2015 at 1:37 pm
Here is the sample data
sessionKeylocationKeysessionStartsessionEndinstructorKeyFlagcarKeyproductTypeKey
164192NULL2015-07-01 06:00:00.0002015-07-01 08:00:00.00036754NULL1332
164196342015-07-01 09:45:00.0002015-07-01 11:45:00.00013783NULL1332
164195342015-07-01 11:45:00.0002015-07-01 13:45:00.00013783NULL1332
164085132015-07-01 14:00:00.0002015-07-01 16:00:00.00039097NULL1332
164096132015-07-01 16:15:00.0002015-07-01 18:15:00.00039097NULL1332
164133132015-07-01 18:15:00.0002015-07-01 20:15:00.00039097NULL1332
Web programmer
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply