T-SQL - Track of task completion on time before Target

I've been stuck on this task for the past couple of days and I could really use some help. What I'm trying to achieve is to check all the CHANGE_ID that were either not completed before its TARGET date. A CHANGE_ID would have multiple tasks under it and they all should be completed for it to be flagged as COMPLETED. If any of the tasks for a CHANGE_ID is still open or were not completed before TARGET date they it should be flagged as PENDING. TASK_DELAY_CAUSED_BY should display the first TASK_ID which either has missed the target or is still open.
| CHANGE_ID | TASK_ID | CHANGE_TARGET | TASK_START | TASK_END | COMPLETED | PENDING | TASK_DELAY_CAUSED_BY |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| CRQ1 | TAS001 | 2022-12-19 | 2022-12-17 | 2022-12-17 | | | |
| CRQ1 | TAS002 | 2022-12-19 | 2022-12-17 | 2022-12-19 | | 1 | TAS002 |
| CRQ1 | TAS003 | 2022-12-19 | 2022-12-19 | 2022-12-21 | | | |
| CRQ2 | TAS001 | 2023-01-13 | 2023-01-06 | 2023-01-07 | | | |
| CRQ2 | TAS002 | 2023-01-13 | 2023-01-07 | 2023-01-08 | | | |
| CRQ2 | TAS003 | 2023-01-13 | 2023-01-08 | 2023-01-11 | 1 | | |
| CRQ3 | TAS001 | 2023-03-26 | 2023-02-06 | 2023-02-07 | | | |
| CRQ3 | TAS002 | 2023-03-26 | 2023-02-07 | 2023-02-11 | | 1 | |
| CRQ4 | TAS001 | 2023-04-02 | 2023-02-15 | 2023-02-15 | | | |
| CRQ4 | TAS002 | 2023-04-02 | 2023-02-16 | 2023-02-18 | | | |
| CRQ4 | TAS003 | 2023-04-03 | 2023-02-18 | | | 1 | TAS003 |

Every CHANGE_ID would have 3 tasks in it. If TAS003 is not present then it will be considered as pending or still not completed. For instance CRQ3 does not have TAS003 hence it is flagged as PENDING and CRQ4 is still being worked on hence it is too flagged as pending.

Do you think it would be better to track the CHANGE_ID separately? All CHANGE_ID which has a TARGET date in the past with the upcoming ones. Please advise.

Data:

CREATE TABLE CRQ_TRACKER(CHANGE_ID nvarchar(20),TASK_ID nvarchar(20),CHANGE_TARGET date,TASK_START date,TASK_END date);
INSERT INTO CRQ_TRACKER (CHANGE_ID, TASK_ID, CHANGE_TARGET, TASK_START, TASK_END)
VALUES
('CRQ1', 'TAS001', '2022-12-19', '2022-12-17', '2022-12-17'),
('CRQ1', 'TAS002', '2022-12-19', '2022-12-17', '2022-12-19'),
('CRQ1', 'TAS003', '2022-12-19', '2022-12-19', '2022-12-21'),
('CRQ2', 'TAS001', '2023-01-13', '2023-01-06', '2023-01-07'),
('CRQ2', 'TAS002', '2023-01-13', '2023-01-07', '2023-01-08'),
('CRQ2', 'TAS003', '2023-01-13', '2023-01-08', '2023-01-11'),
('CRQ3', 'TAS001', '2023-03-26', '2023-02-06', '2023-02-07'),
('CRQ3', 'TAS002', '2023-03-26', '2023-02-07', NULL),
('CRQ4', 'TAS001', '2023-04-02', '2023-02-15', '2023-02-15'),
('CRQ4', 'TAS002', '2023-04-02', '2023-02-16', '2023-02-18'),
('CRQ4', 'TAS004', '2023-04-02', '2023-01-08', NULL);

For the CHANGE_IDs in the past I'm trying something as below but can't get my head around it

select 
  CHANGE_ID
 ,TASK_ID
 ,CHANGE_TARGET
 ,TASK_START
 ,TASK_END
 ,case when (CHANGE_TARGET between TASK_START and TASK_END) 
    and 1 = row_number() over(partition by CHANGE_ID,TASK_START order by TASK_START)
    then null else 1 end COMPLETED
 ,case when (CHANGE_TARGET between TASK_START and TASK_END) 
    and 1 = row_number() over(partition by CHANGE_ID,TASK_START order by TASK_START)
    then 1 else null end PENDING
from CRQ_TRACKER
where CHANGE_TARGET < getdate();

If you don't have a lot of columns, maybe this is fine. But, yes, typically I would separate the change_id from the task_id. You would do this to limit duplicate storage of information. I'd also make the key a simpler datatype, like an auto incrementing integer, for performance.

CHANGE_ID   CHANGE_CODE   CHANGE_TARGET
1           CRQ1          2022-12-19

TASK_ID   TASK_CODE  CHANGE_ID  TASK_START  TASK_END
1         TAS001     1          2022-12-17  2022-12-17

That's extra though. Using windowed queries, the final query might look something like this:

SELECT 
  c.CHANGE_CODE
 ,t.TASK_ID
 ,c.CHANGE_TARGET
 ,t.TASK_START
 ,t.TASK_END

--If a task is ended or is incomplete, mark as pending

 ,case when (t.TASK_END > c.CHANGE_TARGET OR t.TASK_END IS NULL) 
    THEN 'Pending' ELSE 'Complete' END AS TASK_STATUS

--Over the CHANGE_ID, if there are any tasks pending, make every CHANGE_STATUS "pending"

 ,case when
    MAX(case when (t.TASK_END > c.CHANGE_TARGET OR t.TASK_END IS NULL) 
       THEN 1 ELSE 0 END) OVER (PARTITION BY c.CHANGE_ID) = 1
    then 'Pending' ELSE 'Complete' END AS c.CHANGE_STATUS,

--Over the CHANGE_ID, take the first TASK_ID, ordered by the TASK_START 
--with Complete orders blown out to the year 3000 to keep them at the 
--bottom of the sort.

    FIRST_VALUE(CASE WHEN (t.TASK_END > c.CHANGE_TARGET OR t.TASK_END IS NULL) 
 THEN t.TASK_ID END) 
 OVER ( 
    PARTITION BY c.CHANGE_ID 
    ORDER BY 
        CASE WHEN (t.TASK_END > c.CHANGE_TARGET OR t.TASK_END IS NULL) 
        THEN t.TASK_START ELSE '30000101' END) AS TASK_DELAY_CAUSED_BY  

from CHANGE_T AS c LEFT JOIN TASK_T AS t 
    ON c.CHANGE_ID = t.CHANGE_ID
where c.CHANGE_TARGET < getdate();

T-SQL - Track of task completion on time before Target

1条答案

相关问题

热门标签

最新问答