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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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://www.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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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