Query Months help
- by StealthRT
Hey all i am in need of some helpful tips/advice on how to go about my problem. I have a database that houses a "signup" table. The date for this table is formated as such:
2010-04-03 00:00:00
Now suppose i have 10 records in this database:
2010-04-03 00:00:00
2010-01-01 00:00:00
2010-06-22 00:00:00
2010-02-08 00:00:00
2010-02-05 00:00:00
2010-03-08 00:00:00
2010-09-29 00:00:00
2010-11-16 00:00:00
2010-04-09 00:00:00
2010-05-21 00:00:00
And i wanted to get each months total registers... so following the example above:
Jan = 1
Feb = 2
Mar = 1
Apr = 2
May = 1
Jun = 1
Jul = 0
Aug = 0
Sep = 1
Oct = 0
Nov = 1
Dec = 0
Now how can i use a query to do that but not have to use a query like:
WHERE left(date, 7) = '2010-01'
and keep doing that 12 times? I would like it to be a single query call and just have it place the months visits into a array like so:
do until EOF
theMonthArray[0] = "total for jan"
theMonthArray[1] = "total for feb"
theMonthArray[2] = "total for mar"
theMonthArray[3] = "total for apr"
...etc
loop
I just can not think of a way to do that other than the example i posted with the 12 query called-one for each month.
This is my query as of right now. Again, this only populates for one month where i am trying to populate all 12 months all at once.
SELECT count(idNumber) as numVisits, theAccount, signUpDate, theActive
from userinfo
WHERE theActive = 'YES'
AND idNumber = '0203'
AND theAccount = 'SUB'
AND left(signUpDate, 7) = '2010-04'
GROUP BY idNumber
ORDER BY numVisits;
The example query above outputs this:
numVisits | theAccount | signUpDate | theActive
2 SUB 2010-04-16 00:00:00 YES
Which is correct because i have 2 records within the month of April.
But again, i am trying to do all 12 months at one time (in a single query) so i do not tax the database server as much when compared to doing 12 different query's...
UPDATE
I'm looking to do something like along these lines:
if NOT rst.EOF
if left(rst("signUpDate"), 7) = "2010-01" then
theMonthArray[0] = rst("numVisits")
end if
if left(rst("signUpDate"), 7) = "2010-02" then
theMonthArray[1] = rst("numVisits")
end if
etc etc....
end if
Any help would be great! :)
David
