I have to identify the records with check_in category of S1, T1 and P1 in the check-in-order and pivot the rest of the data until we hit the next category. I was able to mark the category but was unsuccessful to pickup the min and max dates of each category as the check_in values are different for every row.

Here is the sample data and the expected results. Appreciate your help.

Source:             
GRPNBR      CHECK_IN    Date_of_check_in    CHECK_IN_ORDER  
200-001     S1          1/15/2020           3   
200-001     Y23         3/4/2020            15  
200-001     M56         5/6/2020            17  
200-001     UN          8/31/2020           38  
200-001     T1          10/12/2020          78  
200-001     C T28       11/23/2020          91  
200-001     C M3        1/29/2021           93  
200-001     P1          4/22/2021           94  
200-001     CM9         8/4/2021            95  
200-001     CM10        9/4/2021            97  
                
Expected Result:                
GRPNBR  CHECK_IN    POST_CHECK_IN   Date_of_check_in    CHECK_IN_ORDER
200-001 S1          Y23             3/4/2020            15
200-001 S1          M56             5/6/2020            17
200-001 S1          UN              8/31/2020           38
200-001 T1          C T28           11/23/2020          91
200-001 T1          C M3            1/29/2021           93
200-001 P1          CM9             8/4/2021            95
200-001 P1          CM10            9/4/2021            97

CodePudding user response：

Here is one approach that utilizes the window functions to flag and sequence data and then a simple aggregation for the final results

Example or

select GRPNBR,CHECK_IN,Date_of_check_in
from YourTable
where CHECK_IN like   '_1'

select GRPNBR,CHECK_IN as POST_CHECK_IN   ,Date_of_check_in,CHECK_IN_ORDER
from YourTable
where CHECK_IN not like   '_1'

SELECT A.grpnbr,
       CASE
         WHEN date_of_check_in > s1
              AND date_of_check_in < t1 THEN 'S1'
         WHEN date_of_check_in > t1
              AND date_of_check_in < p1 THEN 'T1'
         WHEN date_of_check_in > p1 THEN 'P1'
       END AS CHECK_IN,
       post_check_in,
       date_of_check_in,
       check_in_order
FROM   (SELECT grpnbr,
               check_in AS POST_CHECK_IN,
               date_of_check_in,
               check_in_order
        FROM   yourtable
        WHERE  check_in NOT LIKE '_1') A
       JOIN (SELECT *
             FROM   (SELECT grpnbr,
                            check_in,
                            date_of_check_in
                     FROM   yourtable
                     WHERE  check_in LIKE '_1') src
                    PIVOT ( Max(date_of_check_in)
                          FOR check_in IN ([S1],
                                           [T1],
                                           [P1]) ) piv) B
         ON A.grpnbr = B.grpnbr