resarting row numbers based on values inside categ

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 2008 Forums

Transact-SQL (2008)

resarting row numbers based on values inside categ

Author

Topic

Umar001
Starting Member

10 Posts

Posted - 2012-03-14 : 02:30:52

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

Umar Memon

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-14 : 15:54:55

I stared at your posting, but nothing simple comes to mind. One should be able to do this simply and elegantly using some approach related to the classic "islands and gaps" problem. Or may be something even simpler. However, here is the first thing that comes to my mind (concept only) - hoping someone would post something simpler:

;WITH cte1 AS
(
	SELECT
		ROW_NUMBER() OVER(ORDER BY [HOSP_CODE], MRN, ADM_DATETIME) AS rownumber,
		*
	FROM
		Live.tempnewbornbundling
),
cte2 AS
(
	SELECT a.*,a.rownumber AS N
	FROM cte1 a LEFT JOIN cte1 b ON a.rownumber = b.rownumber+1
	WHERE a.Sequence = 'First' OR b.Sequence = 'Last'
	
	UNION ALL
	
	SELECT c1.*,c2.N
	FROM cte1 c1 INNER JOIN cte2 c2 ON c2.rownumber + 1 = c1.rownumber AND c1.Sequence <> 'First'
)
SELECT
	*,
	ROW_NUMBER() OVER (PARTITION BY [HOSP_CODE],[MRN],N order by [HOSP_CODE],MRN,ADM_DATETIME)
		AS NewRowNumber
FROM
	cte2
ORDER BY
	[HOSP_CODE],MRN,ADM_DATETIME

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 16:42:16

quote:
Originally posted 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

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

Umar Memon


SELECT t.Rownumber,t.key,t.MRN,t.hospcode,t.adm_date,t.sep_date,t.Sequence,
ROW_NUMBER() OVER (PARTITION BY  t.MRN,t.hospcode,t1.key ORDER BY t.key) AS newro
FROM table t
OUTER APPLY(SELECT TOP 1 key 
            FROM table
            WHERE hospcode = t.hospcode 
            AND MRN = t.MRN
            AND key > t.key
            AND Sequence ='First'
            ORDER BY Key
           )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Umar001
Starting Member

10 Posts

Posted - 2012-03-15 : 21:31:21

quote:
Originally posted by sunitabeck

I stared at your posting, but nothing simple comes to mind. One should be able to do this simply and elegantly using some approach related to the classic "islands and gaps" problem. Or may be something even simpler. However, here is the first thing that comes to my mind (concept only) - hoping someone would post something simpler:
;WITH cte1 AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY [HOSP_CODE], MRN, ADM_DATETIME) AS rownumber,
*
FROM
Live.tempnewbornbundling
),
cte2 AS
(
SELECT a.*,a.rownumber AS N
FROM cte1 a LEFT JOIN cte1 b ON a.rownumber = b.rownumber+1
WHERE a.Sequence = 'First' OR b.Sequence = 'Last'

UNION ALL

SELECT c1.*,c2.N
FROM cte1 c1 INNER JOIN cte2 c2 ON c2.rownumber + 1 = c1.rownumber AND c1.Sequence <> 'First'
)
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY [HOSP_CODE],[MRN],N order by [HOSP_CODE],MRN,ADM_DATETIME)
AS NewRowNumber
FROM
cte2
ORDER BY
[HOSP_CODE],MRN,ADM_DATETIME

Thank you for your reply, I think I was unable to explain correctly and I am sorry I did not send test data so here is the script and further explanation of my problem.

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')

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 because i also need to compare dates later 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

Umar Memon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 23:11:22

see my illustration below


set dateformat dmy

declare @test table
(
Rownumber int,
	 [key] int,
 MRN	int,
 hospcode int,
	 adm_date datetime,
	 sep_date datetime,
	 Sequence  varchar(100)
)
insert @test
select 1,	 7099222,	544607,	3,	 '22/07/2011 04:55', '22/07/2011 10:44',	 'First' union all	
select 2,	 7099223,	544607,	3,	 '22/07/2011 10:45', '25/07/2011 19:43',	 'Middle' union all	
select 3,	 7099224,	544607,	3,	 '25/07/2011 19:44', '26/07/2011 11:29',	 'Middle' union all	
select 4,	 7099225,	544607,	3,	 '27/07/2011 12:30',	 '27/07/2011 19:30',	 'First' union all	
select 5,	 7099226,	544607,	3,	 '27/07/2011 19:31',	 '28/07/2011 19:31',	 'Final' union all	
select 1,	 7099227,	559282,	3,	 '03/07/2011 22:50',	 '03/07/2011 23:51',	 'First' union all	
select 2,	 7099228,	559282,	3,	 '03/07/2011 23:52',	 '04/07/2011 15:30',	 'Middle' union all
select 3,	 7099229,	559282,	3,	 '04/07/2011 15:31',	 '04/07/2011 17:59',	 'Final' union all
select 4,	 7099230,	559282,	3,	 '05/07/2011 18:00'	, '05/07/2011 18:05',	 'First' union all
select 5,	 7099231,	559282,	3,	 '05/07/2011 18:06'	, '09/07/2011 14:58',	 'Final'


SELECT t.Rownumber,t.[key],t.MRN,t.hospcode,t.adm_date,t.sep_date,t.Sequence,
ROW_NUMBER() OVER (PARTITION BY  t.MRN,t.hospcode,t1.[key] ORDER BY t.[key]) AS newro
FROM @test t
OUTER APPLY(SELECT TOP 1 [key]
            FROM @test
            WHERE hospcode = t.hospcode 
            AND MRN = t.MRN
            AND [key] > t.[key]
            AND Sequence ='First'
            ORDER BY [Key]
           )t1
order by [key]


output
--------------------------------------------------------------------
Rownumber	key	MRN	hospcode	adm_date	sep_date	       Sequence	newro
1	       7099222	544607	3	2011-07-22 04:55:00.000	2011-07-22 10:44:00.000	First	1
2	       7099223	544607	3	2011-07-22 10:45:00.000	2011-07-25 19:43:00.000	Middle	2
3	       7099224	544607	3	2011-07-25 19:44:00.000	2011-07-26 11:29:00.000	Middle	3
4	       7099225	544607	3	2011-07-27 12:30:00.000	2011-07-27 19:30:00.000	First	1
5	       7099226	544607	3	2011-07-27 19:31:00.000	2011-07-28 19:31:00.000	Final	2
1	       7099227	559282	3	2011-07-03 22:50:00.000	2011-07-03 23:51:00.000	First	1
2	       7099228	559282	3	2011-07-03 23:52:00.000	2011-07-04 15:30:00.000	Middle	2
3	       7099229	559282	3	2011-07-04 15:31:00.000	2011-07-04 17:59:00.000	Final	3
4	       7099230	559282	3	2011-07-05 18:00:00.000	2011-07-05 18:05:00.000	First	1
5	       7099231	559282	3	2011-07-05 18:06:00.000	2011-07-09 14:58:00.000	Final	2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Umar001
Starting Member

10 Posts

Posted - 2012-03-16 : 00:19:17

quote:

Originally posted by visakh16

see my illustration below


set dateformat dmy

declare @test table
(
Rownumber int,
	 [key] int,
 MRN	int,
 hospcode int,
	 adm_date datetime,
	 sep_date datetime,
	 Sequence  varchar(100)
)
insert @test
select 1,	 7099222,	544607,	3,	 '22/07/2011 04:55', '22/07/2011 10:44',	 'First' union all	
select 2,	 7099223,	544607,	3,	 '22/07/2011 10:45', '25/07/2011 19:43',	 'Middle' union all	
select 3,	 7099224,	544607,	3,	 '25/07/2011 19:44', '26/07/2011 11:29',	 'Middle' union all	
select 4,	 7099225,	544607,	3,	 '27/07/2011 12:30',	 '27/07/2011 19:30',	 'First' union all	
select 5,	 7099226,	544607,	3,	 '27/07/2011 19:31',	 '28/07/2011 19:31',	 'Final' union all	
select 1,	 7099227,	559282,	3,	 '03/07/2011 22:50',	 '03/07/2011 23:51',	 'First' union all	
select 2,	 7099228,	559282,	3,	 '03/07/2011 23:52',	 '04/07/2011 15:30',	 'Middle' union all
select 3,	 7099229,	559282,	3,	 '04/07/2011 15:31',	 '04/07/2011 17:59',	 'Final' union all
select 4,	 7099230,	559282,	3,	 '05/07/2011 18:00'	, '05/07/2011 18:05',	 'First' union all
select 5,	 7099231,	559282,	3,	 '05/07/2011 18:06'	, '09/07/2011 14:58',	 'Final'


SELECT t.Rownumber,t.[key],t.MRN,t.hospcode,t.adm_date,t.sep_date,t.Sequence,
ROW_NUMBER() OVER (PARTITION BY  t.MRN,t.hospcode,t1.[key] ORDER BY t.[key]) AS newro
FROM @test t
OUTER APPLY(SELECT TOP 1 [key]
            FROM @test
            WHERE hospcode = t.hospcode 
            AND MRN = t.MRN
            AND [key] > t.[key]
            AND Sequence ='First'
            ORDER BY [Key]
           )t1
order by [key]


output
--------------------------------------------------------------------
Rownumber	key	MRN	hospcode	adm_date	sep_date	       Sequence	newro
1	       7099222	544607	3	2011-07-22 04:55:00.000	2011-07-22 10:44:00.000	First	1
2	       7099223	544607	3	2011-07-22 10:45:00.000	2011-07-25 19:43:00.000	Middle	2
3	       7099224	544607	3	2011-07-25 19:44:00.000	2011-07-26 11:29:00.000	Middle	3
4	       7099225	544607	3	2011-07-27 12:30:00.000	2011-07-27 19:30:00.000	First	1
5	       7099226	544607	3	2011-07-27 19:31:00.000	2011-07-28 19:31:00.000	Final	2
1	       7099227	559282	3	2011-07-03 22:50:00.000	2011-07-03 23:51:00.000	First	1
2	       7099228	559282	3	2011-07-03 23:52:00.000	2011-07-04 15:30:00.000	Middle	2
3	       7099229	559282	3	2011-07-04 15:31:00.000	2011-07-04 17:59:00.000	Final	3
4	       7099230	559282	3	2011-07-05 18:00:00.000	2011-07-05 18:05:00.000	First	1
5	       7099231	559282	3	2011-07-05 18:06:00.000	2011-07-09 14:58:00.000	Final	2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

you are right but in case where in 1 group the sequence is like below it would not work

1 First -- 1
2 Middle -- 2
3 Final --3
4 Middle -- your script is doing it as 4, it should be 1, as after final, group should restart, no matter if there is middle or first or final.
5 First ----- this should be 1 as your script is doing

Umar Memon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 01:16:09

this?


declare @test table
(
Rownumber int,
	 [key] int,
 MRN	int,
 hospcode int,
	 adm_date datetime,
	 sep_date datetime,
	 Sequence  varchar(100)
)
insert @test
select 1,	 7099222,	544607,	3,	 '22/07/2011 04:55', '22/07/2011 10:44',	 'First' union all	
select 2,	 7099223,	544607,	3,	 '22/07/2011 10:45', '25/07/2011 19:43',	 'Middle' union all	
select 3,	 7099224,	544607,	3,	 '25/07/2011 19:44', '26/07/2011 11:29',	 'Final' union all	
select 4,	 7099225,	544607,	3,	 '27/07/2011 12:30',	 '27/07/2011 19:30',	 'First' union all	
select 5,	 7099226,	544607,	3,	 '28/07/2011 19:31',	 '28/07/2011 20:31',	 'Final' union all	
select 5,	 7099227,	544607,	3,	 '29/07/2011 22:31',	 '30/07/2011 19:31',	 'Middle' union all	
select 5,	 7099228,	544607,	3,	 '12/08/2011 23:01',	 '13/08/2011 19:31',	 'Final' union all	
select 1,	 7099229,	559282,	3,	 '03/07/2011 22:50',	 '03/07/2011 23:51',	 'First' union all	
select 2,	 7099230,	559282,	3,	 '03/07/2011 23:52',	 '04/07/2011 15:30',	 'Middle' union all
select 3,	 7099231,	559282,	3,	 '04/07/2011 15:31',	 '04/07/2011 17:59',	 'Final' union all
select 4,	 7099232,	559282,	3,	 '05/07/2011 18:00'	, '05/07/2011 18:05',	 'First' union all
select 5,	 7099233,	559282,	3,	 '05/07/2011 18:06'	, '09/07/2011 14:58',	 'Final'


SELECT t.Rownumber,t.[key],t.MRN,t.hospcode,t.adm_date,t.sep_date,t.Sequence,t1.[key],
ROW_NUMBER() OVER (PARTITION BY  t.MRN,t.hospcode,t1.[key] ORDER BY t.[key]) AS newro
FROM @test t
OUTER APPLY(SELECT TOP 1 [key]
            FROM @test
            WHERE hospcode = t.hospcode 
            AND MRN = t.MRN
            AND [key] >= t.[key]
            AND Sequence ='Final'
            ORDER BY [Key] 
           )t1
order by t.[key]


output
---------------------------------------
Rownumber	key	MRN	hospcode	adm_date	sep_date	Sequence	key	newro
1	7099222	544607	3	2011-07-22 04:55:00.000	2011-07-22 10:44:00.000	First	7099224	1
2	7099223	544607	3	2011-07-22 10:45:00.000	2011-07-25 19:43:00.000	Middle	7099224	2
3	7099224	544607	3	2011-07-25 19:44:00.000	2011-07-26 11:29:00.000	Final	7099224	3
4	7099225	544607	3	2011-07-27 12:30:00.000	2011-07-27 19:30:00.000	First	7099226	1
5	7099226	544607	3	2011-07-28 19:31:00.000	2011-07-28 20:31:00.000	Final	7099226	2
5	7099227	544607	3	2011-07-29 22:31:00.000	2011-07-30 19:31:00.000	Middle	7099228	1
5	7099228	544607	3	2011-08-12 23:01:00.000	2011-08-13 19:31:00.000	Final	7099228	2
1	7099229	559282	3	2011-07-03 22:50:00.000	2011-07-03 23:51:00.000	First	7099231	1
2	7099230	559282	3	2011-07-03 23:52:00.000	2011-07-04 15:30:00.000	Middle	7099231	2
3	7099231	559282	3	2011-07-04 15:31:00.000	2011-07-04 17:59:00.000	Final	7099231	3
4	7099232	559282	3	2011-07-05 18:00:00.000	2011-07-05 18:05:00.000	First	7099233	1
5	7099233	559282	3	2011-07-05 18:06:00.000	2011-07-09 14:58:00.000	Final	7099233	2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Umar001
Starting Member

10 Posts

Posted - 2012-03-16 : 07:43:40

thank you for your quick replies, how ever still i think that it is not what I exactly want.

I think I am unable to explain properly so explaining once again

in your case in first group you are doing as following

First 1
Middle 2
Final 3
First 1
Final 2
Middle 1
Final 2

however there may be a case like,

First 1
Middle 2
First 1
Final 2
Middle 1
Final 2

let me explain you why I want this so it may help you to understand my exact requirements
. 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 [first and Final] and groups for First, Middle or Middle, Finals are unacceptable
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].

<pre>
(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
</pre>
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.

Umar Memon

Umar001
Starting Member

10 Posts

Posted - 2012-03-16 : 07:49:31

SELECT t.Rownumber,t.[key],t.MRN,t.hospcode,t.adm_date,t.sep_date,t.Sequence,
ROW_NUMBER() OVER (PARTITION BY t.MRN,t.hospcode,t1.[key] ORDER BY t.[key]) AS newro
FROM @test t
OUTER APPLY(SELECT TOP 1 [key]
FROM @test
WHERE hospcode = t.hospcode
AND MRN = t.MRN
AND (([key] >= t.[key] AND Sequence ='Final') OR ([key] > t.[key] AND Sequence ='First'))
ORDER BY [Key]
)t1
order by t.[key]

Umar Memon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:22:10

this is problem of not providing with full set of sample data covering all scenarios you wanted. ok..let me have a go at this again...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources