Selecting last 6 months

  • Hi,

    I need to select rows from the last 6 months prior to the last loading date.

    I wonder which is the best and most cost effective way to achive this.

    This is the way i've done it.

     

    SELECT

    -columns

    FROM table

    WHERE date > DATEADD(MONTH, -6,  (select MAX(date) from table where <some criteria>))

     

    With the subquery im reading the table tiwce, is there a better way to perform this query?

    For now it's not an issue, but I fear it might be in the future when the table grows..

    Any tips on how to improve the query would be much appreciated.

    /E

  • Instead of approximating the "current" date by selecting the MAX(date) the code could reference CAST(GETDATE() as DATE) to access the system datetime and cast it as type DATE.

    select *
    from tableName
    where [date] > dateadd(month, -6, cast(getdate() as date));

    • This reply was modified 2 months, 3 weeks ago by  Steve Collins.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Hi Steve, thanks for the reply.

    My problem is that I have to filter the table on "last date loaded" e.g. the table doesn't get the data i'm interested in every day, some times it could take several weeks between inserts.

    I can't use GETDATE(), I need to access the date when the data was last loaded.

    /E

    
    
                        
  • Do you have a table that tracks your data loads?  That table should have a date/time column that identifies the last time the load was processed and completed successfully.

    With the correct index on that column - pulling that latest load date would be very simple and quick.

    I would modify the approach as:

    Declare @loadDate datetime = (Select max(datefield) From dbo.LoadProcessTable Where SomeStatus = 'Completed');

    Select ...
    From dbo.MyTable
    Where DateColumn >= dateadd(month, -6, @loadDate);

    Or...instead of worrying about *exactly* 6 months ago...

    Select ...
    From dbo.MyTable
    Where DateColumn >= dateadd(month, datediff(month, 0, @loadDate) - 6, 0); --first of the month 6 months ago

    This makes sure you have the past full 6 months - plus current month to date.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hi Jeffrey, many thanks!

    I have the column in my table telling me the last time rows were added.

    I guess I could use that one, but if I do, isnt it the same thing as my original query?

     

    /E

  • sqlErik wrote:

    Hi Jeffrey, many thanks!

    I have the column in my table telling me the last time rows were added.

    I guess I could use that one, but if I do, isnt it the same thing as my original query?

    /E

    Jeffrey's first query is the same, but his second one is not. What he means by *exactly* in his post is that when you add -6 months to GETDATE(), the DATEADD function includes the time, so you'll get this time on this date 6 months ago.

    In the second query, he's going back to the first of the month 6 months ago so you get the full month. For a good reference of some common date calculations, see https://www.sqlservercentral.com/blogs/some-common-date-routines.

  • sqlErik wrote:

    Hi Jeffrey, many thanks!

    I have the column in my table telling me the last time rows were added.

    I guess I could use that one, but if I do, isnt it the same thing as my original query?

    /E

    So the only way to determine when data was loaded is to look in the table that was loaded?  You don't have a separate table that tracks when the process ran and if/when it completed successfully?

    If that is the case - then you have no choice but to pull the data from that table.

    I would still separate the process and use a variable...and ensure there is an index on that column.  This is really just a preference - using that code in the query will work and will probably perform the same.

    As to selecting the prior 6 months...that needs to be clarified.  I showed a way to make sure you always get a full 6 months plus the current month to date.  If you really need a rolling 6 months based on the latest date (and time) then your solution works, but will be inconsistent due to the time portion (unless that column is always set to 00:00:00.000 time).

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 7 (of 7 total)

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