Hi,

I am trying to get a count of all records where the date_resolved is some date or later
OR
the date resolved is NULL and date_submitted is before the date

and I want this data for each day 20 days prior to today.

I have a table called days that I can use to join on to get the range of days.

Can I do that?

not sure if I understand it correctly ... what is date? Is it today? or is it some arbitrary date? And what do you mean by each day 20 days prior to today? Is is each date_submitted within 20 days of today?

Assuming it's some arbitrary date, you would do something like this ...

SELECT
date_submitted, count(*)
FROM
some_table
WHERE
(
date_resolved >= '2009-09-09'
OR (date_resolved IS NULL AND date_submitted < '2009-09-09')
) AND date_submitted >= CURDATE() - INTERVAL 20 DAYS
GROUP BY
date_submitted

I don't think the above makes sense though ... why would you want to have date_submitted > (today - 20 days) and date_submitted > some date ... Perhaps I'm not understanding your question right.

Thanks for the reply.

Here is what I think should work. The problem is once I add the interval clause the results only include items with dates matching the Submit_Date, does not count items with a tcgaz_data_dates.Submit_Date < '2009-09-09'.

In the end the '2009-09-09' should really be the same as the interval date -
CURDATE() - INTERVAL 20 DAY, but I am not sure how the query will behave with two intervals in the same query.

Any thoughts?

Submit_Date count( * )
2009-08-23 3
2009-08-24 1
2009-08-25 1
2009-08-27 3
2009-08-31 1
2009-09-01 2
2009-09-02 3
2009-09-03 7
2009-09-04 3
2009-09-06 3
2009-09-07 7
2009-09-08 2

SELECT
tcgaz_data_dates.Submit_Date, count(*)
FROM
tcgaz_data_dates
WHERE
(tcgaz_data_dates.date_resolved >= CURDATE() - INTERVAL 20 DAY
OR (tcgaz_data_dates.date_resolved IS NULL
AND tcgaz_data_dates.Submit_Date < '2009-09-09')
)
AND tcgaz_data_dates.Submit_Date >= CURDATE() - INTERVAL 20 DAY
AND tcgaz_data_dates.product_platform in ('0', 'HPP')
AND state not in ('1AAAA', 'Duplicate', 'Failed', 'Rejected', 'Unreproducible')
GROUP BY
tcgaz_data_dates.Submit_Date