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

Question

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!!

Use the "old" FOR XML PATH method (there's literally 100's of examples of this on Stack Overflow) and then get that upgrade path finalised and implemented so you're on a version of SQL Server that isn't completely unsupported. — Thom A
– Thom A ♦, Commented Sep 24, 2020 at 15:52
Does this answer your question? Comma separated results in SQL — Thom A
– Thom A ♦, Commented Sep 24, 2020 at 15:53
"No, I've tried that" Then show us that attempt. Edit your question with the SQL you used so we can explain why it didn't work as you wanted. FOR XML PATH is clearly what you are after here, and this appears to be a duplicate if you get the filtering correct in the first place. — Thom A
– Thom A ♦, Commented Sep 24, 2020 at 15:56

xQbert · Accepted Answer · 2020-09-25 13:49:51Z

1

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!)

edited Sep 25, 2020 at 13:49

answered Sep 24, 2020 at 16:23

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

JC_BI Over a year ago

Great, I'm doing some tests right now and also trying to remove the > at the end...

xQbert Over a year ago

it can be at beginning or at end or we can just take left of length - 2 but left requires more nesting cte's I think or duplicate the call to stuff...

JC_BI Over a year ago

Yes, I want to take it off. It only should appear in between the status.

xQbert Over a year ago

Ok added outer select and used left to eliminate the last ' >' There may be an "easier" way but this follows a sequence to address it. My rules are get something to work. Laugh, then do it right... then make it better. If you sit there and do nothing, you get nothing done. So try, fail, try again and keep trying till you get it to work, then try and make it better if you have the time ;P

xQbert Over a year ago

An example would be useful. Using the sample data you have: what is the expected results? lead/lag analytics may work; but I'm not sure what you're after.

|

Collectives™ on Stack Overflow

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

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related