LAST MAX 2 DATES

Question

Post reply

LAST MAX 2 DATES

Viewing 3 posts - 16 through 17 (of 17 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: 246695 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

kaj SSCommitted Points: 1580 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 )