January 18, 2021 at 3:16 pm
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
January 18, 2021 at 6:14 pm
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));
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 18, 2021 at 7:26 pm
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
January 18, 2021 at 9:44 pm
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
January 19, 2021 at 8:18 am
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
January 19, 2021 at 11:20 am
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://qa.sqlservercentral.com/blogs/some-common-date-routines.
January 19, 2021 at 5:44 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply