none
Query to find records on 6 consecutive days RRS feed

  • Question

  • I have a table that has records of people who have a flight on a certain date.  I am looking to see who has had flights on all of the past 6 days.

    What would the SQl be for that? Where it checks each day and if the person has a flight on that day?

    Wednesday, May 23, 2018 9:40 PM

Answers

  • Hi,

    One possible way is to count the distinct records (assuming people can fly multiple times per day) for the last six days and return those with exactly 6. For example:

    SELECT Flyer
    FROM (SELECT Flyer
    FROM (SELECT DISTINCT Flyer
    FROM Table
    WHERE FlyDate Between Date()-5 And Date())
    GROUP BY Flyer
    HAVING Count(*)=6)

    (untested)
    Just my 2 cents...

    • Marked as answer by JHarding08 Thursday, May 24, 2018 8:22 PM
    Wednesday, May 23, 2018 10:01 PM

All replies

  • Hi,

    One possible way is to count the distinct records (assuming people can fly multiple times per day) for the last six days and return those with exactly 6. For example:

    SELECT Flyer
    FROM (SELECT Flyer
    FROM (SELECT DISTINCT Flyer
    FROM Table
    WHERE FlyDate Between Date()-5 And Date())
    GROUP BY Flyer
    HAVING Count(*)=6)

    (untested)
    Just my 2 cents...

    • Marked as answer by JHarding08 Thursday, May 24, 2018 8:22 PM
    Wednesday, May 23, 2018 10:01 PM
  • Hi JHarding,

    I found you post multiple threads and did not come back.

    Do you have any issue with threads below?

    Access Date Time Formatting

    Allowing text in Date Field

    If not, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    If you do, please feel free to follow up.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 24, 2018 2:01 AM
  • Marked an answer for this one. thank you for the reminder!
    Thursday, May 24, 2018 8:10 PM
  • This makes sense.  Thanks!
    Thursday, May 24, 2018 8:22 PM
  • This makes sense.  Thanks!

    Hi,

    You're welcome. Glad to hear you got it to work. Good luck with your project.

    Thursday, May 24, 2018 8:32 PM