Networks

Ask something
Discussion Locked

SQL Query

0 helpful
5 replies

by joseph_pelish

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I work for a Theatre and I need to write a query to see if a patron has visited us more than once in a week. I have a table, let's say [Visits] that has the [DateVisited],[TicketNum] and the [PatronID]. Who would I create a query to see if any patron visited more than once in a week. Given that the patron can buy more then one ticket per visit.

5 replies

by dryflies

Not enough information. does the patron buy 3 tickets monday and visit monday wednesday and friday? or does the patron buy two tickets and visit monday, 3 more tickets wednesday, and 5 tickect on friday. (with the result of both scenarios being 3 visits)? Or is the use case different than one of those?

by joseph_pelish

Both scenrios are possible. I do not care when the bought the tickets, some are sold months in advance. But that they have tickets for 2 or more events with in the same week or during the same weekend. Here is a scenario.

John Doe bought tickets for "Peter Pan" for monday June 01, 2005 and also bought tickets for "Huck Finn" on thursday june 04, 2005.
Jane Soe bought tickets for "Jack and Jill" on Saturday June 06, 2005 and also for "Jack and Jill" on Sunday June 07, 2005.

These would be two different queries, with the second, of course, being a subset of the first.

The other parts I can figure out. It is the knowing if they have ticket for two different events in the same week, etc. I have considered doing it in a cursor or as a set of tables. But neither is working out at the moment.

by joseph_pelish

It does not matter how many tickets they buy. All that matters is if they saw 2 or more performances in the same week or in the same weekend.

by dryflies

OK,

This is enough info to at least form a plan. First, you need to identify a specific week. I am assuming you will be running this query weekly? in that case you can go either for the last 7 days, or last saturday to the previous sunday. I am familiar with M$ access for the most part but can do MySql as well. Both solutions require some programming other than SQL either VBA for Access or PHP/perl for MySql. Identify the date period and then insert it into the query as follows:
SELECT Count(Visits.PatronID) AS CountOfPatronID, Visits.DateVisited
FROM Visits
WHERE (((Visits.DateVisited)>"aweekago"));
I am not sure on the syntax of "aweekago" I think you may need to use VB or PHP/PERL to develop the query string correctly. in VB the function used would be DateDiff(interval,now,then)
OK, thats enough for now, if you have more questions, rate again.

by joseph_pelish

OK, sorry, I should have made it clear that this is a query that is being run for this entire year. Do not know how often they will want it in the future but for now it is for the entire year. So I do not think this will work. All I need is a count back, no date or anything. I am trying to avoid using VBA or something like that. The syntax for SQL is the same as VBA for DateDiff.

This discussion is closed to new replies.