SQL Logic for time dependent role mapping

Question

I have a Time dependent Master data table – DRNUMBER

Recently with new CLASS – ‘1TC’, ECC folks introduced Role information. Earlier with CLASS - ‘1JL’ role information will not be available.

CLASS - 1JL is some what detail level and CLASS - 1TC is aggregated level if same role is performed with continued dates.

Expected output should look like below. How to achieve this in SQL

Answer 1

Hi @Yash90.B
Please check this:

DECLARE @JUN_STDATE NVARCHAR(8),@JUN_ENDDATE NVARCHAR(8),@SEN_STDATE NVARCHAR(8),@SEN_ENDDATE NVARCHAR(8)  
SELECT @JUN_STDATE = STDATE FROM #DRNUMBER WHERE CLASS='1TC' AND ROLE='JUNIOR';  
SELECT @JUN_ENDDATE = ENDDATE FROM #DRNUMBER WHERE CLASS='1TC' AND ROLE='JUNIOR';  
SELECT @SEN_STDATE = STDATE FROM #DRNUMBER WHERE CLASS='1TC' AND ROLE='SENIOR';  
SELECT @SEN_ENDDATE = ENDDATE FROM #DRNUMBER WHERE CLASS='1TC' AND ROLE='SENIOR';  
  
UPDATE #DRNUMBER   
SET ROLE = CASE WHEN "STDATE" BETWEEN @JUN_STDATE AND @JUN_ENDDATE AND "ENDDATE" BETWEEN @JUN_STDATE AND @JUN_ENDDATE THEN 'JUNIOR'     
                WHEN "STDATE" BETWEEN @SEN_STDATE AND @SEN_ENDDATE AND "ENDDATE" BETWEEN @SEN_STDATE AND @SEN_ENDDATE THEN 'SENIOR'       
				END  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

DROP TABLE IF EXISTS #DRNUMBER;
CREATE TABLE #DRNUMBER
 (
 DRNR NVARCHAR(5)
 , CLASS NVARCHAR(3)
 , ENDDATE DATE
 , STDATE DATE
 , ROLE NVARCHAR(241)
 );


INSERT INTO
 #DRNUMBER (DRNR, STDATE, ENDDATE, CLASS, ROLE)
VALUES
 ('01234', '20210101', '20210430', '1JL', '');
INSERT INTO
 #DRNUMBER (DRNR, STDATE, ENDDATE, CLASS, ROLE)
VALUES
 ('01234', '20210501', '20211225', '1JL', '');
INSERT INTO
 #DRNUMBER (DRNR, STDATE, ENDDATE, CLASS, ROLE)
VALUES
 ('01234', '20211226', '99991231', '1JL', '');
INSERT INTO
 #DRNUMBER (DRNR, STDATE, ENDDATE, CLASS, ROLE)
VALUES
 ('01234', '20210101', '20211225', '1TC', 'JUNIOR');
INSERT INTO
 #DRNUMBER (DRNR, STDATE, ENDDATE, CLASS, ROLE)
VALUES
 ('01234', '20211226', '99991231', '1TC', 'SENIOR');

SELECT *  FROM #DRNUMBER;

SELECT
 c1.DRNR
 , c1.STDATE
 , c1.ENDDATE
 , c1.CLASS
 , c2.ROLE
FROM
 #DRNUMBER c1
 CROSS APPLY
(
 SELECT TOP (1)
 *
 FROM
 #DRNUMBER c2
 WHERE
 c1.STDATE <= c2.ENDDATE
 AND c1.ENDDATE >= c2.STDATE
 AND c2.CLASS = '1TC'
 ORDER BY
 c2.STDATE
 , c2.ENDDATE
) c2 
WHERE c1.CLASS = '1JL'
UNION ALL
SELECT c1.DRNR
 , c1.STDATE
 , c1.ENDDATE
 , c1.CLASS
 , c1.ROLE
FROM #DRNUMBER  c1
where c1.class = '1TC'
ORDER BY class, STDATE, ENDDATE