March 19, 2012 at 12:36 am
use this code please
Drop table #table
Drop table #table_with_groupid
-- Prepare test data
CREATE TABLE #table
( [Admissions_key] bigint NOT NULL PRIMARY KEY,
MRN nvarchar(10) NOT NULL,
hosp_code nvarchar(10) NOT NULL,
adm_datetime datetime NOT NULL,
sep_datetime datetime NOT NULL,
Sequence nvarchar(10) NOT NULL
)
SET DATEFORMAT DMY
INSERT INTO #table( Admissions_key,MRN, hosp_code, adm_datetime, sep_datetime,Sequence)
VALUES
(7099222,'0000544607','0003','22/07/2011 04:55','22/07/2011 10:44','First'),
(7099223,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle'),
(7099224,'0000544607','0003','25/07/2011 19:44','26/07/2011 11:29','Middle'),
(7099225,'0000544607','0003','27/07/2011 13:30','27/07/2011 19:30','First'),
(7099226,'0000544607','0003','27/07/2011 19:31','28/07/2011 11:30','Final'),
(7099227,'0000559282','0003','03/07/2011 22:50','03/07/2011 23:51','First'),
(7099228,'0000559282','0003','03/07/2011 23:52','04/07/2011 15:30','Middle'),
(7099229,'0000559282','0003','04/07/2011 15:31','04/07/2011 17:59','Final'),
(7099230,'0000559282','0003','05/07/2011 18:00','05/07/2011 18:05','Middle'),
(7099231,'0000559282','0003','05/07/2011 18:06','09/07/2011 14:58','Final'),
(7099232,'0000999271','0003','07/08/2011 01:00','07/08/2011 18:05','Middle'),
(7099233,'0000999271','0003','07/08/2011 18:06','09/08/2011 14:58','Final'),
(7099234,'0000999271','0003','10/08/2011 18:00','10/08/2011 18:05','First'),
(7099235,'0000446435','0003','11/08/2011 18:06','12/08/2011 12:08','First'),
(7099236,'0000446435','0003','12/08/2011 12:09','12/08/2011 14:58','Final')
;WITH cur_prev AS
( -- Match current row with row above
SELECT cur.*, prev_Sequence = prev.Sequence, pre_sep_datetime = CASE WHEN cur.Sequence='First' THEN NULL ELSE prev.sep_datetime END
FROM #table cur
OUTER APPLY -- we use OUTER APPLY instead of CROSS APPLY to get a row even if previous row does not exist
( -- Find previous row to current row.
SELECT TOP 1 tt.*
FROM #table tt
WHERE tt.MRN = cur.MRN AND tt.hosp_code = cur.hosp_code -- within the same group
and -- only rows above
( tt.adm_datetime < cur.adm_datetime
OR tt.adm_datetime = cur.adm_datetime AND tt.Admissions_key < cur.Admissions_key
)
ORDER BY tt.adm_datetime DESC, tt.Admissions_key DESC -- sort should be unique, so we added a PK column
) prev
)
SELECT c.*,
GroupID =
( -- Find the first row above that starts a group
SELECT TOP 1 cc.Admissions_key
FROM cur_prev cc
WHERE cc.MRN = c.MRN AND cc.hosp_code = c.hosp_code -- within the same group
AND -- only rows above, but this time INCLUDING current row!
( cc.adm_datetime < c.adm_datetime
OR cc.adm_datetime = c.adm_datetime AND cc.Admissions_key <= c.Admissions_key
)
ORDER BY CASE WHEN cc.Sequence='First' OR cc.prev_Sequence='Final' THEN 0 ELSE 1 END, -- first try to find a row that starts a group
cc.adm_datetime DESC, cc.Admissions_key DESC -- sort should be unique, so we added a PK column
)
INTO #table_with_groupid
FROM cur_prev c
SELECT * FROM #table_with_groupid
SELECT t.*, g.IsGroupCorrect
FROM #table_with_groupid t
LEFT JOIN
( -- Find which group is correct and which is not
SELECT tg.GroupID,
IsGroupCorrect = CASE -- correct is group that have 'First' and 'Final' in it.
WHEN MAX(CASE WHEN tg.Sequence='First' THEN 1 ELSE 0 END)=1 AND MAX(CASE WHEN tg.Sequence='Final' THEN 1 ELSE 0 END)=1 THEN 'C'
ELSE 'E'
END
FROM #table_with_groupid tg
--where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1
GROUP BY tg.GroupID
) g on t.GroupID = g.GroupID
March 19, 2012 at 1:32 am
SELECT t.*, g.IsGroupCorrect
FROM #table_with_groupid t
LEFT JOIN
( -- Find which group is correct and which is not
SELECT tg.GroupID,
IsGroupCorrect = CASE -- correct is group that have 'First' and 'Final' in it.
WHEN MAX(CASE WHEN tg.Sequence='First' THEN 1 ELSE 0 END)=1 AND MAX(CASE WHEN tg.Sequence='Final' THEN 1 ELSE 0 END)=1
and MAX(Case When tg.pre_sep_datetime IS NOT NUll then 1 else 0 end) = 1
and MAX(Case When datediff(MINUTE,tg.pre_sep_datetime,tg.adm_datetime)< =1 then 1 else 0 end) = 1
THEN 'C'
ELSE 'E'
END
FROM #table_with_groupid tg
--where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1
GROUP BY tg.GroupID
) g on t.GroupID = g.GroupID
This is working when there is first and final, but not when there is first, many middle and final
I think, I am very near, please rectify, do i need to use cursor or CTE again?
March 19, 2012 at 1:33 am
Drop table #table
Drop table #table_with_groupid
-- Prepare test data
CREATE TABLE #table
( [Admissions_key] bigint NOT NULL PRIMARY KEY,
MRN nvarchar(10) NOT NULL,
hosp_code nvarchar(10) NOT NULL,
adm_datetime datetime NOT NULL,
sep_datetime datetime NOT NULL,
Sequence nvarchar(10) NOT NULL
)
SET DATEFORMAT DMY
INSERT INTO #table( Admissions_key,MRN, hosp_code, adm_datetime, sep_datetime,Sequence)
VALUES
(7099222,'0000544607','0003','22/07/2011 04:55','22/07/2011 10:44','First'),
(7099223,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle'),
(7099224,'0000544607','0003','25/07/2011 19:44','26/07/2011 11:29','Middle'),
(7099225,'0000544607','0003','27/07/2011 13:30','27/07/2011 19:30','First'),
(7099226,'0000544607','0003','27/07/2011 19:36','28/07/2011 11:30','Final'),
(7099227,'0000559282','0003','03/07/2011 22:50','03/07/2011 23:51','First'),
(7099228,'0000559282','0003','03/07/2011 23:52','04/07/2011 15:30','Middle'),
(7099229,'0000559282','0003','04/07/2011 15:33','04/07/2011 17:59','Final'),
(7099230,'0000559282','0003','05/07/2011 18:00','05/07/2011 18:05','Middle'),
(7099231,'0000559282','0003','05/07/2011 18:06','09/07/2011 14:58','Final'),
(7099232,'0000999271','0003','07/08/2011 01:00','07/08/2011 18:05','Middle'),
(7099233,'0000999271','0003','07/08/2011 18:06','09/08/2011 14:58','Final'),
(7099234,'0000999271','0003','10/08/2011 18:00','10/08/2011 18:05','First'),
(7099235,'0000446435','0003','11/08/2011 18:06','12/08/2011 12:08','First'),
(7099236,'0000446435','0003','12/08/2011 12:09','12/08/2011 14:58','Final')
;WITH cur_prev AS
( -- Match current row with row above
SELECT cur.*, prev_Sequence = prev.Sequence, pre_sep_datetime = CASE WHEN cur.Sequence='First' THEN NULL ELSE prev.sep_datetime END
FROM #table cur
OUTER APPLY -- we use OUTER APPLY instead of CROSS APPLY to get a row even if previous row does not exist
( -- Find previous row to current row.
SELECT TOP 1 tt.*
FROM #table tt
WHERE tt.MRN = cur.MRN AND tt.hosp_code = cur.hosp_code -- within the same group
and -- only rows above
( tt.adm_datetime < cur.adm_datetime
OR tt.adm_datetime = cur.adm_datetime AND tt.Admissions_key < cur.Admissions_key
)
ORDER BY tt.adm_datetime DESC, tt.Admissions_key DESC -- sort should be unique, so we added a PK column
) prev
)
SELECT c.*,
GroupID =
( -- Find the first row above that starts a group
SELECT TOP 1 cc.Admissions_key
FROM cur_prev cc
WHERE cc.MRN = c.MRN AND cc.hosp_code = c.hosp_code -- within the same group
AND -- only rows above, but this time INCLUDING current row!
( cc.adm_datetime < c.adm_datetime
OR cc.adm_datetime = c.adm_datetime AND cc.Admissions_key <= c.Admissions_key
)
ORDER BY CASE WHEN cc.Sequence='First' OR cc.prev_Sequence='Final' THEN 0 ELSE 1 END, -- first try to find a row that starts a group
cc.adm_datetime DESC, cc.Admissions_key DESC -- sort should be unique, so we added a PK column
)
INTO #table_with_groupid
FROM cur_prev c
SELECT * FROM #table_with_groupid
SELECT t.*, g.IsGroupCorrect
FROM #table_with_groupid t
LEFT JOIN
( -- Find which group is correct and which is not
SELECT tg.GroupID,
IsGroupCorrect = CASE -- correct is group that have 'First' and 'Final' in it.
WHEN MAX(CASE WHEN tg.Sequence='First' THEN 1 ELSE 0 END)=1 AND MAX(CASE WHEN tg.Sequence='Final' THEN 1 ELSE 0 END)=1
and MAX(Case When tg.pre_sep_datetime IS NOT NUll then 1 else 0 end) = 1
and MAX(Case When datediff(MINUTE,tg.pre_sep_datetime,tg.adm_datetime)< =1 then 1 else 0 end) = 1
THEN 'C'
ELSE 'E'
END
FROM #table_with_groupid tg
--where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1
GROUP BY tg.GroupID
) g on t.GroupID = g.GroupID
March 19, 2012 at 2:30 am
Within the CTE, where prev_Sequence is defined, you can access any column from previous row. E.g.
prev_Adm_datetime = prev.Adm_datetime
Try to learn, not just use the solution. For example, execute inner queries (subqueries) with "*" to get all columns and try to understand how it works. You will benefit more in your knowledge.
March 19, 2012 at 3:44 am
Thanks for your quick reply; I have done exactly what you are saying that I have added columns, also trying to learn as much as possible.
I really appreciate your help.
Thanks
Umar.
March 19, 2012 at 4:41 am
Umar, you are welcome!
Vedran
March 19, 2012 at 6:10 pm
Solved the probelm, thank you so much for the guidance you have provided I really learned alot, never used CTE and Outer Apply before
once again thanks
Drop table #table
Drop table #table_with_groupid
-- Prepare test data
CREATE TABLE #table
( [Admissions_key] bigint NOT NULL PRIMARY KEY,
MRN nvarchar(10) NOT NULL,
hosp_code nvarchar(10) NOT NULL,
adm_datetime datetime NOT NULL,
sep_datetime datetime NOT NULL,
Sequence nvarchar(10) NOT NULL
)
SET DATEFORMAT DMY
INSERT INTO #table( Admissions_key,MRN, hosp_code, adm_datetime, sep_datetime,Sequence)
VALUES
(7099222,'0000544607','0003','22/07/2011 04:55','22/07/2011 10:44','First'),
(7099223,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle'),
(7099224,'0000544607','0003','25/07/2011 19:44','26/07/2011 11:29','Middle'),
(7099225,'0000544607','0003','27/07/2011 13:30','27/07/2011 19:30','First'),
(7099226,'0000544607','0003','27/07/2011 19:31','28/07/2011 11:30','Final'),
(7099227,'0000559282','0003','03/07/2011 22:50','03/07/2011 23:51','First'),
(7099228,'0000559282','0003','03/07/2011 23:53','04/07/2011 15:30','Middle'),
(7099229,'0000559282','0003','04/07/2011 15:31','04/07/2011 17:59','Final'),
(7099230,'0000559282','0003','05/07/2011 18:00','05/07/2011 18:05','Middle'),
(7099231,'0000559282','0003','05/07/2011 18:06','09/07/2011 14:58','Final'),
(7099232,'0000999271','0003','07/08/2011 01:00','07/08/2011 18:05','Middle'),
(7099233,'0000999271','0003','07/08/2011 18:06','09/08/2011 14:58','Final'),
(7099234,'0000999271','0003','10/08/2011 18:00','10/08/2011 18:05','First'),
(7099235,'0000446435','0003','11/08/2011 18:06','12/08/2011 12:08','First'),
(7099236,'0000446435','0003','12/08/2011 12:13','12/08/2011 14:58','Final')
;WITH cur_prev AS
( -- Match current row with row above
SELECT cur.*, prev_Sequence = prev.Sequence,
CASE WHEN (DATEDIFF(minute,prev.sep_datetime,cur.adm_datetime) =1 OR cur.Sequence = 'First') THEN 'Y' ELSE 'N' END AS DateMark
FROM #table cur
OUTER APPLY -- we use OUTER APPLY instead of CROSS APPLY to get a row even if previous row does not exist
( -- Find previous row to current row.
SELECT TOP 1 tt.*
FROM #table tt
WHERE tt.MRN = cur.MRN AND tt.hosp_code = cur.hosp_code -- within the same group
and -- only rows above
( tt.adm_datetime < cur.adm_datetime
OR tt.adm_datetime = cur.adm_datetime AND tt.Admissions_key < cur.Admissions_key
)
ORDER BY tt.adm_datetime DESC, tt.Admissions_key DESC -- sort should be unique, so we added a PK column
) prev
)
SELECT c.*,
GroupID =
( -- Find the first row above that starts a group
SELECT TOP 1 cc.Admissions_key
FROM cur_prev cc
WHERE cc.MRN = c.MRN AND cc.hosp_code = c.hosp_code -- within the same group
AND -- only rows above, but this time INCLUDING current row!
( cc.adm_datetime < c.adm_datetime
OR cc.adm_datetime = c.adm_datetime AND cc.Admissions_key <= c.Admissions_key
)
ORDER BY CASE WHEN cc.Sequence='First' OR cc.prev_Sequence='Final' THEN 0 ELSE 1 END, -- first try to find a row that starts a group
cc.adm_datetime DESC, cc.Admissions_key DESC -- sort should be unique, so we added a PK column
)
INTO #table_with_groupid
FROM cur_prev c
SELECT * FROM #table_with_groupid
Select * from
(SELECT t.*, g.IsGroupCorrect
FROM #table_with_groupid t
LEFT JOIN
( -- Find which group is correct and which is not
SELECT tg.GroupID,
IsGroupCorrect = CASE -- correct is group that have 'First' and 'Final' in it.
WHEN MAX(CASE WHEN tg.Sequence='First' THEN 1 ELSE 0 END)=1 AND MAX(CASE WHEN tg.Sequence='Final' THEN 1 ELSE 0 END)=1
AND Min( case when tg.datemark ='Y' Then 1 else 0 end) = 1
THEN 'C' ELSE 'E' END
FROM #table_with_groupid tg
--where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date) <= 1
GROUP BY tg.GroupID
) g on t.GroupID = g.GroupID) as a
March 19, 2012 at 9:59 pm
umar.memon (3/19/2012)
Solved the probelm, thank you so much for the guidance you have provided I really learned alot, never used CTE and Outer Apply beforeonce again thanks
Actually, you may have created a problem that you're not yet aware of. It took a total of 6 table scans (a scan is ok here. Having 6 isn't). Some of the scans have only 15 rows like the original data. Some of them have 24, 33, 39, and as high as 45 rows in the scan. It's not exponential growth but everything will take 2 to 3 times longer in those scans than you probably bargained for not to mention 6 times more I/O than a single scan. I'd actually be a bit surprised if a WHILE loop couldn't do as well for performance which isn't such a bad option at this point.
So, let me ask... how many rows do you have to do this for, how often do you have to do it, and what is the performance of the current code for Duration, CPU, Reads, Writes, and Rowcounts? I ask because I'd like to setup a test to show you not only the performance that you can get out of a loop on this one, but also the performance you can get out of another method. I need the information above so I can setup the test correctly and do a little result checking prior to posting a solution.
--Jeff Moden
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply