Report Employees with No Time 1

[LSN2TCI]

In CR 2011, I am trying to create a report that lists all Employees with no time entered for a Date Range. Two tables {Employees.EmpCode} and {Time.EmpCode} with a Left Outer Join. When I mix in the Parameter for Date Range {Time.Date} from the table on right, the LOJ no longer functions. I will need to suppress all duplicates, and null fields. Any help is greatly appreciated.

 

[LSN2TCI]

I should point out that this report is for data in MS SQL 2008. And I can pass SQL command through CR if needed.

 

[briangriffin]

In this case you don't even need the join if you want to use a command:

select * from Employees where EmpCode not in (select EmpCode from Time where DateField between {?StartDate} and {?EndDate})

Notice the parameters are created within the CR command dialog.

Your date field from the Time table is undoubtedly a datetime field, so to use the calendar controls in CR for the users to select the date range you'll need to convert to a date:

select * from Employees where EmpCode not in (select EmpCode from Time where cast(DateField as date) between {?StartDate} and {?EndDate})

Technically this will slow down the query, but depending on volume of data, etc. it might not be noticeable. If it's a problem then you can take a slightly different approach.

 

[LSN2TCI]

SQL is all new to me. But I need to learn. I am getting just a little confused by the EmpCode, which explains why the original developer created different field names for the data;
"SYS_MFS_EMPLOYEES"."CODE" This is the Employee List, I am using the Employee Code "CODE" as the key
"PAY_TMDET"."EMPCODE" This is the Time Entry Table, EMPCODE is the key.

So far in Crystal, I have setup a Command;
SELECT "CODE" from "SYS_MFS_EMPLOYEES" not in (SELECT "EMPCODE" from "PAY_TMDET" where cast("DATE" as date) between {?StartDate} and {?EndDate})

And of course I am getting a Syntax Error.

 

[LSN2TCI]

Whew! ignore that last post of mine.
Back home, I was able to research and piece together a much better string of code that works from within Mgmt Studio and CR;

select * from SYS_MFS_EMPLOYEES where CODE not in (select EMPCODE from PAY_TMDET where DATE between '2012-10-20' and '2012-11-4')

I am attempting to get the following line to work;
select * from SYS_MFS_EMPLOYEES where CODE not in (select EMPCODE from PAY_TMDET where cast(DATE as date) between {?StartDate} and {?EndDate})

I suspect the syntax of the 'cast' command works as swapping out actual dates for {?Start and End} works. I definitely need to be able to pass on the parameters. I have had a past consultant tell me that I could not pass Date parameters from CR to SQL, but he was admittedly not a SQL pro (He works in Pervasive)

Your help thus far has been huge!

 

[briangriffin]

Often you need to strip off the time from a datetime field, so casting your field to a date does that. Otherwise you'd drop records from the last day of the range. For example, if you asked for records between 11/1 and 11/30, you wouldn't get a record from 11/30 13:00. This is because in SQL a date is considered to be midnight (the beginning of the day).


So I'm not sure why this won't work for you:

select * from SYS_MFS_EMPLOYEES
where CODE not in
(select EMPCODE from PAY_TMDET
where cast(DATE as date) between {?BeginDate} and {?EndDate})


You can definitely pass parameters from CR to a SQL command.

 

[LSN2TCI]

To quote you, Brian, "Notice the parameters are created within the CR command dialog. " I don't understand that comment.

Am I missing something on the parameter creation? I created the {?StartDate} and {?EndDate} parameters as usual in the Field Explorer under Parameter Fields. I think I actually did this prior to adding the SQL Command in Database Expert.

Should I not create new parameters in such a way? If so, how do I define the parameter (ie, String/Date, Static/Dynamic, etc..)

Also, when saving the SQL Command in Database Expert, CR seems to execute the Query for syntax testing, and I suspect SQL is coming back with an error for the Date Parameters that are not defined. CR does not query the user for the parameter values when saving the command in db expert.

 

[LSN2TCI]

Nevermind.

Now I see the "Parameter List" Window right there on the Add Command to Report box.

Question answered

Thanks so much for your help!