Query to find records on 6 consecutive days

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?

Answer 1

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...

Answer 2

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 [e-mail].

Answer 3

Marked an answer for this one. thank you for the reminder!

Answer 4

This makes sense.  Thanks!

Answer 5

This makes sense.  Thanks!

Hi,

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