Date Help Request

I hope someone might be able to offer some assistance. I posted a
similar post, but the solution provided didn't work.

Here here goes.

We have crews that go out to visit clients. Might be 2 to a 5 person
crew. They will visit a location to perform a task. If the task is
not completed on the day they visit, they will visit again the next
day.

What we'd like to be able to do is count the number of visits we make
to a client within a year. In the scenario I gave above, that would
be counted as one visit.

We enter all our information into a payroll screen that has the
following fields. ContractNo for contract number, a jobcode used to
identify what we did while there, svcdate the date of service.

Below is the original post I made a few days ago, with a different way
of describing what I just said.

Any help is appreciated.

Thanks.

Morning all,

I have field that contains dates. I need to add up each time a visit
is made. Easy enough. In a query, I run the date count and all works
great. However, I need to filter out if the date of the last visit
and next visit are within 2 days of themselves. Example. They might
visit a customer on the November 21. They didn't complete the work.
They will not return on the 22nd, because it's a Holiday, so they will
return on the 23rd to finish up. I need to count that span of time as
1 visit, not 2.

Any thoughts, suggestions?

Thanks

Try something like this. I'm assuming that ContractNo identifies the client:

SELECT ContractNo, COUNT(*) As VisitCount
FROM YourTable AS T1
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.ContractNo = T1.ContractNo
AND T2.SvcDate T1.SvcDate
AND T2.SvcDate - T1.SvcDate = 2)
GROUP BY ContractNo;

I'm not sure if it will do the trick. What happens for instance if a crew
visits a site on one day then again for a new task two days later, the
intervening day in this instance not being a holiday? The above query would
still treat this as one visit as it assumes any visits two or less days apart
are one visit. Also what if the holiday period is two days, so a single
visit could be 3 days apart; here at least 25 December (Christmas day) and 26
December (Boxing Day) are both holidays, and to muddy the waters even more if
one or both fall on a weekend the official holidays are moved to the adjacent
weekday(s).

To be sure of getting it right you'd probably need an auxiliary 'calendar'
table, i.e. a table of all working days only, so that you could count against
this table rather than the full calendar.

Ken Sheridan
Stafford, England

"Cy" wrote:

I hope someone might be able to offer some assistance. I posted a
similar post, but the solution provided didn't work.

Here here goes.

We have crews that go out to visit clients. Might be 2 to a 5 person
crew. They will visit a location to perform a task. If the task is
not completed on the day they visit, they will visit again the next
day.

What we'd like to be able to do is count the number of visits we make
to a client within a year. In the scenario I gave above, that would
be counted as one visit.

We enter all our information into a payroll screen that has the
following fields. ContractNo for contract number, a jobcode used to
identify what we did while there, svcdate the date of service.

Below is the original post I made a few days ago, with a different way
of describing what I just said.

Any help is appreciated.

Thanks.

Morning all,

I have field that contains dates. I need to add up each time a visit
is made. Easy enough. In a query, I run the date count and all works
great. However, I need to filter out if the date of the last visit
and next visit are within 2 days of themselves. Example. They might
visit a customer on the November 21. They didn't complete the work.
They will not return on the 22nd, because it's a Holiday, so they will
return on the 23rd to finish up. I need to count that span of time as
1 visit, not 2.

Any thoughts, suggestions?

Thanks