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.
8,576 questions
{count} votes

Accepted answer
  1. Guoxiong 7,681 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];
    

3 additional answers

Sort by: Most helpful
  1. Guoxiong 7,681 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.


  2. MelissaMa-MSFT 24,126 Reputation points Microsoft Employee
    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.

    No comments

  3. Miamiao-MSFT 4,216 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')
    
    No comments