resarting row number based on values in categories

Question

resarting row number based on values in categories

umar.memon

SSC Veteran

Points: 204
More actions
March 14, 2012 at 12:24 am

#257378

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 70992225446073 22/07/2011 04:55 22/07/2011 10:44 First
2 70992235446073 22/07/2011 10:45 25/07/2011 19:43 Middle
3 70992245446073 25/07/2011 19:44 26/07/2011 11:29 Middle
4 70992255446073 27/07/2011 12:30 27/07/2011 19:30 First
5 70992265446073 27/07/2011 19:31 28/07/2011 19:31 Final
1 70992275592823 03/07/2011 22:50 03/07/2011 23:51 First
2 70992285592823 03/07/2011 23:52 04/07/2011 15:30 Middle
3 70992295592823 04/07/2011 15:31 04/07/2011 17:59 Final
4 70992305592823 05/07/2011 18:00 05/07/2011 18:05 First
5 70992315592823 05/07/2011 18:06 09/07/2011 14:58 Final
Rownumber key MRNhospcodeadm_date sep_date Sequence newro
1 7099222 5446073 22/07/2011 04:55 22/07/2011 10:44First 1
2 7099223 5446073 22/07/2011 10:45 25/07/2011 19:43Middle2
3 7099224 5446073 25/07/2011 19:44 26/07/2011 11:29Middle3
4 7099225 5446073 27/07/2011 12:30 27/07/2011 19:30First 1
5 7099226 5446073 27/07/2011 19:31 28/07/2011 19:31Final 2
1 7099227 5592823 03/07/2011 22:50 03/07/2011 23:51First 1
2 7099228 5592823 03/07/2011 23:52 04/07/2011 15:30Middle2
3 7099229 5592823 04/07/2011 15:31 04/07/2011 17:59Final 3
4 7099230 5592823 05/07/2011 18:00 05/07/2011 18:05First 1
5 7099231 5592823 05/07/2011 18:06 09/07/2011 14:58Final 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

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

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://www.sqlservercentral.com/articles/Best+Practices/61537/

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 2

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 data

CREATE 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

umar.memon SSC Veteran Points: 204 More actions · Answer 3

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

umar.memon SSC Veteran Points: 204 More actions · Answer 4

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.

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 5

In even longer example, You should achieve these groups:

(id, Sequence, SequenceNum, GroupId)

1 First 1 1

2 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

gladys14g2 SSC Enthusiast Points: 104 More actions · Answer 6

gladys14g2

SSC Enthusiast

Points: 104

March 15, 2012 at 9:34 pm

#1460298

thanks for these

umar.memon SSC Veteran Points: 204 More actions · Answer 7

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.

keyMRN hosp_codeadm_datetime sep_datetime Seq I_Group Order1 F_Group Order2 Flag

70992225446073 22/07/2011 04:55 22/07/2011 10:44 First111a1E

70992235446073 22/07/2011 10:45 25/07/2011 19:43 Middle121a2E

70992245446073 25/07/2011 19:44 26/07/2011 11:29 Middle131a3E

70992255446073 27/07/2011 13:30 27/07/2011 19:30 First141b1C

70992265446073 27/07/2011 19:31 28/07/2011 11:30 Final151b2C

70992275592823 03/07/2011 22:50 03/07/2011 23:51 First212a1C

70992285592823 03/07/2011 23:52 04/07/2011 15:30 Middle222a2C

70992295592823 04/07/2011 15:31 04/07/2011 17:59 Final232a3C

70992305592823 05/07/2011 18:00 05/07/2011 18:05 Middle242b1E

70992315592823 05/07/2011 18:06 09/07/2011 14:58 Final252b2E

70992329992713 07/08/2011 01:00 07/08/2011 18:05 Middle313a1E

70992339992713 07/08/2011 18:06 09/08/2011 14:58 Final323a2E

70992349992713 10/08/2011 18:00 10/08/2011 18:05 First333b1E

70992354464353 11/08/2011 18:06 12/08/2011 12:08 First414a1C

70992364464353 12/08/2011 12:09 12/08/2011 14:58 Final424a2C

umar.memon SSC Veteran Points: 204 More actions · Answer 8

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.

umar.memon SSC Veteran Points: 204 More actions · Answer 9

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.

umar.memon SSC Veteran Points: 204 More actions · Answer 10

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

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 11

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://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

So, I will do that instead of you:

-- Prepare test data

CREATE 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 result

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

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

umar.memon SSC Veteran Points: 204 More actions · Answer 12

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.

umar.memon SSC Veteran Points: 204 More actions · Answer 13

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?

umar.memon SSC Veteran Points: 204 More actions · Answer 14