Ask - new row_number based on values in same category

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Ask

Ask questions, discuss or help others by answering


	Ask a question


	Subscribe..

new row_number based on values in same category

Mar 14 2012 12:00AM by Umar001

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

how Can I further make partitions and assign new 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 as mentioned below:

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

Thanks, your help will be highly appreciated

Submitted under: Microsoft SQL Server · TSQL · · ·

Previous: resarting row number
Next: SQL Server Intersection

Umar001

282 · 0% · 150

14 Replies

using cursor you can do by this way

DECLARE @Key VARCHAR(10)=''
DECLARE @SrNo INT= 0
DECLARE @NewSrNo INT= 0
DECLARE @Mrn VARCHAR(10) =''
DECLARE @PrevhospCode INT = 0
DECLARE @hospCode INT = 0
DECLARE @PrevMrn VARCHAR(10)= ''
DECLARE @sequence VARCHAR(10)
DECLARE @admdatetime VARCHAR(25)
DECLARE @sepdatetime VARCHAR(25)

CREATE TABLE #tmp(srno int, [KEY] varchar(10),mrn varchar(10),hospcode int,admdatetime varchar(25),sepdatetime varchar(25),sequence varchar(10),newsrno int)

DECLARE @getCursor CURSOR
SET @getCursor = CURSOR FOR
SELECT [key],mrn,hospcode,admdatetime,sepdatetime,Sequence FROM Live.tempnewbornbundling order by HOSPCODE,MRN,ADMDATETIME
OPEN @getCursor
FETCH NEXT FROM @getCursor INTO @key, @Mrn, @hospcode,@admdatetime,@sepdatetime, @sequence
WHILE @@FETCH_STATUS = 0 
    BEGIN
        IF NOT ( ( @hospCode = @PrevhospCode )
                 AND ( @Mrn = @PrevMrn )
               ) 
            BEGIN
                SET @SrNo = 1
                SET @PrevhospCode = @hospCode
                SET @PrevMrn = @Mrn
                SET @NewSrNo = 1
            END
        ELSE 
            BEGIN
                IF @sequence = 'First' 
                    SET @NewSrNo = 1
                ELSE 
                    SET @NewSrNo = @NewSrNo + 1

                SET @SrNo = @SrNo + 1   
            END

    	INSERT into #tmp (srno,[key],mrn,hospcode,admdatetime,sepdatetime,Sequence,Newsrno)VALUES(@srno,@key,@mrn,@hospCode,@admdatetime,@sepdatetime,@sequence,@NewSrNo)

        IF @sequence = 'Final' 
            SET @NewSrNo = 0

        FETCH NEXT FROM @getCursor INTO @key, @Mrn, @hospcode,@admdatetime,@sepdatetime, @sequence
    END
CLOSE @getCursor
DEALLOCATE @getCursor
SELECT * FROM #tmp
DROP TABLE #tmp

commented on Mar 15 2012 4:34AM

Mitesh Modi

18 · 10% · 3078

Another Reply using CTE

;WITH cte AS 
 ( 
 SELECT   ROW_NUMBER() OVER ( PARTITION BY HOSPCODE,MRN
 ORDER BY HOSPCODE, MRN, ADMDATETIME ) AS rownumber ,
 * FROM     Live.tempnewbornbundling
)
SELECT  cte.* , ROW_NUMBER() OVER 
( PARTITION BY HOSPCODE,MRN, test.MaxRNo
 ORDER BY HOSPCODE, MRN, ADMDATETIME ) AS newRowNumber
        FROM    cte
        CROSS APPLY ( SELECT    MAX(rownumber)
        FROM      cte AS a
        WHERE     a.rownumber <= cte.rownumber
        AND Sequence = 'First') AS test( MaxRNo )
        ORDER BY HOSPCODE , MRN , ADMDATETIME

commented on Mar 15 2012 7:39AM

Mitesh Modi

18 · 10% · 3078

if your key field is unique than you can write this in single query like this

 SELECT   ROW_NUMBER() OVER ( PARTITION BY HOSPCODE,MRN
 ORDER BY HOSPCODE, MRN, ADMDATETIME ) AS rownumber ,
 otr.*,
 ROW_NUMBER() OVER ( PARTITION BY HOSPCODE,MRN,test.MaxRNo
 ORDER BY HOSPCODE, MRN, ADMDATETIME ) AS NewRownumber 
  FROM  Live.tempnewbornbundling AS otr
  CROSS APPLY ( SELECT    MAX([key])
  FROM  Live.tempnewbornbundling AS inr
  WHERE  inr.[key] <= otr.[key]
  AND Sequence = 'First') AS test( MaxRNo )
  ORDER BY HOSPCODE , MRN , ADMDATETIME

commented on Mar 15 2012 8:22AM

Mitesh Modi

18 · 10% · 3078

Problem Definition:

I have list of records, having columns Admissionskey,MRN, hospcode, admdatetime, sepdatetime,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.

sending you test data so it would be easy for you to test

Drop table #table -- Prepare test data CREATE TABLE #table ( [Admissionskey] bigint NOT NULL PRIMARY KEY, MRN nvarchar(10) NOT NULL, hospcode nvarchar(10) NOT NULL, admdatetime datetime NOT NULL, sepdatetime datetime NOT NULL, Sequence nvarchar(10) NOT NULL )

SET DATEFORMAT DMY INSERT INTO #table( Admissionskey,MRN, hospcode, admdatetime, sepdatetime,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

I really appreciate your help. will be awaiting for further reply thanks

commented on Mar 15 2012 9:10PM

Umar001

282 · 0% · 150

Dear Mitesh, I am very thankful to you that you have given three different options. all are working well. excep if in one group if there is middle after final, it should also start with 1 as in one group.

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

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

commented on Mar 15 2012 9:44PM

Umar001

282 · 0% · 150

(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

if we take it as a whole than we should have some thing like this for now I m excluding admissions, MRN, HOSPcode, admdatetime, and Sepdatetime for now and just putting additional columns which we need to create on our own based on these.

First we need to create groups based on same MRN and HOSP CODE
arrange them in sequence in order by adm_datetime
assign seq_num [ids]
once seq_nums are assign than further group them and give new groupId, and New SeqNum and than check dates and Flag them.
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].

commented on Mar 16 2012 12:41AM

Umar001

282 · 0% · 150

To solve problem of middle after final you should either use cursor solution or below solution

SELECT   ROW_NUMBER() OVER ( PARTITION BY HOSPCODE,MRN
 ORDER BY HOSPCODE, MRN, ADMDATETIME ) AS rownumber ,
 otr.*,
 ROW_NUMBER() OVER ( PARTITION BY HOSPCODE,MRN,CASE WHEN testMax.tMaxRno > test.MaxRNo THEN testMax.tMaxRno ELSE test.MaxRNo END
 ORDER BY HOSPCODE, MRN, ADMDATETIME ) AS NewRownumber 
  FROM  #table AS otr
  CROSS APPLY ( SELECT    MAX([Admissionskey])
  FROM  #table AS inr
  WHERE  inr.[Admissionskey] <= otr.[Admissionskey]
  AND Sequence = 'First') AS test( MaxRNo )
  CROSS APPLY ( SELECT    MAX([Admissionskey])
  FROM  #table AS inr
  WHERE  inr.[Admissionskey] < otr.[Admissionskey]
  AND Sequence = 'Final') AS testMax( tMaxRNo )
  ORDER BY HOSPCODE , MRN , ADMDATETIME

commented on Mar 16 2012 4:53AM

Mitesh Modi

18 · 10% · 3078

Drop table #table Drop table #tablewithgroupid -- Prepare test data CREATE TABLE #table ( [Admissionskey] bigint NOT NULL PRIMARY KEY, MRN nvarchar(10) NOT NULL, hospcode nvarchar(10) NOT NULL, admdatetime datetime NOT NULL, sepdatetime datetime NOT NULL, Sequence nvarchar(10) NOT NULL )

;WITH cur_prev AS ( -- Match current row with row above

SELECT cur.*, prev_Sequence = prev.Sequence
   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.Admissionskey FROM curprev 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.admdatetime < c.admdatetime OR cc.admdatetime = c.admdatetime AND cc.Admissionskey <= c.Admissionskey )

	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 #tablewithgroupid FROM cur_prev c

SELECT * FROM #tablewithgroupid

SELECT t.*, g.IsGroupCorrect
FROM #tablewithgroupid 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 #tablewithgroupid tg --where datediff(MINUTE,tg.admdatetime,tg.presep_date)< =1 GROUP BY tg.GroupID ) g on t.GroupID = g.GroupID

commented on Mar 19 2012 12:42AM

Umar001

282 · 0% · 150

Thank you for your great help. it is highly appreciated. I have two more quesitons from you. I would appreciate if you can further help.

1) I am having difficulty to paste my code in reply can u please tell how to do that so on this forum. I have the code above pasted as whole if you run my code, I am getting my desired result after removing numbers you will get my desired result except comparison of dates

can you please advice me how can I compare dates admission date of 1 record with the separation date of preceding record [a record that appears before it]

the dereference should be only < = 1 minute between admission_date and separation date of previous record.

if difference is <= 1 than we should mark C other wise E in the same subgroup

Thanks, will be waiting for your reply

commented on Mar 19 2012 12:51AM

Umar001

282 · 0% · 150

42Use this code please42 42

42 Drop table #table Drop table #tablewithgroupid -- Prepare test data CREATE TABLE #table ( [Admissionskey] bigint NOT NULL PRIMARY KEY, MRN nvarchar(10) NOT NULL, hospcode nvarchar(10) NOT NULL, admdatetime datetime NOT NULL, sepdatetime 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




     42

commented on Mar 19 2012 1:34AM

Umar001

282 · 0% · 150

I have little bit modified your code please test it at your side

;WITH cur_prev AS
(   -- Match current row with row above

SELECT cur.*, prev_Sequence = prev.Sequence, ISNULL(DATEDIFF(MINUTE, CASE WHEN cur.Sequence='First' THEN NULL ELSE prev.sep_datetime  END,cur.adm_datetime) ,1) AS timemin,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 SUM(CASE WHEN tg.timemin < 0 THEN 0 ELSE tg.timemin end ) <= COUNT(tg.timemin)
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

commented on Mar 19 2012 6:32AM

Mitesh Modi

18 · 10% · 3078

How to post the code in this forum

http://beyondrelational.com/modules/23/pages/362/posts/7654/how-to-use-the-editor.aspx

http://beyondrelational.com/ask/public/questions/298/formatting-your-code-when-posting-in-the-forums.aspx

commented on Mar 21 2012 3:53AM

Mitesh Modi

18 · 10% · 3078

SET DATEFORMAT DMY
INSERT INTO #table( Admissions_key,MRN, hosp_code, adm_datetime, sep_datetime,Sequence)
VALUES

-- case 1:
(7099221,'0000544607','0003','22/07/2011 04:55','22/07/2011 10:44','First'),   --C   2 Duplicate Middle episodes same adm_date, Sep_date
(7099222,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle'),  --C
(7099223,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle'),  --E  one should be eliminated as it is duplicated, rest should be Correct
(7099224,'0000544607','0003','25/07/2011 19:44','26/07/2011 11:29','Final'),   --C

--Case 2:
(7099225,'0000559282','0003','03/07/2011 22:50','03/07/2011 23:51','First'),  -- C  First, Middle, Final,Final, First three records will
(7099226,'0000559282','0003','03/07/2011 23:52','04/07/2011 15:30','Middle'), --  C      Will be  correct as ther is final which will be considered  
(7099227,'0000559282','0003','04/07/2011 15:31','04/07/2011 17:59','Final'),  -- C       as part of another group.
(7099228,'0000559282','0003','04/07/2011 15:31','04/07/2011 19:00','Final'), -- E

--Case 3: 
(7099229,'0000999271','0003','07/08/2011 01:00','07/08/2011 19:00','First'), -- E   -- this first is error, becasue in this group, there is no middle and final
(7099230,'0000999271','0003','07/08/2011 01:00','07/08/2011 18:05','First'), -- C
(7099231,'0000999271','0003','07/08/2011 18:06','09/08/2011 14:58','Middle'),-- C
(7099232,'0000999271','0003','09/08/2011 14:59','09/08/2011 16:00','Final'), -- C

--Case 4: 
(7099233,'0000999275','0003','09/08/2011 17:58','09/08/2011 18:00','First'),  --E -- all errors, because there dates do not match
(7099234,'0000999275','0003','09/08/2011 16:01','10/08/2011 19:00','Middle'), --E -- logically this is an error, but this is not to be worried at this stage.
(7099235,'0000999275','0003','10/08/2011 18:01','10/08/2011 20:00','Middle'), --E
(7099236,'0000999275','0003','10/08/2011 20:01','10/08/2011 22:00','Final'), --E

--Case 5:
(7099237,'0000999276','0003','11/08/2011 15:38','11/08/2011 18:00','First'), -- C  --  first three should be a correct group 
(7099238,'0000999276','0003','11/08/2011 18:01','11/08/2011 22:00','Middle'), -- C  
(7099240,'0000999276','0003','11/08/2011 22:01','12/08/2011 02:00','Final'), -- C
(7099241,'0000999276','0003','12/08/2011 02:01','12/08/2011 05:00','Final'), --E

--Case 6:
(7099242,'0000999277','0003','11/08/2011 15:38','11/08/2011 18:00','First'), -- C  --  first three should be a correct group 
(7099243,'0000999277','0003','11/08/2011 18:01','11/08/2011 22:00','Middle'), -- C  
(7099244,'0000999277','0003','11/08/2011 22:01','12/08/2011 02:00','Final'), -- C
(7099245,'0000999277','0003','12/08/2011 02:01','12/08/2011 05:00','Middle'), --E 

--Case 7: 
(7099246,'0000999298','0003','13/08/2011 16:28','13/08/2011 23:00','First'), -- E  --  First one should be error, 
(7099247,'0000999298','0003','13/08/2011 18:30','13/08/2011 20:00','First'), -- C  --   Last three should be a correct group 
(7099248,'0000999298','0003','13/08/2011 20:01','14/08/2011 02:00','Middle'), -- C  
(7099249,'0000999298','0003','14/08/2011 02:01','14/08/2011 04:00','Final'), -- C

--Case 8: 
(7099250,'0000999301','0003','14/08/2011 15:38','14/08/2011 23:00','Middle'), -- E  --  Last three should be a correct group 
(7099251,'0000999301','0003','14/08/2011 20:00','15/08/2011 08:00','First'), -- C  --  
(7099252,'0000999301','0003','15/08/2011 08:01','15/08/2011 21:00','Middle'), -- C  
(7099253,'0000999301','0003','15/08/2011 21:01','16/08/2011 02:00','Final'), -- C


--Case 9: 
(7099254,'0000999302','0003','16/08/2011 11:28','16/08/2011 18:00','Final'), -- E  --  Last three should be a correct group 
(7099255,'0000999302','0003','16/08/2011 17:00','16/08/2011 19:00','First'), -- C  --  
(7099256,'0000999302','0003','16/08/2011 19:01','16/08/2011 23:00','Middle'), --C  
(7099257,'0000999302','0003','16/08/2011 23:01','17/08/2011 01:00','Final'), -- C


--Case 10: 
(7099258,'0000999333','0003','18/08/2011 15:38','19/08/2011 22:00','First'),  -- C  --  Ther will be two groups as both groups standalone meet
(7099259,'0000999333','0003','19/08/2011 22:01','19/08/2011 23:00','Middle'),  -- C   -- criteria of first and final and are within 1 minute difference
(7099260,'0000999333','0003','19/08/2011 23:01','20/08/2011 03:00','Final'), -- C  
(7099261,'0000999333','0003','19/08/2011 23:01','20/08/2011 03:00','Final'), -- E  

(7099262,'0000999333','0003','20/08/2011 01:00','20/08/2011 08:00','First'), -- E  
(7099263,'0000999333','0003','20/08/2011 01:00','20/08/2011 08:00','First'), -- C  
(7099264,'0000999333','0003','20/08/2011 08:01','21/08/2011 01:00','Middle'), --C  
(7099265,'0000999333','0003','21/08/2011 01:01','21/08/2011 22:00','Final') -- C  


;WITH cur_prev AS 
(   -- Match current row with row above
Select * , IsRecordCorrect  = Case when RecordDuplicate = 'DN' and DateMark ='Y' then 'Y' else 'N' end from (
    SELECT cur.*, prev_Sequence = prev.Sequence,
    CASE  WHEN cur.MRN =prev.MRN AND cur.hosp_code = prev.hosp_code and cur.adm_datetime = prev.adm_datetime and cur.sep_datetime = prev.sep_datetime THEN 'DY' ELSE 'DN' END AS RecordDuplicate,
    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 ) correct
)




SELECT c.*,  Case When c.RecordDuplicate = 'DY' and c.Sequence <> 'First' then '0' else
    	(	-- 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
    )   end as GroupID 
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 and date mark is yes
    			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  tg.RecordDuplicate = 'DN'
    	GROUP BY tg.GroupID
    ) g on t.GroupID = g.GroupID) as a

commented on Mar 21 2012 6:12PM

Umar001

282 · 0% · 150

Thanks, Mitesh, I also had to figure out duplicated and I had solved this probelm yesterday with a different technique. I really appreciate your help in this regard. I really learned a lot from your posts. Thanks

commented on Mar 21 2012 6:14PM

Umar001

282 · 0% · 150

Your Reply

Sign Up or Login to post a comment.