SQL help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL help

Author

Topic

safderalimd
Starting Member

32 Posts

Posted - 2007-11-20 : 16:02:33

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-20 : 16:24:22

select * from
tbl1 a --Change tbl1 to your table name
inner join
tbl2 b --Change tbl2 to your second table name
on ( a.Status = 'Admit'
and
a.Admit_Sid = b.SID)
or (a.Status = 'Discharge'
and
a.DischargeID = b.SID)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-20 : 16:34:10

Your sample data did not provide ID field(s) for you to match a discharge with a admit.

Here are three steps you may try:

1) Create temp Table, such as

IF OBJECT_ID( 'tempdb..#temp) IS NULL CREATE TABLE #temp( ID INT, AdmitDt datetime, dischargeDt datetime)

2) Insert into #temp (ID, AdmitDt)
Select ID, date1 from yourtable where Status ='Admit'

3) Update #temp Set dischargeDt = date1
from yourtable where status = 'discharge' and
--join them on the key(s)

Hope this give you the idea.

safderalimd
Starting Member

32 Posts

Posted - 2007-11-21 : 11:11:30

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-21 : 11:56:11

try this. hope this will provide you with expected result ( I've not tested this)

With CTE_Patient (row_id,id,client_id ,status ,date1) AS
(
SELECT ROW_NUMBER OVER (partition by client_id,date1 order by date1) AS 'Row_No',id ,client_id ,status ,date1
FROM t1

)

SELECT ROW_NUMBER OVER(ORDER BY adm.date1,disch.date1) as 'stay_no',
adm.client_id,
adm.id as 'admit_id',
disch.id as 'discharge_id',
adm.date1 as 'admit_date',
disch.date1 as 'discharge_date'
FROM CTE_Patient adm
INNER JOIN CTE_Patient disch
ON adm.client_id = disch.client_id
AND adm.status ='admit'
AND disch.status='discharge'
AND adm.row_id=disch.row_id-1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-22 : 00:12:14

sorry slight change:-

With CTE_Patient (row_id,id,client_id ,status ,date1) AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY client_id ) AS 'Row_No',id ,client_id ,status ,date1
FROM t1
)

SELECT ROW_NUMBER() OVER(ORDER BY adm.client_id) as 'stay_no',
adm.client_id,
adm.id as 'admit_id',
disch.id as 'discharge_id',
adm.date1 as 'admit_date',
disch.date1 as 'discharge_date'
FROM CTE_Patient adm
INNER JOIN CTE_Patient disch
ON adm.client_id = disch.client_id
AND adm.status ='admit'
AND disch.status='discharge'
AND adm.row_id=disch.row_id-1

safderalimd
Starting Member

32 Posts

Posted - 2007-11-22 : 07:33:06

THANKS. THE BELOW SQL DID WORK.....

quote:
Originally posted by visakh16

sorry slight change:-

With CTE_Patient (row_id,id,client_id ,status ,date1) AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY client_id ) AS 'Row_No',id ,client_id ,status ,date1
FROM t1
)

SELECT ROW_NUMBER() OVER(ORDER BY adm.client_id) as 'stay_no',
adm.client_id,
adm.id as 'admit_id',
disch.id as 'discharge_id',
adm.date1 as 'admit_date',
disch.date1 as 'discharge_date'
FROM CTE_Patient adm
INNER JOIN CTE_Patient disch
ON adm.client_id = disch.client_id
AND adm.status ='admit'
AND disch.status='discharge'
AND adm.row_id=disch.row_id-1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources