Help with Select statement based off of Date Range Parameter

[torolimon]

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?

 

[kray4660]

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)?

 

[torolimon]

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.

 

[pmax9999]

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:
[ul]
[li]{START_DATE} earlier than the range start date and the {END_DATE} after the range start date[/li]
[li]{START_DATE} and {END_DATE} both within the range[/li]
[li]{START_DATE} earlier than the range end date and the {END_DATE} after the range end date[/li]
[li][/li]
[/ul]

The following code should work:

(
	{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

 

[Charliy]

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}))