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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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