SQL Server. Using SSMS as the SQL editor.

I have a query that gets me more than the data I need. Essentially, I just need to show the results that tell me the first-ever date on a customer's account where a fill was sent. And if the customer pauses shipments and restarts then just the very first restart fill date. I have been working on this query using two CROSS APPLYs, one CROSS APPLY and one CTE, etc.

I get close, but only on the first row. The last column on row 2 should be a date and on row 3 should be NULL as we did not send anything after the customer ended his pause date. Here is the code I have so far.

The three tables are:

CUSTOMER:

FILL_DATE_HX:

PAUSE_HX:

The Query I have so far is:

WITH restart_fill_date AS 
(
    SELECT DISTINCT
        fill.FILL_DATE,
        cus.CUST_NO,
        cus.SERIES_NO,
        ROW_NUMBER() OVER (PARTITION BY cus.CUST_NO ORDER BY cus.CUST_NO, PAUSE_HX.PAUSE_START_DATE ) RN
    FROM 
        Fills.dbo.FILL_DATE_HX fill
    LEFT JOIN 
        Fills.dbo.customer cus ON fill.CUST_NO = cus.CUST_NO
    LEFT JOIN 
        Fills.dbo.PAUSE_HX ON cus.CUST_NO = PAUSE_HX.CUST_NO
), first_fill_date AS 
(
    SELECT
        fill.FILL_DATE,
        cus.CUST_NO,
        cus.SERIES_NO,
        ROW_NUMBER() OVER (PARTITION BY cus.CUST_NO  ORDER BY cus.CUST_NO, fill.FILL_DATE) rn_2
    FROM 
        Fills.dbo.FILL_DATE_HX fill
    LEFT JOIN 
        CUSTOMER cus ON fill.CUST_NO = cus.CUST_NO
) 
SELECT DISTINCT
    cus.CUST_NO, 
    cus.SERIES_NO, 
    cus.ACCT_OPENED_DATE,
    CASE 
        WHEN cus.ACCT_OPENED_DATE <= first_fill_date.FILL_DATE 
            THEN first_fill_date.FILL_DATE 
    END FIRST_SENT_DATE,
    PAUSE_HX.PAUSE_START_DATE,  
    PAUSE_HX.PAUSE_END_DATE,
    CASE 
        WHEN PAUSE_HX.PAUSE_END_DATE <= restart_fill_date.FILL_DATE 
            THEN restart_fill_date.FILL_DATE 
    END RESTART_REPLACEMENT_DATE
FROM 
    Fills.dbo.customer cus
LEFT JOIN 
    Fills.dbo.PAUSE_HX ON cus.CUST_NO = PAUSE_HX.CUST_NO
LEFT JOIN 
    Fills.dbo.FILL_DATE_HX ON cus.CUST_NO = FILL_DATE_HX.CUST_NO
LEFT JOIN 
    first_fill_date ON cus.CUST_NO = first_fill_date.CUST_NO
LEFT JOIN 
    restart_fill_date ON cus.CUST_NO = restart_fill_date.CUST_NO
WHERE 
    rn = 1 AND rn_2 = 1 

That query returns this result (Scroll to right to see last column):

But I need to get this: (Because there should be a date on line 2 and line 3 is a true NULL (Scroll to right to see last column))

Any advice would be greatly appreciated.

CodePudding user response：

You need only one CTE but with more columns to achieve your result

The first solution uses a subqiery instead of a CTE, i think it will be faster than the CTE, but i dodn't test it with a second CTE which remove all unnecessary row_numbers biiger than 1

SELECT c.[CUST_NO], c.[SERIES_NO]
, CONVERT(date,[ACCT_OPENED]),CONVERT(date,[PAUSE_START_DATE]), CONVERT(date,[PAUSE_END_DATE])
,CONVERT(date,(SELECT TOP 1 [FILL_DATE] FROM FILL_DATE_HX 
WHERE [CUST_NO] = c.[CUST_NO] AND [SERIES_NO] = c.[SERIES_NO] AND [FILL_DATE] > p.[PAUSE_END_DATE])) as RESTART_REPLACEMENT_DATE
FROM CUSTOMER c INNER JOIN PAUSE_HX p ON c.[CUST_NO] = p.[CUST_NO] AND  c.[SERIES_NO] =  p.[SERIES_NO]
GO

CUST_NO | SERIES_NO | (No column name) | (No column name) | (No column name) | RESTART_REPLACEMENT_DATE
:------ | --------: | :--------------- | :--------------- | :--------------- | :-----------------------
W125    |      1001 | 2020-12-01       | 2021-01-07       | 2021-01-15       | 2021-01-16              
W125    |      1001 | 2020-12-01       | 2021-04-19       | 2021-04-28       | 2021-04-29              
W125    |      1001 | 2020-12-01       | 2021-06-02       | 2021-06-08       | null                    

WITH first_fill_date AS 
(
SELECT
f.[CUST_NO],f.[SERIES_NO],p.[PAUSE_END_DATE] , f.[FILL_DATE]
, ROW_NUMBER() OVER (PARTITION By f.[CUST_NO],f.[SERIES_NO],p.[PAUSE_END_DATE] ORDER BY f.[FILL_DATE] ASC) row_num
FROM FILL_DATE_HX f JOIN PAUSE_HX p 
ON f.[CUST_NO] = p.[CUST_NO] AND  f.[SERIES_NO] =  p.[SERIES_NO] AND f.[FILL_DATE] > p.[PAUSE_END_DATE]
) 
SELECT c.[CUST_NO], c.[SERIES_NO]
, CONVERT(date,[ACCT_OPENED]),CONVERT(date,[PAUSE_START_DATE]), CONVERT(date,p.[PAUSE_END_DATE])
,CONVERT(date,ff.[FILL_DATE]) as RESTART_REPLACEMENT_DATE
FROM CUSTOMER c INNER JOIN PAUSE_HX p ON c.[CUST_NO] = p.[CUST_NO] AND  c.[SERIES_NO] =  p.[SERIES_NO]
LEFT JOIN first_fill_date ff ON ff.[CUST_NO] = p.[CUST_NO] AND  ff.[SERIES_NO] =  p.[SERIES_NO] AND ff.[PAUSE_END_DATE] = p.[PAUSE_END_DATE] AND ff.row_num = 1

GO

CUST_NO | SERIES_NO | (No column name) | (No column name) | (No column name) | RESTART_REPLACEMENT_DATE
:------ | --------: | :--------------- | :--------------- | :--------------- | :-----------------------
W125    |      1001 | 2020-12-01       | 2021-01-07       | 2021-01-15       | 2021-01-16              
W125    |      1001 | 2020-12-01       | 2021-04-19       | 2021-04-28       | 2021-04-29              
W125    |      1001 | 2020-12-01       | 2021-06-02       | 2021-06-08       | null                    

db<>fiddle here