Hello
I must be losing my mind. I am attempting to write a Crystal Report against PAYMASTR in which employee information is pulled, regardless of whether they meet the 'date range' criteria in the parameter.
SELECT "PAYMASTR"."NET_PAY_AMT", "PAYMASTR"."CHECK_DATE", "EMPLOYEE"."DATE_HIRED", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."EMPLOYEE" FROM "DB30_LAW_PROD"."lawuser"."EMPLOYEE" "EMPLOYEE" LEFT OUTER JOIN "DB30_LAW_PROD"."lawuser"."PAYMASTR" "PAYMASTR" ON "EMPLOYEE"."EMPLOYEE"="PAYMASTR"."EMPLOYEE" ORDER BY "EMPLOYEE"."EMPLOYEE"
Here's the rub: employees who do not meet the criteria of the date range are falling out of the report (even though it's an outer join). In Access, I wrote a pretty basic query to tell me who should appear in the report with no checks that meet the date criteria. I still want them to appear.
The report works fine without the date range (PAYMASTR.CHECK_DATE). When I add that, though, the employees who should appear don't. I have to be able to capture both.
Perhaps my mind is still on vacation. Any ideas? This is very strange.
I selected from 3/1/08 to 3/22/08 knowing that certain employees will have checks and others will not.
Thanks, John.
Yeah, I had tried that before but must be missing something.
Attached. CRX off of SQL Server.
Thanks!
This may not be the most elegant solution, but this works when I add a command based on PAYMASTR as follows (and joined it to EMPLOYEE as an outer join):
SELECT PAYMASTR.EMPLOYEE, PAYMASTR.CHECK_DATE, PAYMASTR.NET_PAY_AMT FROM DB30_LAW_PROD.lawuser.PAYMASTR PAYMASTR WHERE (PAYMASTR.CHECK_DATE>=getdate()-30)
On the plus side, you don't have to add a prompt and employees are included even if they weren't paid in the last 30 days. On the negative side, that 30 days is locked in.
This will suffice, I believe, as the time period is fixed.
Actually, the IsNull will only help for employees who have NEVER been paid.
In order to do what you're trying to do, you'd have to push the details part into a subreport or use a SQL command in order to push your date condition into the JOIN, like this:
SELECT EMP.EMPLOYEE, EMP.COMPANY, PYM.NET_PAY_AMT, PYM.CHECK_DATE, EMP.DATE_HIRED, EMP.LAST_NAME, EMP.FIRST_NAME, EMP.EMP_STATUS, PYM.CHECK_DATE FROM EMPLOYEE EMP LEFT OUTER JOIN PAYMASTR PYM ON (EMP.COMPANY=PYM.COMPANY) AND (EMP.EMPLOYEE=PYM.EMPLOYEE) AND (PYM.CHECK_DATE BETWEEN '2002-10-01' AND '2002-10-15') ORDER BY EMP.EMPLOYEE