How to compare same column values rows?


Please see the below example data:

 316 0005B7DCB817 VitalsAckByEmr 2012-03-20 12:07:16.547 
 376 0005B7DCBB3C PatientSelected 2012-03-20 12:28:26.723 
 388 0005B7DCB9A2 VitalsAcquired 2012-03-20 12:29:51.263 

I need to check that flow goes like this 'VitalsAckByEmr' and then 'PatientSelected' and not 'VitalsAckByEmr' and then 'VitalsAcquired'. Please help me how to write a sql query to cheive it. i should compare value after 'VitalsAckByEmr' to be 'PatientSelected'.

Please do the needful.

Thanks, Ram

more ▼

asked Aug 13, 2012 at 04:25 PM in Default

avatar image

30 3 3 7

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

3 answers: sort voted first

You're attempting to order the data in your query. You need to define something to order it by. From the example above, can you order by the first column? It looks to be ordered in the fashion you need. The query would look something like this (but with your column and table names, since I don't know what they are, I made something up).

 SELECT a.Description
 FROM dbo.TableA AS a

This query will retrieve the description column from the table, but it will order it by the first column (ID). It will do this in an ascending manner (ASC), counting up.

more ▼

answered Aug 13, 2012 at 06:21 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

I think I get it, maybe. So the order is by ID still, yes? But you want to compare one value to the next within a result set?

You'll need something like this: WITH CompareValues AS (SELECT a.ID, a.Description, ROW_NUMBER() OVER (ORDER BY ID) AS RowOrder FROM dbo.TableA AS a) SELECT cv.ID FROM CompareValue AS cv JOIN CompareValue as cv2 ON cv.RowOrder + 1 = cv2 WHERE cv.Description = 'whatever' and cv2.Description = 'TheOtherthing'

Again, bear in mind, I'm dummying this out as psuedo-code so it won't just instantly work in your environment.

Aug 13, 2012 at 07:11 PM Grant Fritchey ♦♦

Wait one. What's not working? What have you tried? I need to see evidence of work on your end before I keep doing free work for you. Are you geting paid to provide this solution? What's my cut if I do all the work?

Aug 13, 2012 at 07:53 PM Grant Fritchey ♦♦

Thanks Fritchey for your time and information shared.

I am helping out one of my colleague, so was just playing around to gain some knowledge on the situation he is facing.

Anyways appreciate the knowledge you have shared.

Thanks, Ram

Aug 13, 2012 at 09:04 PM 2ramgopal
(comments are locked)
10|1200 characters needed characters left

Here's an alternative approach.

If you've got a unique value to track a particular patient through this system (eg a PatientID), then you might consider this approach:

 -- set up your data in a table variable for me to show the concept
 -- note the additional "PatID" field.
 declare @VitalData table (PatID integer, LineID integer, StrangeCode varchar(20), Description varchar(50), EntryDate datetime)
 insert into @VitalData values
 (1, 316, '0005B7DCB817', 'VitalsAckByEmr', '2012-03-20 12:07:16.547'), 
 (1, 376, '0005B7DCBB3C', 'PatientSelected', '2012-03-20 12:28:26.723'), 
 (1, 388, '0005B7DCB9A2', 'VitalsAcquired', '2012-03-20 12:29:51.263')

 -- Use a PIVOT table to build a resultset that can be worked against
 SELECT PatID, VitalsAckByEmr, PatientSelected, VitalsAcquired
     SELECT PatID, Description, EntryDate
     FROM @VitalData
 ) AS src
     FOR Description IN ([VitalsAckByEmr], [PatientSelected], [VitalsAcquired])
 ) as pvt

That brings back the following results:

 PatID  VitalsAckByEmr           PatientSelected          VitalsAcquired
 1      2012-03-20 12:07:16.547  2012-03-20 12:28:26.723  2012-03-20 12:29:51.263

If you want to specifically select those PatientID records that don't match the above process, I've added the following records to the @VitalData table:

 (99, 990, 'foo', 'VitalsAckByEmr', '2012-06-01 01:01:01'),
 (99, 991, 'bar', 'PatientSelected', '2012-06-01 01:02:03'),
 (99, 992, 'baz', 'VitalsAcquired', '2012-06-01 00:00')

and you can add a WHERE clause to the outer SELECT, for example:

 where (PatientSelected < VitalsAckByEmr) OR (PatientSelected > VitalsAcquired)

and this time, we get:

 PatID  VitalsAckByEmr           PatientSelected          VitalsAcquired
 99     2012-06-01 01:01:01.000  2012-06-01 01:02:03.000  2012-06-01 00:00:00.000

Just an idea, mind...

more ▼

answered Aug 14, 2012 at 08:02 AM

avatar image

ThomasRushton ♦♦
42.1k 20 57 53

Thanks for the new alternate... added to my learnings Thomas :)

Aug 16, 2012 at 03:07 PM 2ramgopal
(comments are locked)
10|1200 characters needed characters left

This might work but would need testing to make sure it scales past 2 records...

 DROP TABLE Patients

 StrangeCode VARCHAR(20) ,
 Description VARCHAR(50) ,

 -- set up your data in a table variable for me to show the concept
 -- note the additional &quot;PatID&quot; field. * - borrowed from Thomas' answer ;)
 VALUES -- Good Row
 ( 1, 316, '0005B7DCB817', 'VitalsAckByEmr', '2012-03-20 12:07:16.547' ),
 ( 1, 376, '0005B7DCBB3C', 'PatientSelected', '2012-03-20 12:28:26.723' ),
 ( 1, 388, '0005B7DCB9A2', 'VitalsAcquired', '2012-03-20 12:29:51.263' ),
 -- Bad Row
 ( 2, 396, '0005B7DCB817', 'VitalsAckByEmr', '2012-03-20 12:07:16.547' ),
 ( 2, 476, '0005B7DCBB3C', 'VitalsAcquired', '2012-03-20 12:28:26.723' ),
 ( 2, 588, '0005B7DCB9A2', 'PatientSelected', '2012-03-20 12:29:51.263' )

 ;WITH datas
   AS ( SELECT lineid ,
 [p].[Description] ,
 ROW_NUMBER() OVER ( PARTITION BY p.[PatID] ORDER BY [p].[EntryDate] ) AS r_n
    FROM [dbo].[Patients] AS p
 SELECT [p].[PatID] ,
 [d].[Description] ,
 FROM [datas] AS d
 INNER JOIN [dbo].[Patients] AS p ON [d].lineid = p.[LineID]
 WHERE ( [d].[Description] != 'VitalsAckByEmr'
   AND [d].r_n = 1
 OR ( [d].[Description] != 'PatientSelected'
 AND [d].r_n = 2
 OR ( [d].[Description] != 'VitalsAcquired'
 AND [d].r_n = 3
more ▼

answered Aug 17, 2012 at 11:04 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(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



Answers and Comments

SQL Server Central

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



asked: Aug 13, 2012 at 04:25 PM

Seen: 4660 times

Last Updated: Aug 17, 2012 at 11:09 AM

Copyright 2018 Redgate Software. Privacy Policy