Not In Date Range

[jhixson]

I have a report I need that shows everyone who received service for one date range but who did not receive service for a second date range. I have tried using an alias table but it keeps showing clients that received service from the second Date Range. Any suggestions is greatly appreciated.

John

 

[synapsevampire]

I'll assume that these dates cannot overlap.

{table.field} = {?firstdaterangeparm}
and
{table.field} <> {?seconddaterangeparm})

-k

 

[lyanch]

Use a self join (join the table to a copy of itself - you will need an appropriate join.. maybe the person id) and use one table for the &quot;in&quot; and the other table for the &quot;not in&quot; with an equal join between them. That will give you only those that fit both conditions.

Lisa

 

[synapsevampire]

Lisa: Why bother with a self join? Doesn't my solution handle it?

-k

 

[lyanch]

-k

The {table.field} can't be both at the same time.. so your solution would only return items for the = and not eliminate any that don't have a *different* record in the second range.

Lisa

 

[Reebo99]

I would have thought -k solution would work, due to the AND between the 2 criteria. If the suggestion included OR, this wouldn't work. Reebo
Scotland (Going mad in the mist!)

 

[synapsevampire]

Ahhh, right, I oversimplified, I had read it to mean he wanted all of those in one date range, and all not within another date range, your solution looks about right now that I understand it.

-k

 

[kenhamady]

Maybe I am missing something, but I think that the &quot;not in range&quot; criteria is going to force the use of qroup selection. And, if you use Group Selection I think it could be done with or without the self join.

I say this because a person can have some records that are NOT in the range and some records that ARE in the range. The ones that are NOT in the range will qualify even though the person has some that ARE in the range - so the person shows up even though he doesn't qualify.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Reebo99]

Chelseatech, you star! great solution, one line formula, no looping, quick and simple. A star well earnt! Reebo
Scotland (Going mad in the mist!)

 

[Reebo99]

Sorry everyone, wrong thread! Reebo
Scotland (Going mad in the mist!)