By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE Table YourTable
(GRPNBR varchar(100),CHECK_IN varchar(100),Date_of_check_in date,CHECK_IN_ORDER varchar(100))
Insert Into YourTable Values
('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)
10 rows affected
select GRPNBR,CHECK_IN as POST_CHECK_IN ,Date_of_check_in,CHECK_IN_ORDER
from YourTable
where CHECK_IN not like '_1'
| GRPNBR | POST_CHECK_IN | Date_of_check_in | CHECK_IN_ORDER |
|---|---|---|---|
| 200-001 | Y23 | 2020-03-04 | 15 |
| 200-001 | M56 | 2020-05-06 | 17 |
| 200-001 | UN | 2020-08-31 | 38 |
| 200-001 | C T28 | 2020-11-23 | 91 |
| 200-001 | C M3 | 2021-01-29 | 93 |
| 200-001 | CM9 | 2021-08-04 | 95 |
| 200-001 | CM10 | 2021-09-04 | 97 |
select GRPNBR,CHECK_IN,Date_of_check_in
from YourTable
where CHECK_IN like '_1'
| GRPNBR | CHECK_IN | Date_of_check_in |
|---|---|---|
| 200-001 | S1 | 2020-01-15 |
| 200-001 | T1 | 2020-10-12 |
| 200-001 | P1 | 2021-04-22 |
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
| GRPNBR | CHECK_IN | POST_CHECK_IN | Date_of_check_in | CHECK_IN_ORDER |
|---|---|---|---|---|
| 200-001 | S1 | Y23 | 2020-03-04 | 15 |
| 200-001 | S1 | M56 | 2020-05-06 | 17 |
| 200-001 | S1 | UN | 2020-08-31 | 38 |
| 200-001 | T1 | C T28 | 2020-11-23 | 91 |
| 200-001 | T1 | C M3 | 2021-01-29 | 93 |
| 200-001 | P1 | CM9 | 2021-08-04 | 95 |
| 200-001 | P1 | CM10 | 2021-09-04 | 97 |