0

I have an ASP page linked to SQL server. Basically I need some queries will extract all records between certain dates. I need a monthly breakdown for the last 6 months.

So, this month is August (it's the 6th), so the first query needs to have a line that says

AND DATE BETWEEN '2012-08-01' and '2012-08-31'

And I need 5 more queries which say

AND DATE BETWEEN '2012-07-01' and '2012-07-31'

...

AND DATE BETWEEN '2012-06-01' and '2012-06-30'

Etc. etc going back to March.

Is there a way to do this? I've tried to do the following VB script but it's not giving the correct result;

ThisMonth = Date
Month1 = DateAdd("m", -1, Date)
Month1From = DateAdd("d", 1, Month1 - Day(Date))
Month1To = DateAdd("m", 1, Month1 - Day(Date))

Month2 = DateAdd("m", -2, Date)
Month2From = DateAdd("d", 1, Month2 - Day(Date))
Month2To = DateAdd("m", 1, Month2 - Day(Date))

Month3 = DateAdd("m", -3, Date)
Month3From = DateAdd("d", 1, Month3 - Day(Date))
Month3To = DateAdd("m", 1, Month3 - Day(Date))

Month4 = DateAdd("m", -4, Date)
Month4From = DateAdd("d", 1, Month4 - Day(Date))
Month4To = DateAdd("m", 1, Month4 - Day(Date))

Month5 = DateAdd("m", -5, Date)
Month5From = DateAdd("d", 1, Month5 - Day(Date))
Month5To = DateAdd("m", 1, Month5 - Day(Date))

I was going to construct the SQL based on the variables, but I cannot, as the variable Month5To is showing as the 29th of Match, but there are 31 days in March.

Can anyone help?

Ben Hamilton
  • 949
  • 3
  • 10
  • 21

1 Answers1

0

Why not change your query to filter on 

 WHERE MONTH(date)= @m AND YEAR(date) = @y

You could also get all the results in one result set using a GROUP BY query

Also note that your original query will fail should the date values have any times attached.

ie: 2012-07-31 09:15 is not between '2012-07-01' and '2012-07-31'

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • OK, by @m and @y do you mean variables which I create in VB script? – Ben Hamilton Aug 06 '12 at 09:28
  • I mean, ideally, parameters in a parameterised SQL (see http://stackoverflow.com/questions/770419/how-to-make-a-parametrized-sql-query-on-classic-asp) but, yes, values for the month and year. – podiluska Aug 06 '12 at 09:30
  • The date column has no time on it. Not sure about a parameter query, I only have RO access to the server so don't think this will work? I've amended it now so that I use the MONTH and YEAR functions in SQL Server SQL as suggested. Thanks – Ben Hamilton Aug 06 '12 at 09:57
  • The parameterisation takes place in ASP - it won't be affected by your readonly access to the database. – podiluska Aug 06 '12 at 10:02