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

JC_BI 21 Reputation points
2020-09-28T19:24:54.687+00:00

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: 

CREATE TABLE YourTable
(
  ID INT, _Status VARCHAR(80), StatusDate DATETIME 
)
GO
INSERT INTO YourTable
(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'),
(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'),
(104, 'Approved', '6/20/2020 12:19:17'),
(104, 'Sent', '7/20/2020 12:16:17'),
(104, 'Testing', '9/22/2020 17:46:16'),
(105, 'Sent', '9/21/2020 5:46:51'),
(105, NULL, '9/21/2019 9:53:53'),
(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')


Desired output:
If Runs today (Monday Sept 28)
Output: 101| Processing > Tracking > Approved > Received > Closed

IF Runs last Friday (Sept 25)
Output: 102| Declined > Reviewing >  Testing > Approved

IF Runs Wedns (Sept 23)
Output: 104| Sent > Testing

IF Runs Tues (Sept 22)
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)
Output: 107| 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

What I've tried so far: 

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,689 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Guoxiong 8,126 Reputation points
    2020-09-29T00:04:31.883+00:00

    Hope this is what you want:

    DECLARE @startDate datetime;
DECLARE @endDate datetime;
IF DATEPART(weekday, GETDATE()) = 1
BEGIN
    SET @startDate = CONVERT(date, DATEADD(day, -3, GETDATE()));
END
ELSE IF DATEPART(weekday, GETDATE()) = 7
BEGIN
    SET @startDate = CONVERT(date, DATEADD(day, -2, GETDATE()));
END
ELSE 
BEGIN
    SET @startDate = CONVERT(date, DATEADD(day, -1, GETDATE()));
END
SET @endDate = CONVERT(date, GETDATE());

;WITH CTE_01 AS (
    SELECT [ID], [_Status], [StatusDate]
    FROM [dbo].[YourTable] 
    WHERE [StatusDate] >= @startDate AND [StatusDate] < @endDate 
      AND [_Status] IN ('Tracking', 'Approved', 'Reviewing', 'Received', 'Testing', 'Closed')
),
CTE_02 AS (
    SELECT [ID], [_Status], [StatusDate]
    FROM (
        SELECT [ID], [_Status], [StatusDate], ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [StatusDate] DESC) AS Ranked
        FROM [dbo].[YourTable] 
        WHERE [StatusDate] < @startDate 
          AND [ID] IN (SELECT [ID] FROM CTE_01)
          --AND [_Status] IN ('Processing', 'Tracking', 'Approved', 'Reviewing', 'Received', 'Testing', 'Closed')
          AND [_Status] IS NOT NULL
    ) AS t
    WHERE t.Ranked = 1
),
CTE_03 AS (
    SELECT [ID], [_Status], [StatusDate]
    FROM CTE_01
    UNION ALL 
    SELECT [ID], [_Status], [StatusDate]
    FROM CTE_02
)

SELECT 
    t.[ID], 
    STUFF(
        (
            SELECT ',' + [_Status] 
            FROM [CTE_03] 
            WHERE [ID] = t.[ID]
            ORDER BY [StatusDate]
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1,
        1,
        ''
    ) AS StatusNames
FROM [CTE_03] AS t
GROUP BY t.[ID];
    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 Reputation points
    2020-09-28T20:50:17.117+00:00

    I think the main task for your query is to know which date is the starting date and which date is the ending date in the WHERE clause. See this:

    DECLARE @startDate datetime;
DECLARE @endDate datetime;
IF DATEPART(weekday, GETDATE()) = 2
BEGIN
    SET @startDate = CONVERT(date, DATEADD(day, -3, GETDATE()));
END
ELSE IF DATEPART(weekday, GETDATE()) = 1
BEGIN
    SET @startDate = CONVERT(date, DATEADD(day, -2, GETDATE()));
END
ELSE 
BEGIN
    SET @startDate = CONVERT(date, DATEADD(day, -1, GETDATE()));
END
SET @endDate = CONVERT(date, GETDATE());

SELECT * FROM [dbo].[YourTable] 
WHERE [StatusDate] >= @startDate AND [StatusDate] < @endDate;

    Note that on my system Sunday is the first day of the week. You can check it using SELECT @@DATEFIRST.

    1 person found this answer helpful.
  2. MelissaMa-MSFT 24,176 Reputation points
    2020-09-29T02:09:03.67+00:00

    Hi @JC_BI ,

    I made some small modifications based on Guoxiong's query.

    Please also refer below and check whether it is helpful to you.

    Since today is 2020-09-29 from my side, you could replace @enddate with GETDATE() if necessary. 

    DECLARE @startDate datetime;  
 DECLARE @endDate datetime=GETDATE()-1  ---'2020-09-28'  
  
SELECT  @startDate=DATEADD(DAY, CASE DATENAME(WEEKDAY, @endDate)   
                        WHEN 'Sunday' THEN -2   
                        WHEN 'Monday' THEN -3   
                        ELSE -1 END, DATEDIFF(DAY, 0, @endDate))  
  
;WITH CTE_01 AS (  
     SELECT [ID], [_Status], [StatusDate]  
     FROM [dbo].YourTable   
     WHERE [StatusDate] >= @startDate AND [StatusDate] < @endDate   
       AND [_Status] IN ('Tracking', 'Approved', 'Reviewing', 'Received', 'Testing', 'Closed')  
 )  
 ,  
 CTE_02 AS (  
     SELECT [ID], [_Status], [StatusDate]  
     FROM (  
         SELECT [ID], [_Status], [StatusDate], ROW_NUMBER() OVER(PARTITION BY [ID] ORDER BY [StatusDate] DESC) AS Ranked  
         FROM [dbo].[YourTable]   
         WHERE [StatusDate] < @startDate   
           AND [ID] IN (SELECT [ID] FROM CTE_01)  
           AND [_Status] IS NOT NULL  
     ) AS t  
     WHERE t.Ranked = 1  
 )  
  
 ,  
 CTE_03 AS (  
     SELECT [ID], [_Status], [StatusDate]  
     FROM CTE_01  
     UNION ALL   
     SELECT [ID], [_Status], [StatusDate]  
     FROM CTE_02  
 )  
      
 SELECT   
     t.[ID],   
     STUFF(  
         (  
             SELECT ' > ' + [_Status]   
             FROM [CTE_03]   
             WHERE [ID] = t.[ID]  
             ORDER BY [StatusDate]  
             FOR XML PATH(''), TYPE  
         ).value('.', 'NVARCHAR(MAX)'),  
         2,  
         1,  
         ''  
     ) AS StatusNames  
 FROM [CTE_03] AS t  
 GROUP BY t.[ID];

    In your actual situation, you could use below: 

     DECLARE @startDate datetime;  
 DECLARE @endDate datetime;  
  
SET  @startDate=DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE())   
                        WHEN 'Sunday' THEN -2   
                        WHEN 'Monday' THEN -3   
                        ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))  
  
SET @endDate = CONVERT(date, GETDATE());

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  3. m 4,271 Reputation points
    2020-09-29T02:33:34.997+00:00

    Missing 'values'? 

     INSERT INTO YourTable Values
     (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'),
     (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'),
     (104, 'Approved', '6/20/2020 12:19:17'),
     (104, 'Sent', '7/20/2020 12:16:17'),
     (104, 'Testing', '9/22/2020 17:46:16'),
     (105, 'Sent', '9/21/2020 5:46:51'),
     (105, NULL, '9/21/2019 9:53:53'),
     (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')
    1 person found this answer helpful.
    0 comments