Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting records by future schedule date

Status
Not open for further replies.

RepRider

Technical User
Jan 25, 2007
114
US
CRXI and SQL Server

I have a report that I want to select records by the appointment dates in our scheduler tables. I want to automate it to run on a wednesday but choose all the clients who are scheduled for Dr. appts for the next week (M-F).

It is run now by a person manually selecting the appt dates for the next week. How do I rewrite the selection formula to automatically select for the next weeks dates based on the report run date? I know how to make it automatically run every wednesday in Infoview.

The current selection formula is:

not ({Program.program_name} in ["DD ALZHEIMERS RESPITE", "DD COMMUNITY INPATIENT", "DD EMERGENCY SERVICES", "DD FAMILY SUPPORT", "DD STATE INPATIENT", "MI ADULT COMMUNITY INPATIENT", "MI ADULT CRISIS RESIDENTIAL", "MI ADULT EMERGENCY SERVICES", "MI ADULT GERIATRIC ASSESSMENT", "MI ALZHEIMERS RESPITE", "MI CHILD COMMUNITY INPATIENT", "MI CHILD CRISIS RESIDENTIAL", "MI CHILD EMERGENCY SERVICES", "MI CHILD STATE INPATIENT"]) and
{Service_Group.service_group_name} in ["ACT", "ACT TEAM", "HOME BASED", "HOME BASED CLUSTER", "PSYCHIATRIC", "PSYCHIATRIC CLUSTER"] and
{Authorizations.archived} = 0 and
{Authorized_Service_View.archived} = 0 and
{Authorizations.authorization_end_date} >= DataDate - 45 and
not ({Sch_Entry.name} like ["*CANCEL*", "*RESCH*"]) and
{Sch_Entry.start_date} in DateTime (2009, 06, 22, 0, 0, 0) to DateTime (2009, 06, 26, 0, 0, 0)

It seems like I should put a DayofWeek formula in and use that but I am not sure. Can anyone assist me? Thanks!
 
If the job is scheduled to run every Wednesday, and you want to capture Mon - Fri appts. next week, you could:

create 2 formulas:

MonDate = today()+6
FriDate = today()+10

then your selection at the end would be

...
{Sch_Entry.start_date} in {@MonDate} to {@FriDate}

Hope this helps...
 
Is that an open parens after the word today?
 
ok, I got it plugged in but it did not return data for the Thursday of that week. Actually, there were no appts scheduled for Wed or Fri. Do I need to account for that?

There is a preset formula for LastFullWeek. Is there a way to create a formula for NextFullWeek? That would be the best solution for what I need. I don't know how to do that.
 
Nevermind... I found it in another search!!!

thread767-1291407

Works perfect!!! You guys/gals are FABULOUS!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top