x

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
more ▼

asked Oct 02 '12 at 03:59 PM in Default

2ramgopal gravatar image

2ramgopal
30 3 3 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Oct 03 '12 at 08:55 AM

mjharper gravatar image

mjharper
1.3k 2 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816

asked: Oct 02 '12 at 03:59 PM

Seen: 544 times

Last Updated: Oct 03 '12 at 08:55 AM