Query with Subquery and parameters 2

[uachap]

I need to have this as my query in Crystal.

SELECT DISTINCT A.PERSONNUM, A.PERSONFULLNAME FROM VP_DTC_BASESTORE A
WHERE EMPLOYMENTSTATUS = 'Active'
AND HOMELABORLEVELNM4 = 'F'
AND A.PERSONNUM NOT IN
(SELECT DISTINCT B.PERSONNUM FROM VP_DTC_BASESTORE B
WHERE B.DATE BETWEEN fromdate AND todate)

where fromdate and todate are parameters passed into the report.

If I use a SQL Expression, I can't use parameter fields.

I'm using crystal 8. Any help would be appreciated.

 

[tektipdjango]

your SQL statement may be a lot quicker if you use derived queries like this :

select A.PERSONNUM, A.PERSONFULLNAME
from
(
SELECT DISTINCT A.PERSONNUM, A.PERSONFULLNAME FROM VP_DTC_BASESTORE
WHERE EMPLOYMENTSTATUS = 'Active'
AND HOMELABORLEVELNM4 = 'F'
) as tableA
left join
(
SELECT DISTINCT B.PERSONNUM FROM VP_DTC_BASESTORE
WHERE B.DATE BETWEEN fromdate AND todate
) as tableB
on A.PERSONNUM = B.PERSONNUM
where B.PERSONNUM is null

For your problem, I can figure out a solution but I can't test it as I have CR 9 which allows params in sql Queries.

Try to select VP_DTC_BASESTORE table twice in CR and do a left join betwenn personnum field like I did in sql.
In the selection formula try to select the records with B.PERSONNUM field that are null
Then you can use the parameter fields

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[tektipdjango]

Otherwise, one way would be to do a report with the first table with group on personnum and put in the GH section (Hidden) a subreport with the design of the second query, the link between the sub and the report beeing on personnum field.
then do a suppress of the group footer if the sub is not empty. In this GF you put the A.PERSONNUM, A.PERSONFULLNAME fields

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[lbass]

You could do a left join from Table A to Table B, and use the following for your record selection:

{tableA.EmploymentStatus} = 'Active' and
{tableA.HomeLaborLevelNM4} = 'F'

Group on {tableA.Personnum} and then create a formula {@inperiod}:

if {tableB.date} in {?fromdate} to {?todate} then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@inperiod},{TableA.Personnum}) = 0

This should return records only for those people with no dates during the parameter period.

-LB

 

[uachap]

Thank both of you for your valuable suggestions.

lbass, when I use your suggestion, I can understand what you're saying, but I don't know if I'm using the group selection formula correctly.

In order to get it to work, in the group selection formula, this is what I did:

isnull (sum({@inperiod},{VP_DTC_BASESTORE.PERSONNUM})) or
(sum({@inperiod},{VP_DTC_BASESTORE.PERSONNUM})) = 0

Because those that had no date weren't being assigned a 0 or a 1 in the @inperiod formula.

Anything you can see that would mess this up?

Thank you both again!

 

[lbass]

I would have changed {@inperiod} to:

if isnull({tableB.date}) or
not({tableB.date in {?fromdate} to {?todate}) then 0 else 1

...and then used the group selection formula I suggested earlier, but I think your approach should work, also. Does it give you the expected results?

-LB

 

[uachap]

It does give me the expected results. Thank you so much!