SQL Database Date Ranges

Question

I have a flat table which holds status updates.

These updates are stored in the following format:

AgreementID | StatusID | StatusDate

Source Data:

AgreementID StatusID    StatusDate
109         1           14/01/2013 15:00:33
109         2           14/01/2013 15:01:28
109         2           14/01/2013 15:01:28
109         2           14/01/2013 15:02:42
109         2           26/02/2013 16:27:38
109         2           26/02/2013 16:27:45
109         8           19/02/2013 13:57:33
109         8           04/03/2013 16:46:29
109         8           18/03/2013 14:08:12
109         8           18/03/2013 14:47:00
109         8           18/03/2013 14:48:46
109         9           26/03/2013 15:41:51

What I am needing is to map an Agreement Status in Date Ranges, An agreement can have multiple status updates of the same StatusID, but once the agreement goes onto the next StatusID it cannot step backwards to the previous Status ID.

For the last Status the date range should be StatusDate To Date.

I have got the following piece of code, but the results are not giving me what I want...

SELECT     
    AgreementID, 
    CONVERT(datetime, CONVERT(varchar(10), StatusDate, 103), 103) AS StatusDate, 
    CONVERT(datetime, CONVERT(varchar(10), StatusDate, 103), 103) AS DateFrom, 
    CASE WHEN DateTo IS NULL THEN CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 103), 103) ELSE CONVERT(datetime, CONVERT(varchar(10), DateTo, 103), 103) END AS DateTo, 
    StatusID
FROM         
    (
     SELECT     
        AgreementID, 
        StatusID, 
        StatusDate, 
        (SELECT TOP (1) StatusDate FROM TblStatusUpdates AS SU WHERE SU.AgreementID = U.AgreementID AND SU.StatusDate > U.StatusDate ORDER BY StatusID, StatusDate ASC) DateTo, 
        RN = ROW_NUMBER() OVER (Partition BY AgreementID ORDER BY StatusDate)
     FROM         
        (
         SELECT     
            AgreementID, 
            StatusID, 
            MIN(StatusDate) AS StatusDate
         FROM          
            TblStatusUpdates
         GROUP BY 
            AgreementID, StatusID
        ) AS U
    ) AS A

Here is an example result from this query:

AgreementID StatusDate  DateFrom    DateTo      StatusID
109         14/01/2013  14/01/2013  14/01/2013  1
109         14/01/2013  14/01/2013  14/01/2013  2
109         19/02/2013  19/02/2013  26/02/2013  8
109         26/03/2013  26/03/2013  25/04/2013  9

As you can see, the Date To Value is not right, it should always run to the day before the next status ID.

So in this example, then Status 2 Should run from 19/02/2013 TO 18/02/13

Any suggestions would be great. Thank you.

Just a quick comment: [STOP DECLARING VARCHAR WITHOUT LENGTH](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx). It's lazy and problematic. — Aaron Bertrand, Apr 25 '13 at 15:52
Sorry Conrad, wrong date, Status 2 should run from 19/02/2013 TO 18/02/13 - I will update the post — Richard Gale, Apr 25 '13 at 16:02
@Richard still doesn't make a whole lot of sense to me. Can you add the source data. — Conrad Frix, Apr 25 '13 at 16:08
@Richard : http://www.sqlfiddle.com/#!6/ded53/1 is this what you want ? — ATR, Apr 25 '13 at 17:37
Hi. Thanks for that. It's nearly there but not quite. Status 2 should be from 14/01/13 to 18/02/13. Status 8 should be to 25/03/13 and status 9 should run to today's date. — Richard Gale, Apr 25 '13 at 19:41

Gordon Linoff · Answer 1 · 2013-04-26T13:02:05.533

If you are using SQL Server 2012, you can do this:

with agst as
     (select AgreementID, 
             CONVERT(datetime, CONVERT(varchar(10), min(StatusDate), 103), 103) AS StatusDate, 
             CONVERT(datetime, CONVERT(varchar(10), min(StatusDate), 103), 103) AS DateFrom, 
             StatusID
      from TblStatusUpdates
      group by AgreementID, StatusID
     )
select agst.*,
       lead(datefrom) over (partition by AgreementId, statusId order by DateFrom) - 1 as DateTo
from agst

I think I simplified the calculation of the StatusDate/MinDate by just using an aggregation, which is the agst common-table-expression. The lead() function just pulls the next value into the row.

You can do something similar with a correlated subquery. Here is that version:

with agst as
     (select AgreementID, 
             CONVERT(datetime, CONVERT(varchar(10), min(StatusDate), 103), 103) AS StatusDate, 
             CONVERT(datetime, CONVERT(varchar(10), max(StatusDate), 103), 103) AS DateFrom, 
             StatusID
      from TblStatusUpdates
      group by AgreementID, StatusID
     )
select agst.*,
       (select top 1 DateFrom
        from agst agst2
        where agst2.agreementId = agst.agreementid and
              agst2.StatusID = agst.StatusID and
              agst2.DateFrom > agst.DateFrom
        order by DateFrom desc
       ) - 1 as DateTo
from agst

@Richard . . . I suspected as much, which is why I included both versions. — Gordon Linoff, Apr 25 '13 at 16:14
You loose me slightly with the code 'agst2.id = agst.id' Should that be StatusID? — Richard Gale, Apr 25 '13 at 16:19
I have tried your code, but I get all NULLS in the DateTo Results. — Richard Gale, Apr 26 '13 at 08:50

score 0 · Answer 2 · answered Apr 26 '13 at 09:39

quick update on this one, I was trying to be far too over complicated to get to the answer I needed.

Here is the code I have got now:

SELECT
     AgreementID, 
     StatusID,
     DateFrom,
     CASE WHEN DateTo IS NOT NULL THEN DATEADD(S, -1, DateTo) ELSE GETDATE() END AS DateTo
FROM
    (
     SELECT 
        AgreementID, 
        StatusID, 
        MIN(CONVERT(datetime,StatusDate,103)) as DateFrom, 
        (SELECT TOP(1) StatusDate FROM TblStatusUpdates AS B WHERE B.StatusID > A.StatusID ORDER BY StatusID ASC, StatusDate ASC) AS DateTo
     FROM 
        TblStatusUpdates AS A
     GROUP BY 
        AgreementID, 
        StatusID
    ) AS C

This gives me the following results:

AgreementID StatusID    DateFrom                  DateTo
109         1           2013-01-14 15:00:33.360   2013-01-14 15:01:27.393
109         2           2013-01-14 15:01:28.393   2013-02-19 13:57:32.437
109         8           2013-02-19 13:57:33.437   2013-03-26 15:41:49.843
109         9           2013-03-26 15:41:50.843   2013-04-26 10:34:13.860

Thank you all for your suggestions.

score -1 · Answer 3 · answered Apr 25 '13 at 18:02

-1

Hopefully this is what you are looking for.

SQL FIDDLE

answered Apr 25 '13 at 18:02

ATR

2,160
4
22
43

Hi. Thanks for that. It's nearly there but not quite. Status 2 should be from 14/01/13 to 18/02/13. Status 8 should be to 25/03/13 and status 9 should run to todays date – Richard Gale Apr 25 '13 at 19:56

SQL Database Date Ranges

3 Answers3