Select Query Using Different Date Ranges

[Hemi426]

Greetings, I want to build a query that runs daily to select some past records. I would like to have it work as follows:



1 - On any day other than the first day of the current month, I need records from the first day of the current month to previous day of the current day ie - on the 17th, I need the 1st thru the 16th (Current month aggregate)

2 - On the first on the month, I need records from the 1st day of the previous month to the first day of the current month (Previous month total)

Here is what I have tried so far in the criteria field of my date\time column:

IIf(DatePart('d',[Response_Date])<>1,((Between DateSerial(Year(Date()),Month(Date()),Day(Date())-1) And DateSerial(Year(Date()),Month(Date()),Day(Date())) ,(Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)

I have also been toying with another type of statement (below) but am not sure how to get the statement to evaluate the IF function and select the proper date range accordingly

In (SELECT [Response_Date] FROM [dbo_Response_Master_Incident] WHERE [Response_Date] BETWEEN #01/01/10# AND #07/01/10#)

I am on the right track here or is there another easy way to accomplish this goal?? Thanks for any help.

[jzwp11]

I think the following is your best option but there are some errors

IIf(DatePart('d',[Response_Date])<>1,((Between DateSerial(Year(Date()),Month(Date()),Day(Date())-1) And DateSerial(Year(Date()),Month(Date()),Day(Date())) ,(Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)


1. If you are running this daily, you need the IIF() function to evaluate the current date, not the response_date

IIf(DatePart('d',date())<>1,((Between DateSerial(Year(Date()),Month(Date()),Day(Date())-1) And DateSerial(Year(Date()),Month(Date()),Day(Date())) ,(Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)

2.You need to have the response date as part of the BETWEEN clause

IIf(DatePart('d',date())<>1,((Response_Date Between DateSerial(Year(Date()),Month(Date()),Day(Date())-1) And DateSerial(Year(Date()),Month(Date()),Day(Date())) ,(Response_Date Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)


3.If the date is not the first, you have to hard code the 1st, you have it using the current day's date

you have this:
IIf(DatePart('d',[Response_Date])<>1,((Between DateSerial(Year(Date()),Month(Date()),Day(Date())-1) And DateSerial(Year(Date()),Month(Date()),Day(Date())) ,(Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)

Should be this:
IIf(DatePart('d',date())<>1,((Response_Date Between DateSerial(Year(Date()),Month(Date()),Day(Date())-1) And DateSerial(Year(Date()),Month(Date()),1) ,(Response_Date Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)


4. The second half of your IIF() function will probably return the wrong year if the date is 1/1/yyyy, you need to subtract the 1 from the month of the current date first and then apply the dateserial() function; this has to be done for the year as well. I would also recommend using the dateadd() function throughout your criteria. Also, the last part of the BETWEEN clause after the AND can be simplified to just Date() since you want the criteria to be BETWEEN the first of the previous month and today (when the current date is the 1st)

IIf(DatePart('d',date())<>1,Response_date Between DateSerial(Year(date()),Month(date()),Day(dateadd( "d",-1,date()))) And DateSerial(Year(date()),Month(date()),1) ,Response_Date Between DateSerial(Year(dateadd("m",-1,date())),Month(dateadd("m",-1,date())),1) And date())



Out of curiosity, if it is the first of the month, wouldn't you want from the first of the previous month through the end of the previous month rather than to the first of the month (i.e. the current date=first)? If so then, you have to subtract one from the current date shown in blue below

IIf(DatePart('d',date())<>1,Response_date Between DateSerial(Year(date()),Month(date()),Day(dateadd( "d",-1,date()))) And DateSerial(Year(date()),Month(date()),1) ,Response_Date Between DateSerial(Year(dateadd("m",-1,date())),Month(dateadd("m",-1,date())),1) And dateadd("d",-1,date()))

[Hemi426]

Thank you jzwp11, I'll work with this and see what I can come up with.

Thanks Again!

[jzwp11]

Sounds good; let us know if you have further questions.