SBendBuckeye
Programmer
I have a header table and a detail table. The detail table has an activity date in it. I need to modify a report with a chart in it which reports everyone with no activity.
The initial design was simple. I did a select for the header table based on a NOT IN subselect as (simplified) below:
Select etc
From Header
Where HeaderKey NOT IN (Select DetailKey From Detail)
Now it gets interesting. The users want to select a date range parm and define no activity as either no activity ever (the simple case above) or as no activity within the user selected date range.
For the main report I created a subreport with a Shared BooleanVar named HasNoActivity. The subreport spins through all of the detail records for a given header record and returns True if none of the detail records fall within the specified date range and false if one or more do fall in the range. I then do some conditional logic based on HasNoActivity being true.
The problem is that the chart is itself a subreport so I cannot use the selecting subreport above as I did on the main report due to Crystals nesting subreport restrictions.
So I am attempting to create a Union query which will return the correct recordset for my chart (using SQL Server but a stored procedure is NOT an option).
The top half of the query is just the simple NOT IN subselect I used above. The bottom half is giving me fits because a detail record may or may not reflect activity depending on the date range selected by the user. If any one of the dates is in the range then activity should be true.
Here is an example to maybe clarify things. Assume 2 detail records with Activity Dates of 5/13 and 7/4.
If the user selects 6/1 thru 6/30 there is no activity because both dates are outside the range.
If the user selects 5/1 thru 5/30 or 7/1 thru 7/31 then there is activity because one or the other activity records falls within the specified range.
The problem is how do I get rid of the one outside of the range when one falls inside the range? No matter what I have tried, the one outside the range gets selected when it should not.
Any suggestions, help, etc would be greatly appreciated!
Thanks in advance for any help you can give me!
The initial design was simple. I did a select for the header table based on a NOT IN subselect as (simplified) below:
Select etc
From Header
Where HeaderKey NOT IN (Select DetailKey From Detail)
Now it gets interesting. The users want to select a date range parm and define no activity as either no activity ever (the simple case above) or as no activity within the user selected date range.
For the main report I created a subreport with a Shared BooleanVar named HasNoActivity. The subreport spins through all of the detail records for a given header record and returns True if none of the detail records fall within the specified date range and false if one or more do fall in the range. I then do some conditional logic based on HasNoActivity being true.
The problem is that the chart is itself a subreport so I cannot use the selecting subreport above as I did on the main report due to Crystals nesting subreport restrictions.
So I am attempting to create a Union query which will return the correct recordset for my chart (using SQL Server but a stored procedure is NOT an option).
The top half of the query is just the simple NOT IN subselect I used above. The bottom half is giving me fits because a detail record may or may not reflect activity depending on the date range selected by the user. If any one of the dates is in the range then activity should be true.
Here is an example to maybe clarify things. Assume 2 detail records with Activity Dates of 5/13 and 7/4.
If the user selects 6/1 thru 6/30 there is no activity because both dates are outside the range.
If the user selects 5/1 thru 5/30 or 7/1 thru 7/31 then there is activity because one or the other activity records falls within the specified range.
The problem is how do I get rid of the one outside of the range when one falls inside the range? No matter what I have tried, the one outside the range gets selected when it should not.
Any suggestions, help, etc would be greatly appreciated!
Thanks in advance for any help you can give me!