Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Count of dates after

    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?

  2. #2
    Join Date
    Sep 2009
    Posts
    64
    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.

  3. #3
    Join Date
    Sep 2009
    Posts
    4

    Count of dates after

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •