Search code examples
sqlsql-serversql-server-2008for-xml-path

Retrieve multiple status into one row based on multiple conditions SQL 2008

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.

This is how the table looks like:

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

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

Working:

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

Any help would be appreciated.

Thank you!!

Solution

  • This appears to work with sample data provided. I'd need more sample data to test all possible edge cases.

    Fiddle: http://www.sqlfiddle.com/#!18/33d76/9/0 (others feel free to steal fiddle to test!)

    • First I assign a row number to each record so I can then add back in rows for those IDs which which are only 1 row deep based on your limits... (CTE) Note we order by the date descending within each ID so the newest record is listed first. That way if changes occur on same day, row 2 is still a record in the set and is eliminated in the union; but if only 1 record exists for a day, the prior newestrecord becomes row 2 and is later included in the data set via CT3.
    • CTE2 Limits the data to only those you desire to see based on rules
    • CTE3 unions in the records with only 1 row so you get prior status. This works because I don't care about the duplicates causes as union eliminates them.
    • Finally we then stuff our data from cte3 using CTE2's data set to generate the combined status
    • use a wrapper query to eliminate the extra '>' at end using a wrapper query and left.

    .

    WITH CTE AS (SELECT *, Row_number() over (PARTITION BY ID ORDER BY statusDate desc) RN FROM YourTable),
     CTE2 as (SELECT * FROM cte WHERE StatusDate >= dateadd(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

    Results in :

    ID  _Status
101 Processing > Tracking
102 Approved > Reviewing > Approved
107 Tracking > Received

    Lots of ways to skin a cat; this is just 1. (proverbial cat; don't want hate from animal lovers!)