locked
Row Number/Dense Rank RRS feed

  • Question

  • Hi, I'm trying to create an additional field that provides a number that only changes when the Number and Phase changes ordered by date.  

    Declare @Sample Table
    	(
    	Number int,
    	CreateDate int,
    	Phase varchar(50)
    	)
    Insert Into @Sample Values
    	(1916,20160219,'Deteriorating'),
    	(1916,20160513,'Deteriorating'),
    	(1916,20160605,'Deceased'),
    	(2247,20160229,'Deteriorating'),
    	(2247,20160311,'Deteriorating'),
    	(2247,20160316,'Deteriorating'),
    	(2247,20160601,'Deteriorating'),
    	(2247,20160711,'Deteriorating'),
    	(2247,20160831,'Deteriorating'),
    	(2247,20160914,'Deteriorating'),
    	(2247,20160920,'Stable'),
    	(4963,20160523,'Deteriorating'),
    	(4963,20160615,'UnStable'),
    	(4963,20160616,'UnStable'),
    	(4963,20160621,'Dying'),
    	(4963,20160622,'Dying'),
    	(4963,20160628,'Deceased')
    
    Select * From @Sample
    
    

    So it looks like this

    Number	CreateDate	Phase
    1916	20160219	Deteriorating
    1916	20160513	Deteriorating
    1916	20160605	Deceased
    2247	20160229	Deteriorating
    2247	20160311	Deteriorating
    2247	20160316	Deteriorating
    2247	20160601	Deteriorating
    2247	20160711	Deteriorating
    2247	20160831	Deteriorating
    2247	20160914	Deteriorating
    2247	20160920	Stable
    4963	20160523	Deteriorating
    4963	20160615	UnStable
    4963	20160616	UnStable
    4963	20160621	Dying
    4963	20160622	Dying
    4963	20160628	Deceased

    So what I want to be able to show is the following

    Number	CreateDate	Phase               PhaseNo
    1916	20160219	Deteriorating   1
    1916	20160513	Deteriorating   1
    1916	20160605	Deceased         2
    2247	20160229	Deteriorating   1
    2247	20160311	Deteriorating   1
    2247	20160316	Deteriorating   1
    2247	20160601	Deteriorating   1
    2247	20160711	Deteriorating   1
    2247	20160831	Deteriorating   1
    2247	20160914	Deteriorating   1
    2247	20160920	Stable           2
    4963	20160523	Deteriorating   1
    4963	20160615	UnStable          2
    4963	20160616	UnStable         2
    4963	20160621	Dying              3
    4963	20160622	Dying              3
    4963	20160628	Deceased        4 

    The closest I have got is using Dense_Rank() Over(Partition By Number Order By Number, Phase desc) but this still doesn't work.  

    This is the first time entering the forum so please let me know if I haven't shown something correctly

    Wednesday, December 13, 2017 4:35 PM

Answers

  • Here you go with full illustration with additional data added to denote full scenario

    Declare @Sample Table
    	(
    	Number int,
    	CreateDate int,
    	Phase varchar(50)
    	)
    Insert Into @Sample Values
    	(1916,20160219,'Deteriorating'),
    	(1916,20160513,'Deteriorating'),
    	(1916,20160605,'Deceased'),
    	(2247,20160229,'Deteriorating'),
    	(2247,20160311,'Deteriorating'),
    	(2247,20160316,'Deteriorating'),
    	(2247,20160601,'Deteriorating'),
    	(2247,20160711,'Deteriorating'),
    	(2247,20160831,'Deteriorating'),
    	(2247,20160914,'Deteriorating'),
    	(2247,20160920,'Stable'),
    	(4963,20160523,'Deteriorating'),
    	(4963,20160615,'UnStable'),
    	(4963,20160616,'UnStable'),
    	(4963,20160621,'Dying'),
    	(4963,20160622,'Dying'),
    	(4963,20160628,'Deceased'),
    	(5017,20160520,'Deteriorating'),
    	(5017,20160525,'Deteriorating'),
    	(5017,20160602,'UnStable'),
    	(5017,20160613,'Stable'),
    	(5017,20160620,'UnStable'),
    	(5017,20160625,'Stable'),
    	(5017,20160628,'Stable'),
    	(5017,20160703,'Deteriorating'),
    	(5017,20160709,'Deteriorating'),
    	(5017,20160711,'Dying'),
    	(5017,20160715,'Deceased')
    
    	SELECT s.*,DENSE_RANK() OVER (PARTITION BY Number ORDER BY LastPhaseDate) AS PhaseNo
    	FROM @Sample s
    	OUTER APPLY (
    	SELECT MAX(CreateDate) AS LastPhaseDate
    	FROM @Sample
    	WHERE Number = s.Number
    	AND CreateDate < s.CreateDate
    	AND Phase <> s.Phase)s1
    
    
    
    /*
    output
    --------------------------------------------------------
    
    Number	CreateDate	Phase	        PhaseNo
    --------------------------------------------------------
    1916	20160219	Deteriorating	1
    1916	20160513	Deteriorating	1
    1916	20160605	Deceased	2
    2247	20160229	Deteriorating	1
    2247	20160311	Deteriorating	1
    2247	20160316	Deteriorating	1
    2247	20160601	Deteriorating	1
    2247	20160711	Deteriorating	1
    2247	20160831	Deteriorating	1
    2247	20160914	Deteriorating	1
    2247	20160920	Stable	        2
    4963	20160523	Deteriorating	1
    4963	20160615	UnStable	2
    4963	20160616	UnStable	2
    4963	20160621	Dying	        3
    4963	20160622	Dying	        3
    4963	20160628	Deceased	4
    5017	20160520	Deteriorating	1
    5017	20160525	Deteriorating	1
    5017	20160602	UnStable	2
    5017	20160613	Stable	        3
    5017	20160620	UnStable	4
    5017	20160625	Stable	        5
    5017	20160628	Stable	        5
    5017	20160703	Deteriorating	6
    5017	20160709	Deteriorating	6
    5017	20160711	Dying	        7
    5017	20160715	Deceased	8
    
    
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ASalter87 Thursday, December 14, 2017 9:43 AM
    Thursday, December 14, 2017 9:25 AM

All replies

  • Hi Salter,

    Considering "Phase" values are important to make required numbering. Please check following code, if it fits into your requirement:

    ;WITH cte AS(
    SELECT *,
    DENSE_RANK() OVER(PARTITION BY NUMBER ORDER BY number,
    CASE 
    WHEN Phase = 'Deteriorating' then '1'
    WHEN Phase = 'UnStable' then '2'
    WHEN Phase = 'Dying' then '3'
    WHEN Phase = 'Deceased' then '4'
    ELSE Phase END ) AS DR 
    From @Sample
    )
    SELECT * FROM cte ORDER BY Number,DR


    SM

    Wednesday, December 13, 2017 5:10 PM
  • What is the logic to generate PhaseNo?

    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 13, 2017 5:18 PM
  • This problem is known as "Finding Islands".

    One solution is to identify when a group starts (1) and then calculate the running total.

    WITH R AS (
    SELECT
        Number,
        CreateDate,
        Phase,
        CASE
        WHEN LAG(Phase) OVER(PARTITION BY Number ORDER BY CreateDate) = Phase THEN 0 ELSE 1 END AS grp_start
    FROM
    	@Sample
    )
    SELECT
        Number,
        CreateDate,
        Phase,
        SUM(grp_start) OVER(PARTITION BY Number ORDER BY 
        CreateDate ROWS UNBOUNDED PRECEDING) AS grp
    FROM
        R
    ORDER BY
        Number,
        CreateDate;

    http://www.itprotoday.com/microsoft-sql-server/solving-gaps-and-islands-enhanced-window-functions



    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    Wednesday, December 13, 2017 6:57 PM
  • Hi ASalter87,

    Welcome to Transact-SQL community forum.

    Do you mean to want this?

    Declare @Sample Table
    	(
    	Number int,
    	CreateDate int,
    	Phase varchar(50)
    	)
    Insert Into @Sample Values
    	(1916,20160219,'Deteriorating'),
    	(1916,20160513,'Deteriorating'),
    	(1916,20160605,'Deceased'),
    	(2247,20160229,'Deteriorating'),
    	(2247,20160311,'Deteriorating'),
    	(2247,20160316,'Deteriorating'),
    	(2247,20160601,'Deteriorating'),
    	(2247,20160711,'Deteriorating'),
    	(2247,20160831,'Deteriorating'),
    	(2247,20160914,'Deteriorating'),
    	(2247,20160920,'Stable'),
    	(4963,20160523,'Deteriorating'),
    	(4963,20160615,'UnStable'),
    	(4963,20160616,'UnStable'),
    	(4963,20160621,'Dying'),
    	(4963,20160622,'Dying'),
    	(4963,20160628,'Deceased')
    
    ;with cte as 
    (
    select ROW_NUMBER() over (partition by Number order by CreateDate) as rn,
    Number,
    Phase
    from @Sample
    )
    Select 
    Number,CreateDate,Phase,DENSE_RANK() OVER (PARTITION BY Number ORDER BY T1.rs) as PhaseNo
    From @Sample T
    cross apply (select min(rn) as rs from cte where Number=T.Number and Phase=T.Phase) T1

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 8:56 AM
  • The logic to the Phase number is that based on the date, the phase will start out as 1 for each number.  I want to then show a 2 when the phase changes for that number and then 3 when it changes again, even if it goes back to one of the phases that it had before.

    So as an example below, the number 14634 goes from Unstable, Stable, Unstable, Stable and back to Unstable.  So therefore I want the Phase number to show as below.

    Phase No Number Date Phase
    1 14634 05/03/2015 UnStable
    2 14634 10/07/2015 Stable
    3 14634 26/02/2016 UnStable
    3 14634 15/03/2016 UnStable
    3 14634 20/03/2016 UnStable
    4 14634 28/04/2016 Stable
    5 14634 05/07/2016 UnStable



    Aaron

    Thursday, December 14, 2017 9:11 AM
  • Thanks, this was closer than I got however if the patient goes back to a previous phase i.e Stable, Unstable, Stable.  I want the second set of Stables to show as phase 3.  Also by numbering the phases as you have above, I don't think that this works if the patient goes from Unstable to Deteriorating as it will show Phase 2 for the first three below and then Phase 1 for the last four.

    Number Date Phase
    12900 07/12/2015 UnStable
    12900 07/04/2016 UnStable
    12900 26/09/2016 UnStable
    12900 27/09/2016 Deteriorating
    12900 27/09/2016 Deteriorating
    12900 02/10/2016 Deteriorating
    12900 09/10/2016 Deteriorating


    Aaron

    Thursday, December 14, 2017 9:17 AM
  • Hi Will, almost, however this doesn't work where the Phase goes back to a phase that they had before.  Using your code above for the following example it moves the Unstables so that they are together.

    Declare @Sample Table
    	(
    	Number int,
    	CreateDate int,
    	Phase varchar(50)
    	)
    Insert Into @Sample Values
    	(12900,20151207,'UnStable'),
    	(12900,20160407,'Deteriorating'),
    	(12900,20160927,'UnStable')

    So this example I would want to see the 20151207 show as Phase 1, 20160407 as Phase 2, 20160927 as Phase 3.


    Aaron

    Thursday, December 14, 2017 9:24 AM
  • Here you go with full illustration with additional data added to denote full scenario

    Declare @Sample Table
    	(
    	Number int,
    	CreateDate int,
    	Phase varchar(50)
    	)
    Insert Into @Sample Values
    	(1916,20160219,'Deteriorating'),
    	(1916,20160513,'Deteriorating'),
    	(1916,20160605,'Deceased'),
    	(2247,20160229,'Deteriorating'),
    	(2247,20160311,'Deteriorating'),
    	(2247,20160316,'Deteriorating'),
    	(2247,20160601,'Deteriorating'),
    	(2247,20160711,'Deteriorating'),
    	(2247,20160831,'Deteriorating'),
    	(2247,20160914,'Deteriorating'),
    	(2247,20160920,'Stable'),
    	(4963,20160523,'Deteriorating'),
    	(4963,20160615,'UnStable'),
    	(4963,20160616,'UnStable'),
    	(4963,20160621,'Dying'),
    	(4963,20160622,'Dying'),
    	(4963,20160628,'Deceased'),
    	(5017,20160520,'Deteriorating'),
    	(5017,20160525,'Deteriorating'),
    	(5017,20160602,'UnStable'),
    	(5017,20160613,'Stable'),
    	(5017,20160620,'UnStable'),
    	(5017,20160625,'Stable'),
    	(5017,20160628,'Stable'),
    	(5017,20160703,'Deteriorating'),
    	(5017,20160709,'Deteriorating'),
    	(5017,20160711,'Dying'),
    	(5017,20160715,'Deceased')
    
    	SELECT s.*,DENSE_RANK() OVER (PARTITION BY Number ORDER BY LastPhaseDate) AS PhaseNo
    	FROM @Sample s
    	OUTER APPLY (
    	SELECT MAX(CreateDate) AS LastPhaseDate
    	FROM @Sample
    	WHERE Number = s.Number
    	AND CreateDate < s.CreateDate
    	AND Phase <> s.Phase)s1
    
    
    
    /*
    output
    --------------------------------------------------------
    
    Number	CreateDate	Phase	        PhaseNo
    --------------------------------------------------------
    1916	20160219	Deteriorating	1
    1916	20160513	Deteriorating	1
    1916	20160605	Deceased	2
    2247	20160229	Deteriorating	1
    2247	20160311	Deteriorating	1
    2247	20160316	Deteriorating	1
    2247	20160601	Deteriorating	1
    2247	20160711	Deteriorating	1
    2247	20160831	Deteriorating	1
    2247	20160914	Deteriorating	1
    2247	20160920	Stable	        2
    4963	20160523	Deteriorating	1
    4963	20160615	UnStable	2
    4963	20160616	UnStable	2
    4963	20160621	Dying	        3
    4963	20160622	Dying	        3
    4963	20160628	Deceased	4
    5017	20160520	Deteriorating	1
    5017	20160525	Deteriorating	1
    5017	20160602	UnStable	2
    5017	20160613	Stable	        3
    5017	20160620	UnStable	4
    5017	20160625	Stable	        5
    5017	20160628	Stable	        5
    5017	20160703	Deteriorating	6
    5017	20160709	Deteriorating	6
    5017	20160711	Dying	        7
    5017	20160715	Deceased	8
    
    
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ASalter87 Thursday, December 14, 2017 9:43 AM
    Thursday, December 14, 2017 9:25 AM
  • Visakh, thank you that is exactly what I am after.  

    Can you just run me through how this works?


    Aaron

    Thursday, December 14, 2017 9:44 AM
  • Visakh, thank you that is exactly what I am after.  

    Can you just run me through how this works?


    Aaron

    Sure

    Basically you need to identify the each running streak (group) of a phase value (for example Deteriorating, Stable etc). This i'm identifying by tracking the date at which phase changes(I'm going backward and getting the last date when phase changed which I call LastPhaseDate). Then all you need to do is to sequence the data within each group of Number (1916,2247 etc) based on this phase change date. This will ensure phase changes gets sequenced starting from first phase till last phase for each Number grouping done by using PARTITION BY function. Then you need to apply DENSE_RANK function over the group to start with 1 for first phase continuing sequentially till the last one


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 14, 2017 10:02 AM
  • I will suggest to try both solutions and compare performance. With proper indexes, the solution using LAG and running total could outperform the correlated one. 

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, December 14, 2017 1:45 PM