resarting row number based on values in categories
-
I am running one query and getting following results
Select
* from (Select ROW_NUMBER() over (partition by [HOSP_CODE],[MRN] order by [HOSP_CODE],MRN,ADM_DATETIME ) as rownumber,* from
Live.tempnewbornbundling) as a
order by [HOSP_CODE],MRN,ADM_DATETIME
Rownumber key MRN hospcode adm_date sep_date Sequence
1 7099222 544607 3 22/07/2011 04:55 22/07/2011 10:44 First
2 7099223 544607 3 22/07/2011 10:45 25/07/2011 19:43 Middle
3 7099224 544607 3 25/07/2011 19:44 26/07/2011 11:29 Middle
4 7099225 544607 3 27/07/2011 12:30 27/07/2011 19:30 First
5 7099226 544607 3 27/07/2011 19:31 28/07/2011 19:31 Final
1 7099227 559282 3 03/07/2011 22:50 03/07/2011 23:51 First
2 7099228 559282 3 03/07/2011 23:52 04/07/2011 15:30 Middle
3 7099229 559282 3 04/07/2011 15:31 04/07/2011 17:59 Final
4 7099230 559282 3 05/07/2011 18:00 05/07/2011 18:05 First
5 7099231 559282 3 05/07/2011 18:06 09/07/2011 14:58 Final
Rownumber key MRN hospcode adm_date sep_date Sequence newro
1 7099222 544607 3 22/07/2011 04:55 22/07/2011 10:44 First 1
2 7099223 544607 3 22/07/2011 10:45 25/07/2011 19:43 Middle 2
3 7099224 544607 3 25/07/2011 19:44 26/07/2011 11:29 Middle 3
4 7099225 544607 3 27/07/2011 12:30 27/07/2011 19:30 First 1
5 7099226 544607 3 27/07/2011 19:31 28/07/2011 19:31 Final 2
1 7099227 559282 3 03/07/2011 22:50 03/07/2011 23:51 First 1
2 7099228 559282 3 03/07/2011 23:52 04/07/2011 15:30 Middle 2
3 7099229 559282 3 04/07/2011 15:31 04/07/2011 17:59 Final 3
4 7099230 559282 3 05/07/2011 18:00 05/07/2011 18:05 First 1
5 7099231 559282 3 05/07/2011 18:06 09/07/2011 14:58 Final 2
how Can I further make partitions and assign row numbers based on sequence values,
for example I want to restart rownumber each time there is first or after a final in same group HOSPcode, MRN
-
Without your table definitions and scripts to create the sample data, it is difficult to work out an exact solution, however looking at your query and, I guess sample before and after data, you could probably achieve this by using a CTE
Something like
;WITH OriginalQuery AS (
Select
* from (Select ROW_NUMBER() over (partition by [HOSP_CODE],[MRN] order by [HOSP_CODE],MRN,ADM_DATETIME ) as rownumber,* from
Live.tempnewbornbundling) as a
order by [HOSP_CODE],MRN,ADM_DATETIME
)
SELECT Rownumber, key, MRN ,hospcode, adm_date, sep_date ,Sequence ,
ROW_NUMBER() OVER (PARTITION BY [HOSP_CODE],[MRN], Sequence ORDER BY Rownumber ) AS newro
FROM OriginalQuery;
see the following for how to get the best out of this forum.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
-
Main problem here is that we do not have something to group on for one sequence of First, Middle, and Final.
To solve that, I introduced a derived column that searches for 'First' above or equal current row and get the key.
That is the column we will goup (partition) on.
The rest is easy.
CTE is only useful if we have multiple uses of CTE expression or recursion on that expression. We don't have that here, so no need for CTE.
This works, tested on your sample data:
-- Prepare test dataCREATE TABLE #table
( int IDENTITY(7099222,1) PRIMARY KEY,
MRN int NOT NULL,
hospcode int NOT NULL,
adm_datetime datetime NOT NULL,
Sequence varchar(10) NOT NULL
)
SET DATEFORMAT DMY
INSERT INTO #table( MRN, hospcode, adm_datetime, Sequence)
VALUES
(544607, 3, '22/07/2011 04:55', 'First'),
(544607, 3, '22/07/2011 10:45', 'Middle'),
(544607, 3, '25/07/2011 19:44', 'Middle'),
(544607, 3, '27/07/2011 12:30', 'First'),
(544607, 3, '27/07/2011 19:31', 'Final'),
(559282, 3, '03/07/2011 22:50', 'First'),
(559282, 3, '03/07/2011 23:52', 'Middle'),
(559282, 3, '04/07/2011 15:31', 'Final'),
(559282, 3, '05/07/2011 18:00', 'First'),
(559282, 3, '05/07/2011 18:06', 'Final')
-- Result
SELECT sg.*,
newro = ROW_NUMBER() OVER(PARTITION BY sg.SequenceGroup ORDER BY adm_datetime, )
FROM
( SELECT t.*, SequenceGroup = -- can be NULL if 'First' row does not exists!
( SELECT TOP 1 tt.
FROM #table tt
WHERE
( tt.adm_datetime < t.adm_datetime
OR -- "gymnastics" if there are several 'First' rows at the same time
tt.adm_datetime = t.adm_datetime AND tt.<=t.
)
AND tt.Sequence = 'First'
AND tt.MRN = t.MRN
AND tt.hospcode = t.hospcode
ORDER BY tt.adm_datetime DESC, tt. DESC
)
FROM #table t
) sg
In SQL 2012 you could probably put in use windowing function to find 'First' and avoid the "TOP 1" subquery completely, making the query utterly simple.
Hope that helped,
Vedran
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
-
Thank you for your replies, I am sending once again the correct data, I would appreciate if you can further help. I think I was unable to explain that I want to re number the group [which was formed by SAME MRN and HOSPCODE] after 'Final' OR if Record it self is First.
it means if there are two records in order 1 is final and another is middle
middle will also get 1 as it is after Final
I am doing this becasue I want to add another column 'Flag and would check the new group inside the group, if there is First , middle, final, it would be correct or if there is First and final it would be correct.
first Middle --- will be erro, only Middle will be error, only Final will be error, only first will be error, first middle will be error 'middle and final will also be error as shown below
Drop table #table
-- 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')
Select * from #table
-
Problem Definition:
I have list of records, having columns Admissions_key,MRN, hosp_code, adm_datetime, sep_datetime,Sequence and many other columns
There would be multiple records for same MRN and Hospital code. With difference in admission date and separtion date
There may be multiple, first, middle and final sequences
Now we need to further group them by
First we need to sort them Hosp,MRN, adm_date time and find the correct records from those groups.
Criteria for those further groups is that
1- First
2- 0-n middle
3- 1 Final
That was why I was re-numbering them
In 1 group there may be following sequence , where there will be Middle after Final which should be in another group
1 First ---------- 1
2 Final -----------2
3 Middle --------- 1
1 First -1
2 Final 2
3 Middle 1
4 First1
5 First 1
6 Middle2
7 Middle3
8 Final4
9 Final 1
Issue which I am having is that how I can compare a record with another record which is appearing before or after any record
Like how can I compare record 1 with record 2, record 2 with record 3, record 4 with record 5.
-
In even longer example, You should achieve these groups:
(id, Sequence, SequenceNum, GroupId)
1 First 1 12 Final 3 1
3 Middle 2 3
4 First 1 4
5 First 1 5
6 Middle 2 5
7 Middle 2 5
8 Final 3 5
9 Final 3 9
10 Middle 2 10
11 Middle 2 10
12 Final 3 10
Lets try to describe that:
If SequenceNum grows larger than row before or both equals to 2, we stay in the same group.
ELSE we are in the new group.
Now, we should turn this pseudocode to SQL, but it's very late, and I'm going to sleep now and dream about numbers 🙂
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
-
thanks for these
-
I think here issue is that how I can compare 1 record with another and do any test as I also need to compare there dates in future.
I am looking for any simple solution which I can also use to generate flags E and C by comparing dates of 1 with 2, and 2 with 3, and 3 with 4 and so on.
key MRN hosp_code adm_datetime sep_datetime Seq I_Group Order1 F_Group Order2 Flag
7099222 544607 3 22/07/2011 04:55 22/07/2011 10:44 First 1 1 1a 1 E
7099223 544607 3 22/07/2011 10:45 25/07/2011 19:43 Middle 1 2 1a 2 E
7099224 544607 3 25/07/2011 19:44 26/07/2011 11:29 Middle 1 3 1a 3 E
7099225 544607 3 27/07/2011 13:30 27/07/2011 19:30 First 1 4 1b 1 C
7099226 544607 3 27/07/2011 19:31 28/07/2011 11:30 Final 1 5 1b 2 C
7099227 559282 3 03/07/2011 22:50 03/07/2011 23:51 First 2 1 2a 1 C
7099228 559282 3 03/07/2011 23:52 04/07/2011 15:30 Middle 2 2 2a 2 C
7099229 559282 3 04/07/2011 15:31 04/07/2011 17:59 Final 2 3 2a 3 C
7099230 559282 3 05/07/2011 18:00 05/07/2011 18:05 Middle 2 4 2b 1 E
7099231 559282 3 05/07/2011 18:06 09/07/2011 14:58 Final 2 5 2b 2 E
7099232 999271 3 07/08/2011 01:00 07/08/2011 18:05 Middle 3 1 3a 1 E
7099233 999271 3 07/08/2011 18:06 09/08/2011 14:58 Final 3 2 3a 2 E
7099234 999271 3 10/08/2011 18:00 10/08/2011 18:05 First 3 3 3b 1 E
7099235 446435 3 11/08/2011 18:06 12/08/2011 12:08 First 4 1 4a 1 C
7099236 446435 3 12/08/2011 12:09 12/08/2011 14:58 Final 4 2 4a 2 C
-
Yes I think you are just talking about 1 group, if we take it as a whole than we should have some thing like this
I excluding admissions, MRN, HOSP_code, adm_date_time, and Sep_datetime for now and just putting additional columns which we need to create on our own based on these.
1. First we need to create groups based on same MRN and HOSP CODE
2. arrange them in sequence in order by adm_datetime
3. assign seq_num [ids]
4. once seq_nums are assign than further group them and give new groupId, and New SeqNum and than check dates and Flag them.
5. for flag C we need to check two things,
a) it must have 1 and 3[ first and Final] and
b) admission date/time must be within 1 minute of the separation date/time on the preceding record.
in the following example admission_date time of [3-1] must be within 1 minute of separation time of record [1-1].
(SeqNum, Group_Id,Sequence, NewSeqNum, NewGroupId, Flag)
1 1 First 1 1 C
2 1 Final 3 1 C
3 1 Middle 2 3 E
4 1 First 1 4 E
5 1 First 1 5 C
6 1 Middle 2 5 C
7 1 Middle 2 5 C
8 1 Final 3 5 C
9 1 Final 3 9 E
10 1 Middle 2 10 E
11 1 Middle 2 10 E
12 1 Final 3 10 E
1 2 First 1 1 C
2 2 Final 3 1 C
1 3 Middle 2 1 E
2 3 Final 3 1 E
on NewSeqNum, NewGroupId we can put further checks and put flag as C or E
by saying that if in any NewGroupId, if 1 and 3 exists than mark both as C other wise E to both records.
Thank you so much at least now we have a idea what should be done exactly. correct me if i am wrong but still don't have any solution yet 🙁
I am in Australia and still there is a day. will be waiting for your reply if u come up some thing in your dreams :). I am sure when you will see this, I will be in my dreams
will be waiting for your reply
I really appreciate your help in this regard
Thanks.
-
go to next page half work is done i think so now its my time is to sleep. can u please have a look and further dig it out.
-
SELECT t.[Admissions_key],t.MRN,t.hosp_code,t.adm_datetime,t.sep_datetime,t.Sequence,
ROW_NUMBER() OVER (PARTITION BY t.MRN,t.hosp_code,t1.[Admissions_key] ORDER BY t.[Admissions_key]) AS newro
FROM dbo.tempnewborn t
OUTER APPLY(SELECT TOP 1 [Admissions_key]
FROM dbo.tempnewborn
WHERE hosp_code = t.hosp_code
AND MRN = t.MRN
AND (([Admissions_key] >= t.[Admissions_key] AND Sequence ='Final') OR ([Admissions_key] > t.[Admissions_key] AND Sequence ='First'))
ORDER BY [Admissions_Key]
)t1
order by t.[Admissions_key]
see I think I have done half job now we need to rectify which are E and which are C by finding which sub groups have First and Final Both
-
I'm sorry you never sent a complete example. That is, script that prepares your test data.
It would really help you to get the answer more quickly.
Please read this best practices on asking questions: http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
So, I will do that instead of you:
-- Prepare test dataCREATE TABLE #table
( Admission_key int IDENTITY(7099222,1) PRIMARY KEY,
MRN int NOT NULL,
hospcode int NOT NULL,
adm_datetime datetime NOT NULL,
Sequence varchar(10) NOT NULL
)
SET DATEFORMAT DMY
INSERT INTO #table( MRN, hospcode, adm_datetime, Sequence)
VALUES
(544607, 3, '22/07/2011 04:55', 'First'),
(544607, 3, '22/07/2011 10:45', 'Middle'),
(544607, 3, '25/07/2011 19:44', 'Middle'),
(544607, 3, '27/07/2011 12:30', 'First'),
(544607, 3, '27/07/2011 19:31', 'Final'),
(559282, 3, '03/07/2011 22:50', 'First'),
(559282, 3, '03/07/2011 23:52', 'Middle'),
(559282, 3, '04/07/2011 15:31', 'Final'),
(559282, 3, '05/07/2011 18:00', 'Middle'),
(559282, 3, '05/07/2011 18:06', 'Final'),
(559282, 3, '05/07/2011 18:07', 'Final')
select * from #table
Now, let's solve the puzzle.
If Admission_key is indentity, then it is not guaranteed that the record with higher Admission_key has greater or equal Adm_datetime.
There is also no guarantee there will be no gaps between admission_key values.
So, don't rely on that.
Let's try this algorithm:
We'll match two consequent rows together: current row and row above (previous row within a group).
Current row starts a group if and only if the current row is "First" or row above is "Last".
All rows below that row belong to that group until we reach a row that starts another group.
Let's implement that:
;WITH cur_prev AS -- cte is usefull here because we will use that expression more than once( -- Match current row with row above
SELECT cur.*, prev_Sequence = prev.Sequence
--Group_id = CASE WHEN cur.Sequence='First' OR prev.Sequence='Final' THEN cur.Admission_key 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.hospcode = cur.hospcode -- within the same group
and -- only rows above
( tt.adm_datetime < cur.adm_datetime
OR tt.adm_datetime = cur.adm_datetime AND tt.Admission_key < cur.Admission_key
)
ORDER BY tt.adm_datetime DESC, tt.Admission_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.Admission_key
FROM cur_prev cc
WHERE cc.MRN = c.MRN AND cc.hospcode = c.hospcode -- 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.Admission_key <= c.Admission_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.Admission_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
Armed with group id, It is not very hard to find which group is correct and which is not.
We'll group by that already calculated group id and count does it have "First" and "Last" rows. If yes, the group is correct, if no, group is not correct.
--=== Final resultSELECT 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
GROUP BY tg.GroupID
) g on t.GroupID = g.GroupID
And the result is here:
Admission_key MRN hospcode adm_datetime Sequence prev_Sequence GroupID IsGroupCorrect------------- ----------- ----------- ----------------------- ---------- ------------- ----------- --------------
7099222 544607 3 2011-07-22 04:55:00.000 First NULL 7099222 E
7099223 544607 3 2011-07-22 10:45:00.000 Middle First 7099222 E
7099224 544607 3 2011-07-25 19:44:00.000 Middle Middle 7099222 E
7099225 544607 3 2011-07-27 12:30:00.000 First Middle 7099225 C
7099226 544607 3 2011-07-27 19:31:00.000 Final First 7099225 C
7099227 559282 3 2011-07-03 22:50:00.000 First NULL 7099227 C
7099228 559282 3 2011-07-03 23:52:00.000 Middle First 7099227 C
7099229 559282 3 2011-07-04 15:31:00.000 Final Middle 7099227 C
7099230 559282 3 2011-07-05 18:00:00.000 Middle Final 7099230 E
7099231 559282 3 2011-07-05 18:06:00.000 Final Middle 7099230 E
7099232 559282 3 2011-07-05 18:07:00.000 Final Final 7099232 E
This is a complete and tested solution, so I hope that helps 🙂
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
-
First, I am really very sorry for inconvenience for not properly explaining problem one at a time and not providing proper data.
Thanks a lot, It is really a great help for me from your side. it is tested solution but not complete yet, I am using forums first time so don't know exaclty how to present it well an also english is not my language so I have a great problem in explaining my problems. once again sorry for that.
I beleive what ever you have provided is fantastic approach I really appreciate your help.
In my posts Posted 3/16/2012 2:01:18 AM and Posted 3/16/2012 5:53:20 AM
I have given test data and problem but it was not in proper format so i think u missed that.
so providing you again.
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')
Select * from #table
Now if you notice, you will find two date time columns in my given data
Adm_datetime and seperation date time.
5. for flag C we need to check two things,
a)There must be First and Final in the group, which is there and working fine now
b) admission date/time must be within 1 minute of the separation date/time on the preceding record.
Can you please tell me how can I access individual records and compare the
admission date time of one record with the separation _time of previous record
Once These records are identified I would be creating a new record in same table and would take few fields from First, Few Fields from Middle [there may be many middle records as well] and Few Fields from Final.
so as per my understanding I would be needing some sort of id in each individual groups which are correct.
Thanks once again for all your help. will be waiting for your reply. I hope I have given you data in correct format this time and explained my problem correctly and completely.
-
I just read my post, want to further clarify that from those correct records I would create a single new record and in that single new record I would be inserting fields from First, Middle and Final. depends on various other conditions.
I hope that now you would have no difficulty in understanding my problem.
will be waiting for your reply thanks
on this forum do i need to mark some where that my question is been answered correctly ? what is the point systems on this forum?
-
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_date = prev.sep_datetime
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
GROUP BY tg.GroupID
) g on t.GroupID = g.GroupID
Can you please rectify me, if I am wrong, where should i check the difference of prev_sepdate with adm_date time ? it should be within 1 minute
where datediff(MINUTE,tg.pre_sep_date, tg.adm_datetime) <= 1 is this the correct syntax ?
I hope now I am using right format so you don't need to re do and recreate test data
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply