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, Sep 24 '20 at 15:52
Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/q/18870326/2029983) — Thom A, Sep 24 '20 at 15:53
No, I've tried that but with the conditions I have, it doesn't work for me. — JC_BI, Sep 24 '20 at 15:54
*"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, Sep 24 '20 at 15:56
Its close, the include the predecessor if there's only 1 status change on that day... such as in 101 is a bit unique. — xQbert, Sep 24 '20 at 16:13
I just added what I have tried, but still need some orientation on how to apply the condition of the status. — JC_BI, Sep 24 '20 at 16:19
That solution doesn't use `FOR XML PATH` at all. You said you'd looked at the posible duplicate I gave above? — Thom A, Sep 24 '20 at 16:23
Uh... why is 107 in your results..... that's not yesterday... — xQbert, Sep 24 '20 at 16:29
My bad it should be 9/23/2020. I'll fix it right now. Thanks — JC_BI, Sep 24 '20 at 16:31
I think I'm close. w/ my answer. but would need more sample data to test all cases. — xQbert, Sep 24 '20 at 16:38

xQbert · Accepted Answer · 2020-09-25T13:49:51.110

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 '20 at 13:49

answered Sep 24 '20 at 16:23

xQbert

34,733
2
41
62

Great, I'm doing some tests right now and also trying to remove the > at the end... – JC_BI Sep 24 '20 at 16:43
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... – xQbert Sep 24 '20 at 16:44
Yes, I want to take it off. It only should appear in between the status. – JC_BI Sep 24 '20 at 16:46
1

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 Sep 24 '20 at 16:48
Either tests are failing bad; or you've moved on already cause this works :P I'm guessing the latter because failed tests would be reported as this answer doesn't work ;P This is very similar to other FOR XML questions; the unique aspect was returning a 2nd row that was outside your criteria when only 1 row existed that matched your critiera. – xQbert Sep 24 '20 at 17:07
I'm trying to pull the previous status regardless of the number of changes the previous day for each row but I'm not able to categorize the row number the ones that have more than 1 change of the previous day. – JC_BI Sep 28 '20 at 14:59
1

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. – xQbert Sep 28 '20 at 15:17
Sure, let me modify the post. – JC_BI Sep 28 '20 at 15:25
Granted, it wouldn't be lead/lag; but their 2008 counterparts – xQbert Sep 28 '20 at 15:25
I just edited the post and included the case statement for the date I tried but still need some work. – JC_BI Sep 28 '20 at 17:32
Are you using the SQL-fiddle link? I can't access for some reason... – JC_BI Sep 28 '20 at 18:02
I've not touched the fiddle since 9/24. But i"m having trouble accessing it as well. – xQbert Sep 28 '20 at 18:21
Ok, do you know how can I accomplish these two features on the query? – JC_BI Sep 28 '20 at 18:23

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

1 Answers1