Query Months help
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"
the开发者_C百科MonthArray[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
You need to create a derived table with all the months and join that onto your query so that you get zero counts instead of missing rows for those months that have no entries. The following query is a bit long, but it should meet your requirements:
SELECT
T1.`month`,
COALESCE(numVisits, 0) AS numVisits
FROM (
SELECT 1 AS month
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
) AS T1
LEFT JOIN (
SELECT
MONTH(signUpDate) AS `month`,
COUNT(*) AS numVisits
FROM userinfo
WHERE theActive = 'YES'
AND idNumber = '0203'
AND theAccount = 'SUB'
AND YEAR(signUpDate) = 2010
GROUP BY MONTH(signUpDate)
) AS T2 ON T1.`month` = T2.`month`
Ok, got it.
do until oRecordset.EOF
theMonth(x) = oRecordset("numVisits")
oRecordset.movenext
x = x + 1
loop
精彩评论