October 13, 2009 at 10:05 am
I have a report that uses a query. It was supposed to fire off at the first of the month.
It didn't.
So now my user wants me to run the query but it can't go into the current month at all. It has to be the previous months worth of data.
I need help changing the where statment to only get up to the past month..not any of the current months data.
WHERE
isnull(datediff(dd,ld.delq_dt,getdate()),0) < 30
would it be possible to change it from using the 'getdate' to something like -
WHERE
isnull(datediff(dd,ld.delq_dt,'09/31/09',0) < 30
October 13, 2009 at 10:16 am
Maybe:
WHERE ld.delq_dt >= DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) -1, 0)
AND ld.delq_dt < DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP), 0)
October 13, 2009 at 11:22 am
thanks ken..but I don't think that's right...I get zero rows back (I know there are records)
October 13, 2009 at 11:57 am
Something along this line?
WHERE ld.delq_dt < dateadd(mm, datediff(mm, 0, '2009-11-10'), 0)
The dateadd/datediff will return the first day of the month.
Edit: code formatting changed to "xml" due to wrong display
October 13, 2009 at 11:59 am
lutz
I get an
An expression of non-boolean type specified in a context where a condition is expected, near ';'.
October 13, 2009 at 12:02 pm
krypto69 (10/13/2009)
lutzI get an
An expression of non-boolean type specified in a context where a condition is expected, near ';'.
change "& lt;" to "<" (the display of SQL code used to work much better on this site... :angry:
October 13, 2009 at 12:03 pm
sorry lutz missed your edit
trying now..
October 13, 2009 at 12:10 pm
So...if I want to only see last months (september) then I would change it to
WHERE ld.delq_dt < dateadd(mm, datediff(mm, 0, '2009-09-30'), 0)
or
WHERE ld.delq_dt < dateadd(mm, datediff(mm, 0, '2009-30-09'), 0)
October 13, 2009 at 12:26 pm
Date format:
it depends on the @@language setting during the session you're running the query in.
By default the Server and each new login is set to US English, so it would be '2009-09-30'
Date content:
Using '2009-09-30' or '2009-30-09' will return in < September first, which will give you data of the month before last month.
If you'd need to get the data for the previous month based on today you could use:
WHERE ld.delq_dt < dateadd(mm, datediff(mm, 0, '2009-10-13'), 0)
AND
ld.delq_dt >= dateadd(mm, datediff(mm, 0, '2009-10-13') - 1, 0)
October 13, 2009 at 12:31 pm
thanks so much for helping me out...
I need the month of september
October 13, 2009 at 12:53 pm
krypto69 (10/13/2009)
thanks so much for helping me out...
No problem. Glad I could help.:-D
So, you're all set then? (data for September will be returned with the code in my previous post)
Edit: typo fixed
October 13, 2009 at 1:21 pm
I'm not getting the same results
could it have something to do with not using the 'isnull' ?
October 13, 2009 at 1:25 pm
I don't get any results either.
But this might be caused by the fact that I don't ave anything to test against. 😉
Please follow the first link in my signature on how to post sample data and give me some data to play with together with your expected result.
October 13, 2009 at 2:02 pm
is this wrong?
original way:
WHERE
isnull(datediff(dd,ld.delq_dt,getdate()),0) < 30
this:
WHERE
isnull(datediff(dd,ld.delq_dt,'2009-09-30'),0) < 30
October 13, 2009 at 3:15 pm
krypto69 (10/13/2009)
is this wrong?original way:
WHERE
isnull(datediff(dd,ld.delq_dt,getdate()),0) < 30
this:
WHERE
isnull(datediff(dd,ld.delq_dt,'2009-09-30'),0) < 30
This method will prevent the usage of any indexes (if they exist) on the column. In this case, if your column 'ld.delq_dt' was indexed - that index would not be used.
Additionally, this method does not actually get the previous months values. It gets the last 30 days values from the time it was run. If it was run on the 1st of October - it would get all 30 days in September. If it was run on the 1st of November - you are going to miss the 1st of October because you have 31 days in that month.
The best method of querying is to use the following construct:
WHERE {your date column} >= {start of date range to check}
AND {your date column} < {end of date range to check + 1}
So, to get all of the data for September - your query would be:
WHERE ld.delq_dt >= '20090901'
AND ld.delq_dt < '20091001'
When SQL Server converts the literal date values to a datetime - you end up with the datetime at midnight. So the above becomes:
WHERE ld.delq_dt >= '20090901 00:00:00.000'
AND ld.delq_dt < '20091001 00:00:00.000'
Okay, now - instead of having to modify the query every month or trying to make sure the query is always run on the 1st of each month and you calculate the number of days in the previous month (I'm guessing that is what you do), we can calculate the 1st of the month very easily using the following formula:
DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0) -- First of current month
DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) - 1, 0) -- First of previous month
Using the above, your query becomes:
WHERE ld.delq_dt >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) - 1, 0) -- First of previous month
AND DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0) -- First of current month
And with that, you will always get the previous months rows regardless of what month it is run in and when it is run. Run this on the last day of the month and it will still get the previous months rows. And, if you have an index on that date column - it could be used (whether or not an index will be used is another topic).
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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply