asked
Compare rows inside the same results of the table data.
Hi, I came through a intresting situationd and would request your suggestions to acheive it. I have a table MET with columns ID,Type,Time & Session which when queried through some conditions will result sumthg like this: ID Type Time Session 12 VS 10:58 XYZ 34 VD 10:58 XYZ 56 VK 10:58 XYZ 78 VS 11:04 ABC 90 VD 11:04 ABC 21 VS 11:10 DEF 22 VD 11:10 DEF 23 VK 11:10 DEF So, I m concerned only pattern where VK comes in a session after VS & VD rest of them like 78 & 90 i don't care. Once VK comes i need to chk the previous row VS and make sure it has the same session as it has i.e.,XYZ equal to XYZ and DEF equal to DEF. Please share your options to acheive it. Thanks for your time and appreciate it. Regards, Ram
Does VS have to be the row before VK or just an ID that's less than VK for that session? If it's ok for VS and VD just to have a lower ID than VK I would do this following (if it just has to be the same session you can get rid of the 2nd AND statement in each JOIN): IF OBJECT_ID('tempdb..#ValidSessions') IS NOT NULL DROP TABLE #ValidSessions SELECT DISTINCT m.SESSION INTO #ValidSessions FROM MET AS m INNER JOIN MET AS m2 ON m.SESSION = m2.SESSION AND m2.Type = 'VD' AND
m.ID >
m2.ID INNER JOIN MET AS m3 ON m.SESSION = m3.SESSION AND m3.Type = 'VS' AND
m.ID >
m3.ID WHERE m.Type = 'VK' SELECT * FROM MET AS m INNER JOIN #ValidSessions AS vs ON m.SESSION = vs.SESSION
9 People are following this question.
