question

2ramgopal avatar image
2ramgopal asked

How to compare same column values rows?

Hi, 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
sqlquery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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 ORDER BY a.ID ASC 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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
1 Like 1 ·
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.
0 Likes 0 ·
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
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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 FROM ( SELECT PatID, Description, EntryDate FROM @VitalData ) AS src PIVOT ( MAX(EntryDate) 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...
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the new alternate... added to my learnings Thomas :)
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
This might work but would need testing to make sure it scales past 2 records... IF OBJECT_ID('Patients') IS NOT NULL DROP TABLE Patients GO CREATE TABLE Patients ( PatID INTEGER , LineID INTEGER , StrangeCode VARCHAR(20) , Description VARCHAR(50) , EntryDate DATETIME ) -- set up your data in a table variable for me to show the concept -- note the additional "PatID" field. * - borrowed from Thomas' answer ;) INSERT INTO Patients 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] , [d].[r_n] 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 )
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.