March 30, 2009 at 1:54 pm
I have a query that returns multiple line of data that I would like to combine into on line. To do this I need to get the earliest 'BegSta' and latest 'EndSta' information for each patient. Below is an example of the data for 2 patients and further down is the desired result.
I have no clue if this can be done. Any feedback or direction would be appreciated.
There are 3 lines of data for each patient. I need the first 8 fields of the data row that has the earlies 'BegSta' information and the last 2 fields from the data row that has the latest 'EndSta' data. I have highlighted this below. The identifiers for each patient would be the 'Account_Number' and the 'EndDate'
'Account_number','patinetName','AcLvl','CaseMix','EndDate','Rm/bed','Shift','BegSta','EndSta','Start_DTime','End_DTime'
'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Night','Stay','Stay','4/2/2008 22:45','NULL'
'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Day ','Stay','Stay','4/3/2008 6:45','NULL'
'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Evening','Stay','Stay','4/3/2008 14:45','NULL'
'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Night','Stay','Stay','4/2/2008 22:45','NULL'
'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Day ','Stay','Stay','4/3/2008 6:45','NULL'
'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Evening','Stay','Discharge','4/3/2008 14:45','4/3/2008 15:30'
What I would like to end up with is:
'Account_number','patinetName','AcLvl','CaseMix','EndDate','Rm/bed','Shift','BegSta','EndSta','Start_DTime','End_DTime'
'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Night','Stay','Stay','4/3/2008 14:45','NULL'
'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Night','Stay','Discharge','4/3/2008 14:45','4/3/2008 15:30'
March 30, 2009 at 2:11 pm
Can do. It will take a few minutes to set up. In the meanwhile, will you please have a look at this link? I will be sending you an example of how we would like sample data to test, but the reasons WHY are here. Doing this will get you faster answers and many friends among the volunteers here.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2009 at 2:41 pm
Completely confirm Bob. It took about fife minutes to investigate your table structure and transform your posted data to a usable format...
Here my solution:
DECLARE @mytab TABLE
(
acc_no VARCHAR(100),
PatientName VARCHAR(100),
AcLvl VARCHAR(100),
CaseMix VARCHAR(10),
EndDate DATETIME,
RmBed VARCHAR(50),
Shift VARCHAR(20),
BegSta VARCHAR(10),
EndSta VARCHAR(10),
Start_DTime DATETIME,
End_DTime DATETIME
)
INSERT INTO @mytab
SELECT 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Night','Stay','Stay','4/2/2008 22:45',NULL
UNION SELECT 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Day ','Stay','Stay','4/3/2008 6:45',NULL
UNION SELECT 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Evening','Stay','Stay','4/3/2008 14:45',NULL
UNION SELECT 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Night','Stay','Stay','4/2/2008 22:45',NULL
UNION SELECT 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Day ','Stay','Stay','4/3/2008 6:45',NULL
UNION SELECT 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Evening','Stay','Discharge','4/3/2008 14:45','4/3/2008 15:30'
;WITH first_occurence (acc_no, PatientName, dt) AS
(
SELECT acc_no, PatientName, MIN(Start_DTime)
FROM @mytab
GROUP BY acc_no, PatientName
)
, last_occurence (acc_no, PatientName, dt) AS
(
SELECT acc_no, PatientName, MAX(Start_DTime)
FROM @mytab
GROUP BY acc_no, PatientName
)
SELECT m1.acc_no, m1.PatientName, m1.AcLvl, m1.CaseMix, m1.EndDate, m1.RmBed, m1.Shift, m1.BegSta, m1.EndSta, m2.Start_DTime, m2.End_DTime
FROM @mytab m1
JOIN first_occurence fo ON m1.acc_no = fo.acc_no AND m1.PatientName = fo.PatientName AND m1.Start_DTime = fo.dt
JOIN last_occurence lo ON m1.acc_no = lo.acc_no AND m1.PatientName = lo.PatientName
JOIN @mytab m2 ON lo.acc_no = m2.acc_no AND lo.PatientName = m2.PatientName AND lo.dt = m2.Start_DTime
*sitting tauten for Bobs solution* 😉
Greets
Flo
March 30, 2009 at 2:54 pm
Here you go. I did not take the time to do the date conversion to the format you had in your output sample, but I'm sure you can handle the CONVERT function. I sort of had to read between the lines about what you wanted, because you didn't mention patient name, but that is the only thing that distinguishes between rows for Fred and Barney in the data.
If you will notice, I went to the trouble of creating a sample table variable (@test) from your data, so that I could be sure the code was performing as expected. The article I sent you explains that this makes things much easier on us AND gets you tested code quicker. Please take a couple of minutes to set it up like this next time.
Now, as to the solution. I first used a CTE to get a DISTINCT list of the account numbers and names we want to feed into the primary query. In the primary query, we use CROSS APPLY against subqueries, instead of functions, to get your results. The subqueries do nothing more than sort the set of rows for each account_number/patientname, and take the first row. Subquery X takes the first row, and subquery Y sorts in descending order to get the last row.
Think of it as "joining" to different tables that contain first rows and last rows.
I haven't tested two cross-applies that sort in opposite directions against a large number of rows, so please let me know how this performs for you... and of course, please let me know if you have any questions.
Bob
Declare @test-2 table
(Account_number varchar(20)
,patientName varchar(20)
,AcLv varchar(20)
,CaseMix int
,EndDate datetime
,RmBed varchar(20)
,Shift varchar(20)
,BegSta varchar(20)
,EndSta varchar(20)
,Start_DTime datetime
,End_DTime datetime
)
insert into @test-2
select 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Night','Stay','Stay','4/2/2008 22:45',NULL union all
select 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Day ','Stay','Stay','4/3/2008 6:45',NULL union all
select 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Evening','Stay','Stay','4/3/2008 14:45',NULL union all
select 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Night','Stay','Stay','4/2/2008 22:45',NULL union all
select 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Day ','Stay','Stay','4/3/2008 6:45',NULL union all
select 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Evening','Stay','Discharge','4/3/2008 14:45','4/3/2008 15:30'
select * from @test-2
;with cte1 as (select distinct account_number,patientName from @test-2 )
select c.Account_Number,c.PatientName,x.AcLv,x.CaseMix,x.EndDate,x.RmBed,x.Shift,x.BegSta,x.EndSta,y.start_Dtime, y.end_Dtime
from cte1 c
cross apply (select top 1 AcLv,CaseMix,EndDate,RmBed,Shift,BegSta,Endsta
from @test-2 t1
where t1.account_number = c.account_number and t1.patientName = c.PatientName
order by end_Dtime,start_Dtime
) x
cross apply (select top 1 Start_Dtime, end_Dtime
from @test-2 t1
where t1.account_number = c.account_number and t1.patientName = c.PatientName
order by end_Dtime desc,start_Dtime desc
) y
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2009 at 2:56 pm
Dang, you're fast, FLO !!!
When I get a chance, I'll crank out a large test set and we can race our solutions, or perhaps they could both be tested against the production data.
I just tested them against each other in my sandbox against Fred and Barney. Cross Apply seems to be a little faster in the sprints at 2 ms against your query's 6-7 ms. High volume testing will have to wait for a while though.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 30, 2009 at 3:13 pm
Your solution is just the thing I was tauten for. Maybe my solution was faster written but your is faster executed :-). I know I can learn from you so I wanted to compare the solutions.
Very good idea to use a CROSS APPLY! I saw just once in combination with CTEs and I forgot for this case.
In my production system I'm still bound on 2k syntax...
Best regards
Flo
March 30, 2009 at 3:17 pm
Bob and Florian,
Thanks for the super quick response I really appreciate it. I had read the information at the link you provided Bob but obviously didn't comprehend it. On my second read I had that "ah ha" moment.
I will have an opportunity to try both scenarios tomorrow and will report back.
Thanks!
March 30, 2009 at 3:26 pm
Great 🙂 We'll look forward to hearing from you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 12:43 pm
Bob and Flo,
I have tried to run your solutions but I am getting errors as listed below. I am wondering if it is because we run SQL2000, which I did not detail in my post.:sick:
Here is the error message when I run Flo's:
Server: Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'WITH'.
Server: Msg 170, Level 15, State 1, Line 50
Line 50: Incorrect syntax near ','.
Here is the error when I run Bob's:
Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'with'.
Server: Msg 170, Level 15, State 1, Line 37
Line 37: Incorrect syntax near 'apply'.
Server: Msg 156, Level 15, State 1, Line 43
Incorrect syntax near the keyword 'order'.
Server: Msg 156, Level 15, State 1, Line 54
Incorrect syntax near the keyword 'order'.
March 31, 2009 at 12:54 pm
Yes, that is most certainly the problem. Hang on a minute and I'll give you an SQL2000 solution.
For the future, if you have questions post them in the SQL 7,2000 forum. That way we will know what functionality is and is not available. You can't use CTEs and CROSS APPLY until you get to SQL2005.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 2:04 pm
So there seems to be no beautiful solution on SQL Server 2000 - as I think...
DECLARE @mytab TABLE
(
acc_no VARCHAR(100),
PatientName VARCHAR(100),
AcLvl VARCHAR(100),
CaseMix VARCHAR(10),
EndDate DATETIME,
RmBed VARCHAR(50),
Shift VARCHAR(20),
BegSta VARCHAR(10),
EndSta VARCHAR(10),
Start_DTime DATETIME,
End_DTime DATETIME
)
INSERT INTO @mytab
SELECT 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Night','Stay','Stay','4/2/2008 22:45',NULL
UNION SELECT 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Day ','Stay','Stay','4/3/2008 6:45',NULL
UNION SELECT 'BRK2000512XXX','Fred Flintsone','Medium','3','4/3/2008','921W-A','Evening','Stay','Stay','4/3/2008 14:45',NULL
UNION SELECT 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Night','Stay','Stay','4/2/2008 22:45',NULL
UNION SELECT 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Day ','Stay','Stay','4/3/2008 6:45',NULL
UNION SELECT 'BRK2000512XXX','Barney Rubble','Medium','3','4/3/2008','705-A','Evening','Stay','Discharge','4/3/2008 14:45','4/3/2008 15:30'
SELECT m1.acc_no, m1.PatientName, m1.AcLvl, m1.CaseMix, m1.EndDate, m1.RmBed, m1.Shift, m1.BegSta, m1.EndSta, m2.Start_DTime, m2.End_DTime
FROM @mytab m1
JOIN (SELECT acc_no, PatientName, MIN(Start_DTime) Start_DTime
FROM @mytab
GROUP BY acc_no, PatientName) first_row
ON m1.acc_no = first_row.acc_no
AND m1.PatientName = first_row.PatientName
AND m1.Start_DTime = first_row.Start_DTime
JOIN (SELECT acc_no, PatientName, MAX(Start_DTime) Start_DTime
FROM @mytab
GROUP BY acc_no, PatientName) last_row
ON m1.acc_no = last_row.acc_no
AND m1.PatientName = last_row.PatientName
JOIN @mytab m2 ON m1.acc_no = m2.acc_no
AND m1.PatientName = m2.PatientName
AND m2.Start_DTime = last_row.Start_DTime
*again... sitting tauten for Bobs solution* 🙂
Greets
Flo
March 31, 2009 at 2:50 pm
Flo, I think you have a winner. I don't believe I can improve on that in a single query, since I had begun a similar approach before being distracted by a minor crisis at work. However, the execution plan indicates that it is going to do three separate scans of the the input table. We'll just have to see how it runs against production volumes.
If unsatisfactory, it might be possible to improve by breaking it into steps, but I don't want to go there if a single query will get the job done.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 3:35 pm
Well...this looks like the solution. I will try to run this against my test database tomorrow. I will take some time for verification of the data. I have been trying to figure this out on my own for a long time. I can't believe how strait foward the solution was/is.
I will let you know the result of the verification
Thanks for your quick responses and nudges. Next time I will scroll down the page to make sure I post in the correct place. However, we will be upgrading to 2005 in the near future so seeing the other options was helpful.
March 31, 2009 at 3:39 pm
You're welcome, gnethery.
You will find much to like in SQL 2005, and more to come in 2008. Good luck with that.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 31, 2009 at 11:15 pm
Service pack 1. I removed one unnecessary aggregated sub select to improve the performance:
SELECT m1.acc_no, m1.PatientName, m1.AcLvl, m1.CaseMix, m1.EndDate, m1.RmBed, m1.Shift, m1.BegSta, m1.EndSta, m2.Start_DTime, m2.End_DTime
FROM @mytab m1
JOIN (SELECT acc_no, PatientName, MIN(Start_DTime) First_DTime, MAX(Start_DTime) Last_DTime
FROM @mytab
GROUP BY acc_no, PatientName) boundary
ON m1.acc_no = boundary.acc_no
AND m1.PatientName = boundary.PatientName
AND m1.Start_DTime = boundary.First_DTime
JOIN @mytab m2 ON m1.acc_no = m2.acc_no
AND m1.PatientName = m2.PatientName
AND m2.Start_DTime = boundary.Last_DTime
Greets
Flo
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply