SQL Logic for time dependent role mapping

Question

Post reply

SQL Logic for time dependent role mapping

Yash90.B

SSC Enthusiast

Points: 187
More actions
February 25, 2022 at 6:42 am

#3988665
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/blank.
CLASS - 1JL is some what detail level and CLASS - 1TC is aggregated level if same role is performed with continued dates.
DRNR STDATE ENDATE CLASS ROLE
01234 20210101 20210430 1JL
01234 20210501 20211225 1JL
01234 20211226 99991231 1JL
01234 20210101 20211225 1TC JUNIOR
01234 20211226 99991231 1TC SENIOR
Expected output should look like below. How to achieve this in SQL
DRNR STDATE ENDATE CLASS ROLE
01234 20210101 20210430 1JL JUNIOR
01234 20210501 20211225 1JL JUNIOR
01234 20211226 99991231 1JL SENIOR
01234 20210101 20211225 1TC JUNIOR
01234 20211226 99991231 1TC SENIOR
- This topic was modified 3 years, 6 months ago by Yash90.B.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80381 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Based on your sample data, this works, I think. I suspect that more refinement will be required to make it work with actual data. Please post sample data in consumable format in future (as I have done).

DROP TABLE IF EXISTS #Table_1;

CREATE TABLE #Table_1
(
    DRNR CHAR(5) NOT NULL
   ,STDate DATE NOT NULL
   ,ENDate DATE NOT NULL
   ,Class CHAR(3) NOT NULL
   ,Role VARCHAR(10) NULL
);

INSERT #Table_1
(
    DRNR
   ,STDate
   ,ENDate
   ,Class
   ,Role
)
VALUES
('01234', '20210101', '20210430', '1JL', NULL)
,('01234', '20210501', '20211225', '1JL', NULL)
,('01234', '20211226', '99991231', '1JL', NULL)
,('01234', '20210101', '20211225', '1TC', 'JUNIOR')
,('01234', '20211226', '99991231', '1TC', 'SENIOR');

WITH Roles
AS (SELECT t.STDate
          ,t.ENDate
          ,t.Role
    FROM #Table_1 t
    WHERE t.Role IS NOT NULL)
SELECT t.DRNR
      ,t.STDate
      ,t.ENDate
      ,t.Class
      ,Role = ISNULL(t.Role, r.Role)
FROM #Table_1 t
    JOIN Roles r
        ON t.STDate >= r.STDate
           AND t.ENDate <= r.ENDate
ORDER BY t.Class
        ,t.STDate;