none
how to find the corrent pair of previous and next on date periods?

    Question

  • I have data recorded over the months  like the following:

    id    status    statusdate
    1    f    2013-01-01
    1    f    2013-02-01
    1    f    2013-04-01
    1    n    2013-03-01
    1    n    2013-05-01
    2    f    2013-02-01
    2    f    2013-03-01
    2    f    2013-04-01
    2    n    2013-03-01
    2    n    2013-05-01

    what is the t-sql to use to put it liked:

    id    status         statusdate    nextstatus    nextstatusdate

    1     f          2013-01-01    n        2013-03-01
    1    f        2013-04-01    n        2013-05-01
    2    f        2013-02-01    n        2013-03-01
    2    f        2013-04-01    n        2013-05-01

    sample data is:

    declare @t table
    (
    id    int
    ,status varchar(10)
    ,statusdate    date
    )


    insert into @t
    select 1, 'f', '2013-01-01'
    union
    select 1, 'f', '2013-02-01'
    union
    select 1, 'n', '2013-03-01'
    union
    select 1, 'f', '2013-04-01'
    union
    select 1, 'n', '2013-05-01'
    union
    select 2, 'f', '2013-02-01'
    union
    select 2, 'n', '2013-03-01'
    union
    select 2, 'f', '2013-04-01'
    union
    select 2, 'n', '2013-05-01'

    select * from @t order by 1,3


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, September 19, 2013 11:04 PM

Answers

  • everyone's reply has been great and helped me focus and expand the data.

    i feel really happy finding a way to do it.



    declare @s table
    (
     id    int
    ,status varchar(10)
    ,statusDate    date
    )



    insert into @s
    select 1, 'f', '2013-01-01'
    union
    select 1, 'f', '2013-02-01'
    union
    select 1, 'n', '2013-03-01'
    union
    select 1, 'f', '2013-04-01'
    union
    select 1, 'n', '2013-05-01'
    union
    select 1, 'f', '2013-06-01'
    union
    select 1, 'n', '2013-07-01'
    union
    select 2, 'f', '2013-02-01'
    union
    select 2, 'n', '2013-03-01'
    union
    select 2, 'f', '2013-04-01'
    union
    select 2, 'f', '2013-05-01'
    union
    select 2, 'f', '2013-06-01'
    union
    select 2, 'n', '2013-07-01'
    union
    select 2, 'f', '2013-08-01'
    union
    select 2, 'n', '2013-09-01'
    union
    select 3, 'f', '2013-03-01'
    union
    select 3, 'n', '2013-04-01'


    /*

    id    status    statusDate_Start    statusDate_End    status    statusDate_Start
    1    f    2013-01-01    2013-02-01    n    2013-03-01
    1    f    2013-04-01    2013-04-01    n    2013-05-01
    1    f    2013-06-01    2013-06-01    n    2013-07-01
    2    f    2013-02-01    2013-02-01    n    2013-03-01
    2    f    2013-04-01    2013-06-01    n    2013-07-01
    2    f    2013-08-01    2013-08-01    n    2013-09-01
    3    f    2013-03-01    2013-03-01    n    2013-04-01

    */

    declare @t table
    (
    id    int
    ,    status    varchar(10)
    ,    statusDate_Start    date
    ,    statusDate_End    date
    )

    insert into @t
    select id, status, min(statusDate) as statusDate_Start, max(statusDate) as statusDate_End
    from (
            select
                        t1.*
                    ,    TheGroup    =
                     (select min(statusDate)
                      from @s t2
                      where t2.id = t1.id and
                            t2.statusDate > t1.statusDate and
                            t2.status <> t1.status
                     )
            from @s t1
         ) t
    group by id, status, TheGroup
    order by id, 3

    ;with f2n as
    (

        select
                rn=row_number() over (
                                        partition    by    id
                                        order        by    statusDate_Start
                                    )
            ,    id
            ,    status
            ,    statusDate_Start
            ,    statusDate_End
        FROM
                @t
    )

    select

            t1.id
            ,    t1.status,    t1.statusDate_Start,    t1.statusDate_End

            , t2.status
            , t2.statusDate_Start
    from
                f2n    t1
            join
                f2n t2
            on    t1.id =t2.id
            and    t1.rn        =    t2.rn-1
    where
            t1.status='f'
        and t2.status='n'


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.




    • Edited by light_wt Friday, September 20, 2013 7:52 PM
    • Marked as answer by light_wt Friday, September 20, 2013 7:54 PM
    Friday, September 20, 2013 7:30 PM

All replies

  • Something like this will get you pretty close.  But, I don't understand your data.  In particular, I don't understand why {1, 'f', '2013-02-01'} should not correspond to {1, 'n', '2013-05-01'} as the next status, and likewise {2, 'f', '2013-03-01'} should not correspond to {2, 'n', '2013-05-01'}.  Instead, both {1, 'f', '2013-02-01'} and {2, 'f', '2013-03-01'} appear to be arbitrarily excluded in your example.  Apologies if I'm missing something obvious.

    ;WITH t (id, status, statusdate, [rank])
    AS (
    	SELECT id
    		,status
    		,statusdate
    		, RANK() over (partition by id, status order by statusdate)
    	FROM @t
    )
    
    SELECT t1.id
    	,t1.status
    	,t1.statusdate
    	,t2.status [nextstatus]
    	,t2.statusdate [nextstatusdate]
    FROM t t1
    JOIN t t2 ON (t1.id = t2.id AND t1.status = 'f' and t1.status <> t2.status and t1.[rank] = t2.[rank]) 
    ORDER by 1,3


    Jason

    Friday, September 20, 2013 12:10 AM
  • hi Jason, it was a nice try.

    "why {1, 'f', '2013-02-01'} should not correspond to {1, 'n', '2013-05-01'} as the next status"

    think of the date when there is a n, meaning the customer had a default on payment.  So, the following

    1     f          2013-01-01    n        2013-03-01
    1    f        2013-04-01    n        2013-05-01

    reads

    from Jan to Mar, there was a default on march

    t h e n  , from april to may, there was a default on may


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, September 20, 2013 4:05 AM

  • select  id,'f'status,
                      min(case when status='f' then statusdate end) statusdate,
     'n' nextstatus,
     min(case when status='n' then statusdate end) nextstatuisdate
    from @t t1
    group by id
    union all
    select  id,'f'status,
                      max(case when status='f' then statusdate end) fmindt,
     'n' nextstatus,
     max(case when status='n' then statusdate end) nmindt
    from @t t1
    group by id
    order by id


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance


    Friday, September 20, 2013 5:53 AM
  • very good try.  I am sorry and I should have posted a much longer sample data. 



    declare @t table
    (
    id    int
    ,status varchar(10)
    ,statusdate    date
    )


    insert into @t
    select 1, 'f', '2013-01-01'
    union
    select 1, 'f', '2013-02-01'
    union
    select 1, 'n', '2013-03-01'
    union
    select 1, 'f', '2013-04-01'
    union
    select 1, 'n', '2013-05-01'
    union
    select 1, 'f', '2013-06-01'  --< not showing up
    union
    select 1, 'n', '2013-07-01'  --< not showing up.
    union
    select 2, 'f', '2013-02-01'
    union
    select 2, 'n', '2013-03-01'
    union
    select 2, 'f', '2013-04-01'
    union
    select 2, 'n', '2013-05-01'
    union
    select 3, 'f', '2013-03-01'  --< showing dup
    union
    select 3, 'n', '2013-04-01'

    select * from @t order by 1,3

    select  
        id
        ,'f'status
        , min(case when status='f' then statusdate end) statusdate
        ,'n' nextstatus
        , min(case when status='n' then statusdate end) nextstatuisdate
    from @t t1
    group by id
    union all
    select  
        id
        ,'f'status
        , max(case when status='f' then statusdate end) fmindt
        , 'n' nextstatus
        , max(case when status='n' then statusdate end) nmindt
    from @t t1
    group by id
    order by id


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, September 20, 2013 6:43 PM
  • everyone's reply has been great and helped me focus and expand the data.

    i feel really happy finding a way to do it.



    declare @s table
    (
     id    int
    ,status varchar(10)
    ,statusDate    date
    )



    insert into @s
    select 1, 'f', '2013-01-01'
    union
    select 1, 'f', '2013-02-01'
    union
    select 1, 'n', '2013-03-01'
    union
    select 1, 'f', '2013-04-01'
    union
    select 1, 'n', '2013-05-01'
    union
    select 1, 'f', '2013-06-01'
    union
    select 1, 'n', '2013-07-01'
    union
    select 2, 'f', '2013-02-01'
    union
    select 2, 'n', '2013-03-01'
    union
    select 2, 'f', '2013-04-01'
    union
    select 2, 'f', '2013-05-01'
    union
    select 2, 'f', '2013-06-01'
    union
    select 2, 'n', '2013-07-01'
    union
    select 2, 'f', '2013-08-01'
    union
    select 2, 'n', '2013-09-01'
    union
    select 3, 'f', '2013-03-01'
    union
    select 3, 'n', '2013-04-01'


    /*

    id    status    statusDate_Start    statusDate_End    status    statusDate_Start
    1    f    2013-01-01    2013-02-01    n    2013-03-01
    1    f    2013-04-01    2013-04-01    n    2013-05-01
    1    f    2013-06-01    2013-06-01    n    2013-07-01
    2    f    2013-02-01    2013-02-01    n    2013-03-01
    2    f    2013-04-01    2013-06-01    n    2013-07-01
    2    f    2013-08-01    2013-08-01    n    2013-09-01
    3    f    2013-03-01    2013-03-01    n    2013-04-01

    */

    declare @t table
    (
    id    int
    ,    status    varchar(10)
    ,    statusDate_Start    date
    ,    statusDate_End    date
    )

    insert into @t
    select id, status, min(statusDate) as statusDate_Start, max(statusDate) as statusDate_End
    from (
            select
                        t1.*
                    ,    TheGroup    =
                     (select min(statusDate)
                      from @s t2
                      where t2.id = t1.id and
                            t2.statusDate > t1.statusDate and
                            t2.status <> t1.status
                     )
            from @s t1
         ) t
    group by id, status, TheGroup
    order by id, 3

    ;with f2n as
    (

        select
                rn=row_number() over (
                                        partition    by    id
                                        order        by    statusDate_Start
                                    )
            ,    id
            ,    status
            ,    statusDate_Start
            ,    statusDate_End
        FROM
                @t
    )

    select

            t1.id
            ,    t1.status,    t1.statusDate_Start,    t1.statusDate_End

            , t2.status
            , t2.statusDate_Start
    from
                f2n    t1
            join
                f2n t2
            on    t1.id =t2.id
            and    t1.rn        =    t2.rn-1
    where
            t1.status='f'
        and t2.status='n'


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.




    • Edited by light_wt Friday, September 20, 2013 7:52 PM
    • Marked as answer by light_wt Friday, September 20, 2013 7:54 PM
    Friday, September 20, 2013 7:30 PM