SQL query to match last instance of date

Question

We have a system which, for each client, stores details about a plan of action that was agreed on a certain date, and which may be entered onto the system on or after that date. Each plan is valid from the date it was agreed until the date that the next plan is agreed. A client can have more than one plan on the same day.

The data is stored in an SQL database and looks something like this (assuming a single client):

PlanID  ClientID    PlanDate        EntryDateTime           (other fields)
1       1           2019-01-01      2019-01-01 12:53:01     ABC...
2       1           2019-01-11      2019-01-12 09:18:23     DEF...
3       1           2019-01-21      2019-01-21 14:02:11     GHI...
4       1           2019-01-21      2019-01-22 08:32:58     JKL...
5       1           2019-01-31      2019-02-01 11:47:18     MNO...

We need to run reports on the contents of the action plan over time. For the purposes of a report, we need to know both the start and end date of each given plan and where multiple plans were made on the same day then we only care about the final instance of that plan.

Given the above, we want output that looks something like this:

PlanID  ClientID    StartDate       EndDate         (other fields)
1       1           2019-01-01      2019-01-11      ABC...
2       1           2019-01-11      2019-01-21      DEF...
4       1           2019-01-21      2019-01-31      JKL...
5       1           2019-01-31      NULL            MNO...

However, the nearest I have got to is the following query:

SELECT 
    OuterTable.*,

    (SELECT 
        MIN(PlanDate) AS PlanDate 
        FROM tblPlans
        WHERE
            ClientID = OuterTable.ClientID
            AND PlanDate > OuterTable.PlanDate
    ) AS PlanEndDate,

FROM
    tblPlans AS OuterTable;

This gives the following result:

PlanID  ClientID    PlanDate        PlanEndDate     (other fields)
1       1           2019-01-01      2019-01-11      ABC...
2       1           2019-01-11      2019-01-21      DEF...
3       1           2019-01-21      2019-01-31      GHI...
4       1           2019-01-21      2019-01-31      JKL...
5       1           2019-01-31      NULL            MNO...

In other words, the row data is correct but the results set includes both of the records for 21st January.

How can I adapt the query so that only the most recently entered record for a given PlanDate is included.

Ideally any solution would use generic (portable) SQL, but for the practical purposes of the project I need something that will work on both MySQL (v5.6 and above) and MS SQL Server (2014 and above).

Answer 1

try this one:

with

filtered as (
    select
        ClientID,
        PlanDate,
        max(EntryDateTime) as max_entry_datetime
    from
        tblPlans
    group by
        ClientID,
        PlanDate
)

SELECT
    p.PlanID,
    p.ClientID,
    p.PlanDate as StartDate,
    min(f2.PlanDate) as EndDate
FROM
    tblPlans as p
    join filtered as f on   p.ClientID = f.ClientID
                            and p.PlanDate = f.PlanDate
                            and p.EntryDateTime = f.max_entry_datetime
    left join filtered as f2 on p.ClientID = f2.ClientID
                                and p.PlanDate < f2.PlanDate
group by
    p.PlanID,
    p.ClientID,
    p.PlanDate

or with window functions (if you can use them):

SELECT
    p.PlanID,
    p.ClientID,
    p.PlanDate as StartDate,
    lead(p.PlanDate) over(  partition by
                                p.ClientID
                            order by
                                p.PlanDate) as EndDate
FROM
    (   select
            *,
            row_number() over(  partition by
                                    ClientID,
                                    PlanDate
                                order by
                                    EntryDateTime desc) as rn
        from
            tblPlans) as p
where
    rn = 1

for MySQL 5 I think this one will work:

SELECT
    p.PlanID,
    p.ClientID,
    p.PlanDate as StartDate,
    min(f2.PlanDate) as EndDate
FROM
    tblPlans as p
    join (  select
                ClientID,
                PlanDate,
                max(EntryDateTime) as max_entry_datetime
            from
                tblPlans
            group by
                ClientID,
                PlanDate) as f on   p.ClientID = f.ClientID
                                    and p.PlanDate = f.PlanDate
                                    and p.EntryDateTime = f.max_entry_datetime
    left join ( select
                    ClientID,
                    PlanDate,
                    max(EntryDateTime) as max_entry_datetime
                from
                    tblPlans
                group by
                    ClientID,
                    PlanDate) as f2 on  p.ClientID = f2.ClientID
                                        and p.PlanDate < f2.PlanDate
group by
    p.PlanID,
    p.ClientID,
    p.PlanDate

Answer 2

I've been fiddling around, and the following seems to work, as far as I can tell. It is the same query as in my original post, apart from the additional RIGHT JOIN.

SELECT
    OuterTable.*,

    (SELECT
        MIN(PlanDate) AS PlanDate
        FROM tblPlans
        WHERE
            ClientID = OuterTable.ClientID
            AND PlanDate > OuterTable.PlanDate
    ) AS PlanEndDate,

FROM
    tblPlans AS OuterTable
    RIGHT JOIN
        (SELECT ClientID, PlanDate, MAX(EntryDateTime) AS EntryDate
         FROM tblPlans
         GROUP BY ClientID, PlanDate
        ) AS PlanFilter
    ON OuterTable.ClientID = PlanFilter.ClientID
    AND OuterTable.PlanDate = PlanFilter.PlanDate
    AND OuterTable.EntryDateTime = PlanFilter.EntryDate;

It's not clear to me how this compares to the other answers so I would welcome some feedback in the comments to help me decide which answer to accept.

Answer 3

You eem to want:

select p.*,
       lead(planDate) over (partition by clientId) as endDate
from (select p.*,
             row_number() over (partition by clientId, planDate order by planId desc) as seqnum
      from tblplans p
     ) p
where seqnum = 1;

Answer 4

You need to use the LEAD() function which is a built in SQL Server function.

try the below

SELECT 
PlanID  
,ClientID    
,PlanDate
,LEAD(PlanDate,1) OVER ( PARTITION BY ClientID  ORDER BY PlanID )  PlanEndDate     
FROM TableName