I need to retrieve multiple statuses into one row based on the closest status before + previous day if the day of the week in(Tuesday, Wednesday, Thursday, Friday, and Saturday), if the query runs on Monday it should include the closest status before + data from(Friday, Saturday, and Sunday) and if the query runs on Sunday it should retrieve the closest status before + data from (Friday and Saturday) and also should include the previous status regardless of the status not on the list of the previous day. Using status date, based on specific statuses and grouped by ID.
Status category list: (Tracking, Approved, Reviewing, Received, Testing, Closed)
Conditions:
1- If the query runs on (Tuesday, Wednesday, Thursday, Friday, and Saturday) Then it should retrieve the Closest status before the previous business day Status from the previous day.
2- If the query runs on Monday same condition applies but it should include data from Closest status before > Friday > Saturday > Sunday.
3-If query runs on Sunday same condition applies but it should include data from Closest status before > Friday > Saturday
The query I'm working on:
WITH CTE AS (SELECT *, Row_number() over (PARTITION BY ID ORDER BY statusDate desc) RN FROM YourTable),
CTE2 as (SELECT * FROM cte WHERE StatusDate >= (CASE WHEN DATENAME(DW,StatusDate) = 'Monday' THEN dateadd(DD, -1, cast(getdate() as date) + dateadd(DD, -2, cast(getdate() as date) + dateadd(DD, -3, cast(getdate() as date) WHEN DATENAME(DW,StatusDate) = 'Sunday' THEN dateadd(DD, -1, cast(getdate() as date) + dateadd(DD, -2, cast(getdate() as date) ELSE ateadd(DD, -1, cast(getdate() as date)) and _Status in ('Tracking', 'Approved', 'Reviewing', 'Received', 'Testing', 'Closed')), CTE3 as (SELECT * FROM CTE2 UNION SELECT * FROM CTE where RN = 2 and ID in (SELECT ID FROM CTE2)) SELECT ID, left(_Status, len(_Status)-2) as _Status FROM (SELECT Distinct t1.ID,
STUFF((SELECT t2._Status + ' > '
FROM CTE3 t2
WHERE t1.ID = t2.ID
ORDER BY StatusDate
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,0,'') _Status from CTE2 t1) Z
This is how the table looks like:
CREATE TABLE YourTable ( ID INT, _Status VARCHAR(80), StatusDate DATETIME ) If Runs today (Monday Sept 28) (101, 'Submitted','07/31/2020 13:35:41'), (101, 'Processing','07/31/2020 15:19:23'), (101, 'Tracking', '9/26/2020 9:18:48'), (101, 'Approved', '9/26/2020 10:16:48'), (101, 'Received', '9/27/2020 8:16:48'), (101, 'Closed', '9/27/2020 9:16:48'), Output: Processing > Tracking > Approved > Received > Closed IF Runs Friday (Sept 25) (102, 'Complete', '8/10/2020 9:16:41'), (102, 'Declined', '8/13/2020 9:18:48'), (102, 'Reviewing','9/24/2020 17:59:13'), (102, 'Testing', '9/24/2020 19:16:05'), (102, 'Approved', '9/24/2020 21:42:56'), Output: Declined > Reviewing > Testing > Approved IF Runs Wedns (Sept 23) (104, 'Approved', '6/20/2020 12:19:17'), (104, 'Sent', '7/20/2020 12:16:17'), (104, 'Testing', '9/22/2020 17:46:16'), Output: Sent > Testing IF Runs Tues (Sept 22) (105, 'Sent', '9/21/2020 5:46:51'), (105, NULL, '9/21/2019 9:53:53'), Output: This row should not come up in the report since the previous status is not listed in the category list. IF Runs Yesterday (Sunday Sept 27) (107, 'Approved', '9/10/2019 9:53:54'), (107, 'Reviewing', '9/20/2019 9:53:54'), (107, 'Started', '9/23/2019 9:53:54'), (107, 'Approved', '9/25/2020 9:40:54'), (107, 'Reviewing', '9/25/2020 10:50:54'), (107, 'Received', '9/25/2020 10:53:54'), (107, 'Tracking', '9/26/2020 9:58:54'), (107, 'Received', '9/26/2020 10:53:54') Output: Started > Approved > Reviewing > Received > Tracking >Received
