Need 5 Business Days...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone! Using A02 on XP.

I have a query that is pulling all records ">=[StartDate] and < [EndDate]+1"

I also have a field [TurnTime] that is a calculation of
[DateMailed]-[Complete]. My problem is that our Turn Time Standard is five
business days. I can't identify business days here. Is there something I
can do to this field or should I have another field identify how many
business days exist in the list of dates between [StartDate] and [EndDate]?
Then use that field for setting the criteria to >5?

I would really, really appreciate any help or advice! Thanks in advance for
your time!

Luv U Guys!!!
 
Hi everyone! Using A02 on XP.

I have a query that is pulling all records ">=[StartDate] and < [EndDate]+1"

I also have a field [TurnTime] that is a calculation of
[DateMailed]-[Complete]. My problem is that our Turn Time Standard is five
business days. I can't identify business days here. Is there something I
can do to this field or should I have another field identify how many
business days exist in the list of dates between [StartDate] and [EndDate]?
Then use that field for setting the criteria to >5?

I would really, really appreciate any help or advice! Thanks in advance for
your time!

Luv U Guys!!!
Click to expand...
Advice? Use a calendar table.
Something to look at: http://www.psci.net/gramelsp/temp/Calendars.zip
 
I appreciate the info Michael but I'm looking for a field in my query to tell
me how many business days there are in my date range, then I can put >5 as
criteria and only pull records that exceed the 5 business day turnaround
standard. I'll keep your files for possible future need though. Thanks again
and I really appreciate your participation in the newsgroups!
--
Bonnie W. Anderson
Cincinnati, OH


Michael Gramelspacher said:
Hi everyone! Using A02 on XP.

I have a query that is pulling all records ">=[StartDate] and < [EndDate]+1"

I also have a field [TurnTime] that is a calculation of
[DateMailed]-[Complete]. My problem is that our Turn Time Standard is five
business days. I can't identify business days here. Is there something I
can do to this field or should I have another field identify how many
business days exist in the list of dates between [StartDate] and [EndDate]?
Then use that field for setting the criteria to >5?

I would really, really appreciate any help or advice! Thanks in advance for
your time!

Luv U Guys!!!
Click to expand...
Advice? Use a calendar table.
Something to look at: http://www.psci.net/gramelsp/temp/Calendars.zip
Click to expand...
 
I appreciate the info Michael but I'm looking for a field in my query to tell
me how many business days there are in my date range, then I can put >5 as
criteria and only pull records that exceed the 5 business day turnaround
standard. I'll keep your files for possible future need though. Thanks again
and I really appreciate your participation in the newsgroups!
Click to expand...
If I understand the problem the idea is to find all records
between startdate and enddate where number of days
between DateMailed and Complete is greater than 5.

This assumes a calendar table and a holiday table. You can get by without
a separate holiday table by having a holiday column in the calendar table.
It makes the subquery simpler.

This is untested.

SELECT T.*,(SELECT Count(*)
FROM Calendar
WHERE (((Calendar.calendar_date) Between T.[DateMailed] And T.[Complete])
AND ((Calendar.work_day)=1)
AND ((Exists (SELECT Holidays.holiday
FROM Holidays
WHERE Holidays.holiday = Calendar.calendar_date))=False))
AS TurnTime
FROM Table1 AS T
WHERE T.some_date BETWEEN [StartDate] AND [EndDate]
AND (SELECT Count(*)
FROM Calendar
WHERE (((Calendar.calendar_date) Between [DateMailed] And [Complete])
AND ((Calendar.work_day)=1)
AND ((Exists (SELECT Holidays.holiday
FROM Holidays
WHERE Holidays.holiday = Calendar.calendar_date))=False)) >
5;
 
Back
Top