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, 2012 at 03:59 PM in Default

avatar image

2ramgopal
30 3 3 7

(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, 2012 at 08:55 AM

avatar image

mjharper
2k 3 7 13

(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.

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:

x2079

asked: Oct 02, 2012 at 03:59 PM

Seen: 707 times

Last Updated: Oct 03, 2012 at 08:55 AM

Copyright 2016 Redgate Software. Privacy Policy