INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with Select statement based off of Date Range Parameter

Help with Select statement based off of Date Range Parameter

(OP)
I have a table that captures patients that belong in a registry with a START_DATE field and an END_DATE field. I've added a Date range parameter {?Date Range} so I can run this report for whatever date range requested.

I'm struggling to write a select statement that will include the patients that belonged to the registry during the date range selected because all active patients have the default date 12/31/9999. Any ideas on how this can be done?

RE: Help with Select statement based off of Date Range Parameter

Now is that default date for the start_date or end_date or both? Also if that date is in one or both of the fields, what action do you want to take (basically I working up to creating a formula to deal with the exceptions)?

RE: Help with Select statement based off of Date Range Parameter

(OP)
Default date is only for the End_Date.

If I'm running the report for the Date Range 11/1/16 to 11/30/16, I want to capture all patients that their START_DATE and END_DATE fall within that range.

RE: Help with Select statement based off of Date Range Parameter

Your posts are a little unclear. I have interpreted the request to mean patients in the registry at any time during the parameter date range. This would include patients where:
  • {START_DATE} earlier than the range start date and the {END_DATE} after the range start date
  • {START_DATE} and {END_DATE} both within the range
  • {START_DATE} earlier than the range end date and the {END_DATE} after the range end date
The following code should work:

CODE

(
	{START_DATE} < MINIMUM({?Date Range}) and
	{END_DATE}   > MINIMUM({?Date Range})
)
or
(
	{START_DATE} = {?Date Range} and
	{END_DATE} = {?Date Range}
)
or
(
	{START_DATE} < MAXIMUM({?Date Range}) and
	{END_DATE}   > MAXIMUM({?Date Range})
) 

Hope this helps.

Cheers
Pete

RE: Help with Select statement based off of Date Range Parameter

If you're looking for all patients served during a period it's a little different that that. Something more like:

{START_DATE} <= MAXIMUM({?DATE RANGE})
AND (ISNULL({END_DATE}) OR {END_DATE} >= MINIMUM({?DATE RANGE}))

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close