x

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

more ▼

asked Aug 13 '12 at 04:25 PM in Default

2ramgopal gravatar image

2ramgopal
30 3 3 5

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

answered Aug 13 '12 at 06:21 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

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 '12 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 '12 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 '12 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
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...
more ▼

answered Aug 14 '12 at 08:02 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 13 20 44

Thanks for the new alternate... added to my learnings Thomas :)
Aug 16 '12 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...

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 &quot;PatID&quot; 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
   )
more ▼

answered Aug 17 '12 at 11:04 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

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

x2

asked: Aug 13 '12 at 04:25 PM

Seen: 1426 times

Last Updated: Aug 17 '12 at 11:09 AM