none
How to pivot dates in query results to columns based off of a another column RRS feed

  • Question

  • I have a status change history table whose data looks like this:

    

    I want a query to show me the date ranges of when the first column (PESH_ENTITY_ID) was in an 'Active' status.

    I want my results to look like this:

         PESH_ENTITY_ID      ACTIVE_START    ACTIVE_END

         56869                         NULL                      4/11/2006

         56869                         9/1/2006                6/10/2010

    NOTE:  I don't have a record when it first started, so I injected a NULL in my result set for the first ACTIVE start date.  



    Monday, December 30, 2019 5:34 PM

Answers

  • If it is necessary to imply PIVOT, then check this attempt too:

    declare @table as table
    (
    	PESH_ENTITY_ID int,
    	PESH_STATUS_CD_OLD varchar(10),
    	PESH_STATUS_CD_NEW varchar(10),
    	PESH_CHANGE_DT datetime
    )
     
    set dateformat mdy
     
    insert into @table values
    	( 568869, 'ACTIVE', 'CANCELLED', '4/11/2006 08:00:00' ),
    	( 568869, 'CANCELLED', 'ACTIVE', '9/1/2006 07:00:00' ),
    	( 568869, NULL, NULL, '6/10/2010 10:00:00' ),
    	( 568869, 'ACTIVE', 'CANCELLED', '6/10/2010 11:00:00' ),
     
    	( 900000, 'CANCELLED', 'ACTIVE', '1/1/2016 01:00:00' ),
    	( 900000, 'ACTIVE', 'CANCELLED', '1/2/2016 02:00:00' ),
    	( 900000, NULL, NULL, '1/2/2016 02:33:33' ),
    	( 900000, 'CANCELLED', 'ACTIVE', '1/3/2016 03:00:00' ),
    	( 900000, 'ACTIVE', 'CANCELLED', '1/4/2016 04:00:00' ),
    	( 900000, 'CANCELLED', 'ACTIVE', '1/5/2016 05:00:00' )
     
    select * from @table
    order by PESH_ENTITY_ID, PESH_CHANGE_DT
     
     
    select PESH_ENTITY_ID, [ACTIVE] as [ACTIVE_START], [CANCELLED] as [ACTIVE_END]
    from 
    (
        select PESH_ENTITY_ID, PESH_CHANGE_DT, [Status], 
              (ROW_NUMBER() over (partition by PESH_ENTITY_ID order by PESH_CHANGE_DT)) -
                  (case [Status] when 'CANCELLED' then 1 else 0 end) as rn
        from @table 
        unpivot
        (
    		[Status] for Col in (PESH_STATUS_CD_NEW)
        ) as u
    ) as Q1
    pivot
    (
    	min(PESH_CHANGE_DT)
    	for [Status] in ([ACTIVE], [CANCELLED])
    ) as p
    order by PESH_ENTITY_ID, ACTIVE_START



    • Edited by Viorel_MVP Wednesday, January 1, 2020 10:01 AM
    • Marked as answer by JasonDWilson77 Friday, January 3, 2020 8:14 PM
    Wednesday, January 1, 2020 9:54 AM

All replies

  • create table test ( PESH_ENTITY_ID int,
    PESH_OLD varchar(100)
    , PESH_NEW varchar(100)
    ,PESH_dt date)
    
    insert into test values
    (56869,'Active','Cancelled','2006-04-11')
    ,(56869,'Cancelled','Active','2006-09-01')
    ,(56869,null,null,'2010-06-10')
    ,(56869,'Active','Cancelled','2010-06-10')
    
     ;with mycte as (
    select PESH_ENTITY_ID
    ,PESH_OLD 
    , PESH_NEW
    ,PESH_dt
    ,SUm(case when PESH_OLD='Cancelled'
    then 1 else 0 end) Over(Partition by  PESH_ENTITY_ID order by  PESH_dt) grp
     
    from test)
    Select PESH_ENTITY_ID
    , Case when min(PESH_dt) =max(PESH_dt) then null 
    else min(PESH_dt) end ACtive_Start
    , max(PESH_dt) ACtive_End
    from mycte
    Group by PESH_ENTITY_ID, grp
     
    drop table test 

    • Proposed as answer by Lily Lii Wednesday, January 1, 2020 6:15 AM
    Monday, December 30, 2019 6:54 PM
    Moderator
  • Hi

    I hope following query help you

    create table test ( PESH_ENTITY_ID int,
    PESH_OLD varchar(100)
    , PESH_NEW varchar(100)
    ,PESH_dt date)
    
    insert into test values
    (56869,'Active','Cancelled','2006-04-11')
    ,(56869,'Cancelled','Active','2006-09-01')
    ,(56869,null,null,'2010-06-10')
    ,(56869,'Active','Cancelled','2010-06-10')
    ,(56869,'Cancelled','Active','2010-06-12')
    
    select distinct PESH_ENTITY_ID ,iif(pesh_new='Active',pesh_dt, lag(PESH_dt,1) over (  partition by pesh_entity_id order by pesh_dt ) )  active_start,
    iif(pesh_new='Cancelled',pesh_dt,lag(PESH_dt,1) over (  partition by pesh_entity_id order by pesh_dt desc) ) active_end
    
    from test where PESH_NEW is not null 
     
    drop table test 

    • Proposed as answer by Lily Lii Wednesday, January 1, 2020 6:15 AM
    Monday, December 30, 2019 9:58 PM
  • Hi JasonDWilson77,

    --please try this
    create table #mytable ( 
    PESH_ENTITY_ID int,
    PESH_STATUS_CD_OLD varchar(100),
    PESH_STATUS_CD_NEW varchar(100),
    PESH_CHANGE_DT date)
    
    insert into #mytable values
    (56869,'ACTIVE','CANCELLED','2006-04-11'),
    (56869,'CANCELLED','ACTIVE','2006-09-01'),
    (56869,null,null,'2010-06-10'),
    (56869,'ACTIVE','CANCELLED','2010-06-10')
    
    
    with cte1 as(
    select PESH_ENTITY_ID,
           case when PESH_STATUS_CD_NEW='ACTIVE' 
           then PESH_CHANGE_DT
           else null
           end as ACTIVE_START,
           case when PESH_STATUS_CD_NEW='CANCELLED'
           then PESH_CHANGE_DT 
           else null
           end as ACTIVE_END
    from #mytable)
    ,cte2 as( 
    select *,row_number()over(partition by PESH_ENTITY_ID order by coalesce(ACTIVE_START, ACTIVE_END))
           + case when ACTIVE_END is null then 1 else 0 end as rn
    from cte1
    where ACTIVE_END is not null 
    or ACTIVE_START is not null)
    select PESH_ENTITY_ID,min(ACTIVE_START) ACTIVE_START,max(ACTIVE_END) ACTIVE_END
    from cte2
    group by PESH_ENTITY_ID,rn
    order by PESH_ENTITY_ID

    Best Regards,

    Lily


    • Edited by Lily Lii Tuesday, December 31, 2019 6:31 AM
    • Proposed as answer by Lily Lii Wednesday, January 1, 2020 6:14 AM
    Tuesday, December 31, 2019 3:44 AM
  • Hi JasonDWilson77,

    Do the answers above help you? It's so kind of you to mark helpful replies as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Lily


    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

    Wednesday, January 1, 2020 6:14 AM
  • If it is necessary to imply PIVOT, then check this attempt too:

    declare @table as table
    (
    	PESH_ENTITY_ID int,
    	PESH_STATUS_CD_OLD varchar(10),
    	PESH_STATUS_CD_NEW varchar(10),
    	PESH_CHANGE_DT datetime
    )
     
    set dateformat mdy
     
    insert into @table values
    	( 568869, 'ACTIVE', 'CANCELLED', '4/11/2006 08:00:00' ),
    	( 568869, 'CANCELLED', 'ACTIVE', '9/1/2006 07:00:00' ),
    	( 568869, NULL, NULL, '6/10/2010 10:00:00' ),
    	( 568869, 'ACTIVE', 'CANCELLED', '6/10/2010 11:00:00' ),
     
    	( 900000, 'CANCELLED', 'ACTIVE', '1/1/2016 01:00:00' ),
    	( 900000, 'ACTIVE', 'CANCELLED', '1/2/2016 02:00:00' ),
    	( 900000, NULL, NULL, '1/2/2016 02:33:33' ),
    	( 900000, 'CANCELLED', 'ACTIVE', '1/3/2016 03:00:00' ),
    	( 900000, 'ACTIVE', 'CANCELLED', '1/4/2016 04:00:00' ),
    	( 900000, 'CANCELLED', 'ACTIVE', '1/5/2016 05:00:00' )
     
    select * from @table
    order by PESH_ENTITY_ID, PESH_CHANGE_DT
     
     
    select PESH_ENTITY_ID, [ACTIVE] as [ACTIVE_START], [CANCELLED] as [ACTIVE_END]
    from 
    (
        select PESH_ENTITY_ID, PESH_CHANGE_DT, [Status], 
              (ROW_NUMBER() over (partition by PESH_ENTITY_ID order by PESH_CHANGE_DT)) -
                  (case [Status] when 'CANCELLED' then 1 else 0 end) as rn
        from @table 
        unpivot
        (
    		[Status] for Col in (PESH_STATUS_CD_NEW)
        ) as u
    ) as Q1
    pivot
    (
    	min(PESH_CHANGE_DT)
    	for [Status] in ([ACTIVE], [CANCELLED])
    ) as p
    order by PESH_ENTITY_ID, ACTIVE_START



    • Edited by Viorel_MVP Wednesday, January 1, 2020 10:01 AM
    • Marked as answer by JasonDWilson77 Friday, January 3, 2020 8:14 PM
    Wednesday, January 1, 2020 9:54 AM
  • >> I have a status change history table whose data looks like this: <<

    why did you fail to post DDL and went in for pictures instead? Why don't you know the only display format for DATE allowed in ANSI/ISO standard SQL is "yyyy-mm-dd" ? Why did you pick an ambiguous date format? Does "0/1/2006" mean "2006-01-06" or  2006-06-01"? 

    We now have to guess at keys and constraints and redo what you posted. Here is my attempt at a basic skeleton:

    CREATE TABLE Pesh_History
    (pesh_id CHAR(6) NOT NULL
      CHECK(pesh_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
     pesh_status VARCHAR(10) NOT NULL 
       CHECK ('active', 'inactive', 'cancelled'),
     pesh_status_start_date DATE NOT NULL,
     pesh_status_end_date DATE,
    CHECK (pesh_status_start_date <= pesh_status_end_date), 
      etc);

    There are actually some more constraints and tricks to prevent gaps in the timeline. But let's get the basics out of the way first. The word "status" comes from "state of being" in Latin, which means it has a duration. The date is by its nature and attribute value, not an attribute as you have modeled it.

    You are computing what you should be recording because of a bad design. You can download a copy of temporal queries in SQL by Rick Snodgrass from the University of Arizona website for free in a PDF. It's still a classic text for SQL programmers. Unfortunately, what you've decided to do is violate the ISO half open interval model of time. This means that the start of an interval should be known. This model is why there is no such thing as 24:00:00 Hrs in a day; it becomes 00:00:00 Hrs of the following day. DB2, among other SQL databases, will accept it and do this conversion for you. An all in the ending date is essentially a marker for "eternity", and says that the status is still ongoing.

    >> I want a query to show me the date ranges of when the first column pesh_id) was in an 'Active' status. <<

    If you use the correct relational design, you won't have to keep doing kludges like this everywhere.

    As an aside, please quit using uppercase letters for data element names. We're not using punch cards anymore and we can actually have a readable code. Please learn the difference between a row and a record in SQL; it's very important. I also have no idea what a "PESH" is; can you tell me? I would also think that you need an inactive status, so that you have no temporal gaps.. But we don't have specs so I'm just guessing.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, January 1, 2020 6:47 PM
  • Please refrain from commenting on my posts in the future @Celko.
    Friday, January 3, 2020 3:56 PM
  • I appreciate this but it had issues in more complex data.
    Friday, January 3, 2020 8:14 PM
  • This worked for me.  Thank you very much for your help!
    Friday, January 3, 2020 8:14 PM
  • Please refrain from commenting on my posts in the future @Celko.

    If only this could help🙄


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, January 4, 2020 9:18 PM
    Moderator