Query help needed

Question

Query help needed

ALI-408105

Say Hey Kid

Points: 709
More actions
November 20, 2007 at 1:56 pm

#184001

How can I covert first result set into second one? This is just example. I will have million of admit and discharge rows, each having unique row key(sid).
sid status date1
----------- ---------- ----------
1238272 Discharge 2007-09-28
1234673 Admit 2007-09-26
1230566 Discharge 2007-09-25
1228040 Admit 2007-09-22
stay_no admit_sid discharge_sid admit_date discharge_date
-----------------------------------------------------------------
1 1228040 1230566 2007-09-22 2007-09-25
2 1234673 1238272 2007-09-26 2007-09-28

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

It would help if we knew what the ID is that relates them to each other. Is there a patientID? an encounterID? something? Also - a table name would be useful.

The more accurate you are, the more likely you are to get a cut and paste kind of solution.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 2

I agree with Matt... with the data given, there is no way to relate the correct Discharge to any given Admit... SID certainly won't do it. You need a PatientID.

Credits to Navy Beans says this is yet another "hospital" piece of homework, though 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ALI-408105 Say Hey Kid Points: 709 More actions · Answer 3

create table t1(

id int identity

,client_id int

,status varchar(10)

,date1 datetime)

insert into t1 values(100,'admit', '2007-1-1')

insert into t1 values(100,'discharge', '2007-2-1')

insert into t1 values(200,'admit', '2007-1-1')

insert into t1 values(200,'discharge', '2007-2-1')

insert into t1 values(300,'admit', '2007-1-1')

insert into t1 values(300,'discharge', '2007-2-1')

insert into t1 values(100,'admit', '2007-2-2')

insert into t1 values(100,'discharge', '2007-3-1')

insert into t1 values(100,'admit', '2007-5-2')

insert into t1 values(100,'discharge', '2007-6-1')

select * from t1 order by client_id, date1

id client_id status date1

----------- ----------- ---------- -----------------------

1 100 admit 2007-01-01 00:00:00.000

2 100 discharge 2007-02-01 00:00:00.000

7 100 admit 2007-02-02 00:00:00.000

8 100 discharge 2007-03-01 00:00:00.000

9 100 admit 2007-05-02 00:00:00.000

10 100 discharge 2007-06-01 00:00:00.000

3 200 admit 2007-01-01 00:00:00.000

4 200 discharge 2007-02-01 00:00:00.000

5 300 admit 2007-01-01 00:00:00.000

6 300 discharge 2007-02-01 00:00:00.000

I want below result set from above

stay_no client_id admit_id discharge_id admit_date ischarge_date

------- --------- --------- -------------- -----------------------

1 100 1 2 2007-01-01 2007-02-01

2 100 7 8 2007-02-02 2007-03-01

3 100 9 10 2007-05-02 2007-06-01

4 200 3 4 2007-01-01 2007-02-01

5 300 5 6 2007-01-01 2007-02-01

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 4

Try this out... self join to connect client_ids... first table admit, second table discharge, subquery on second to select top date that is bigger than admit date.

SELECT

ROW_NUMBER()OVER(ORDER BY t1.client_id, t1.date1) as stay_no,

t1.client_id,

t1.id as admit_id,

t2.id as discharge_id,

t1.date1 as admit_date,

t2.date1 as discharge_date

from t1 t1

join t1 t2 on t1.client_id = t2.client_id

and t1.status = 'admit'

and t2.date1 = (select top 1 date1 from t1 t3

where t3.client_id = t1.client_id

and t3.date1 > t1.date1

and t3.status = 'discharge'

order by t3.date1)

and t2.status = 'discharge'

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 5

Sorry formatting got messed up... maybe this is easier to read:

SELECT

ROW_NUMBER()OVER(ORDER BY t1.client_id, t1.date1) as stay_no,

t1.client_id,

t1.id as admit_id,

t2.id as discharge_id,

t1.date1 as admit_date,

t2.date1 as discharge_date

FROM t1 t1 join t1 t2 on t1.client_id = t2.client_id

WHERE t1.status = 'admit'

AND t2.status = 'discharge'

AND t2.date1 =

(SELECT TOP 1 date1

FROM t1 t3

WHERE t3.client_id = t1.client_id

and t3.date1 > t1.date1

and t3.status = 'discharge'

ORDER BY t3.date1)

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

ALI-408105 Say Hey Kid Points: 709 More actions · Answer 6

ALI-408105

Say Hey Kid

Points: 709

November 22, 2007 at 5:34 am

#753375

THANKS FOR THE SQL. IT HELPED ME A LOT.