Date sub

[tonynoriega]

Im having some issues (beyond my personal ones)...

In my table there is the field "last_visit" which i keep dates of my clients last visit in yyyy-mm-dd format.

Im trying to see how many people have visited in the last 7 days of any given date. in this case its a variable $case_date.

With my last query, $case_date = 2007-10-21.

And i want to count back 7 days, hence INTERVAL, to see how many records have been entered within that week...

I know of one record that should show up, becuase the last date was 2007-10-20. BUT for some reason i keep getting a count of 4. It is counting 3 additional records that are after 2007-10-21... why would that be?
___________________________________________________________
$sql = 'SELECT nhm_associate, COUNT(nhm_associate) AS testcount,
SUM(CASE WHEN DATE_SUB(\'' . $case_date . '\',INTERVAL 7 DAY) <= last_visit THEN 1 END) AS weekscount,
SUM(CASE WHEN DATE_SUB(\'' . $case_date . '\',INTERVAL 1 DAY) = last_visit THEN 1 END) AS yesterday,
FROM registration_table WHERE community=\'Sonata\' AND nhm_associate!=\'Not Specified\' GROUP BY nhm_associate';

[whoisgregg]

If you are calculating the "7 days ago" date in PHP then you don't need to use INTERVAL in your MySQL query. Just use `last_visit` >= '$case_date'.

What's happening is MySQL is going back another 7 days from the 7 days back you are providing.

[tonynoriega]

Well isnt that like saying to count 'last_visit' where 'last_visit' is greater than or equal to $case_date?

Which is not what i want.

I want to count 'last_visit' where 'last_visit' is less than or equal to $case_date, and only count back 7 days...(INTERVAL 7)

I want to find only the records that have been entered from 2007-10-21 to 2007-10-15. Which should, according to my table, be 1 record, not 4....

And then i want to count each day of the previous seven days in the week, hence:

$case_date = last_visit INTERVAL 6, 5, 4, 3, 2, 1....

So i can show in a calendar how many records were entered on each of the previous seven days.

i think i stated that right....

[whoisgregg]

Ahh, you did state that correctly. I just read it wrong the first time. :/

You can use a derived table to limit the records between $case_date and the earliest DATE_SUB. Just replace your FROM registration_table with:

FROM ( SELECT * FROM registration_table WHERE `last_visit` >= DATE_SUB(\'' . $case_date . '\',INTERVAL 8 DAY) AND `last_visit` <= \'' . $case_date . '\') AS dateRange

Using this derived table also speeds up the overall query on large tables. I ran a variation of this on a table with 343K records which was averaging ~4.75 seconds when run against the entire table. Using the derived table reduced it down to less than .5 seconds.

[tonynoriega]

Problem solved.

thanks