Date Count Help

[Cy]

This is a reposting of an old thread that I never did get complete
help on. I'm hoping that with the holiday cheer being around, someone
might read and help a fellow poster out.

Here is what I have: I have a table that contains date of visit, job
code, job number, employee number and total hours.

What I would like to be able to do, is pull a query or some other
method, to view how many times we visited a job number, for a specific
job code, within a date range period and get a count of those visits.
Easy enough. However, I need to be able to distinguish, that if the
visit for a particular job code, was within the last two days, we only
count that as one visit. Example. We visit a client, on say December
24th, to perform a job code. We didn't finish the job, but return on
the 26th, because the 25th is Christmas, and we finish the work on the
26th. Need to be able to count that visit, the 24th and subsequent
26th as 1 visit.

Anyone got any suggestions or ideas?

Thanks

 

[Allen Browne]

I'm not sure this question is as simple as it sounds, but the core idea will
be to use a subquery to identify whether or not the visit meets the criteria
for being counted or not. If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html

Presumably you don't want to count weekends and public holidays as workdays.
Hence you will need a table containing the valid workdays. The subquery will
then need to get the previous visit date, and count the number of days form
your Workday table, to get the number of workdays between visits.

The other complicating factor is what happens to successive visits. For
example, if you started a job on Fri Dec 14, and came back on the next
workday (Mon 17), and again on Wed 19 and Fri 21, do you elimintate all the
visits (17, 19, and 21) because they were within 2 days of each other?

 

[Cy]

Allen,

Thank you for responding. Let me add a little clarification. Due to
the nature of the work being performed, landscaping, work does occur
on Weekends and even on most holidays. So, the principal that we are
wanting to use is any visit greater then 2 days apart, actually 3 days
apart, will be counted as a "new" visit. So, if we visited on the
14th, then returned on the 17th, that would count as 1 visit. If we
returned on the 19th, let's say, we realize that this would still be
counted in the prior visit count, thus the 14th, 17th and 19th would
all be counted as 1 visit, even though in theory it would 2 visits.
Now to add a little further information, it is very unlikely that we
would go back to the same place to perform that same job code type
work, within that close a period of time. Like once in 100, so we are
willing to accept the variable as mentioned above.

 

[Allen Browne]

Okay: you can do without the Workday table then.

Just use the subquery to determine whether the previous date is within in
last 2 days, as in this example:
http://allenbrowne.com/subquery-01.html#AnotherRecord