LAST MAX 2 DATES

Question

Post reply

LAST MAX 2 DATES

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Shree23 SSC-Addicted Points: 497 More actions · Answer 1

when i have added 2 rows and max 2 rows is giving '2021-02-19' only one record

('577', '-1', '0', '2021-02-19');

('577', '-1', '1', '2021-02-20');

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Shree23 wrote:

when i have added 2 rows and max 2 rows is giving '2021-02-19' only one record
('577', '-1', '0', '2021-02-19');
('577', '-1', '1', '2021-02-20');

For me it gives 2 rows.

As you have repeatedly refused to provide the information we need to help you resolve your issue, you will not be receiving any more assistance from me.

kaj SSCommitted Points: 1616 More actions · Answer 3

I have to agree with Phil, your replies don't make much sense, and I am also on the verge of despair about you inablility to state your requirements in a meaningful way (meaningful to anybody besides yourself, obviously).

At a guess, you want the last date and the date before that shown side-by-side?

That will still be best done by utilizing the TOP instruction. If TOP is anathema to your way of thinking, and you simply don't want to see a TOP in your code, I will also have to pass.

WITH
    MAX2 AS (
    SELECT DISTINCT TOP(2)
        LogDate
    FROM #Table_1
    ORDER BY
        LogDate DESC
    )

SELECT 
    Div_ID,
    CustomerID,
    Div_Status,
    LogDate,
    dt.m1LastLogDate ,
    dt.LastLogDate
FROM ( 
    SELECT 
        Div_ID,
        CustomerID,
        Div_Status,
        LogDate,
        LAG(Div_Status,1,0) OVER (PARTITION BY Div_ID ORDER BY logdate) AS PreviousValue,
        LEAD (Div_Status,0,1) OVER (PARTITION BY Div_ID ORDER BY logdate) as NextValue
    FROM #Table_1
    ) AS t
CROSS APPLY (
    SELECT MIN(LogDate) AS m1LastLogDate, MAX(LogDate) AS LastLogDate
    FROM MAX2
    ) dt
WHERE (PreviousValue = 0 AND Div_Status = 1 AND NextValue = 1 ) OR
(PreviousValue = 0 AND Div_Status = 0 AND NextValue = 1 ) OR
(PreviousValue = 0 AND Div_Status = 1 AND NextValue = 0 ) OR
(PreviousValue = 1 AND Div_Status = 0 AND NextValue = 0 )