Forum Moderators: open
$sql = 'SELECT nhm_associate, COUNT(nhm_associate) AS weekscount, (SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) <= entry_time GROUP BY nhm_associate) AS yesterday, (SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time) AS totalcount FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time GROUP BY nhm_associate ORDER BY entry_time';
The first part:
SELECT nhm_associate, COUNT(nhm_associate) AS weekscount...
(...inner statements...)
FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time GROUP BY nhm_associate ORDER BY entry_time';
returns 12 records...which is correct. that is a total of all 3 agents who enter records into the table dabase....
the second part:
(SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) = entry_time GROUP BY nhm_associate) AS yesterday
is supposed to count the records that have been entered 2 days ago, from the 3 agents, and is supposed to group them...so if Agent A enters 2, Agent B enters 1, and Agent C enters 0...it should group them...but does not....it is showing that each agent entered 3 records from two days ago...
the last part works fine, which is:
(SELECT COUNT(*) FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 6 DAY) <= entry_time) AS totalcount
this part takes the previous 6 days, and the current day, and groups the weekly total for each agent...that works fine. It is showing Agent A with 7 records, Agent B with 2 records, and Agent C with 3 records....
WHY would the second statement not properly group them?
now that i look at my output...
this section is not pulling any data..
WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) = entry_time
but if i do this:
WHERE DATE_SUB(CURDATE(),INTERVAL 2 DAY) <= entry_time
it pulls the 3 entries over the past 2 days...
if my column of "entry_time" is a data format of "datetime" is CURDATE not able to calculate the proper dates?
in other words, what data type is CURDATE? is that compatible with a datetime value?
