Problems Adding A Date Range Parameter to SQL Command 1

[Sange]

I am current developing a report using Crystal Reports v10. I initially created it by linking a number of tables and adding a date range parameter as well as a location parameter however, the performance was dismal and as a result, I am now looking at developing a similar report using an SQL Command.

The SQL was going fine until I tried to incorporate the date range parameter into the Command. I have checked a few forums and it appears that I can’t actually add a date range parameter to a Command.

What I’m hoping to do is report on how many people were housed at a particular facility over a date range entered by the user. People who entered the facility prior to the beginning of the date range but left within the date range or entered the facility prior to the date range and are still in the facility should also be counted.

The original report had the following in the select {PLACEMENTS.START_DATE} <= {?Date Range} and {DETENTION_FACILITIES.AGENCY_CODE} = {?Station Code} and
(isnull({PLACEMENTS.END_DATE}) or {PLACEMENTS.END_DATE} = {?Date Range})

Is there anyway I can write something similar into my SQL command? I should also mention that the PLACEMENT.START_DATE field is a date/time field however I would want the parameter to appear as a date field.

Any assistance would be appreciated.

Thank you.

 

[Turkbear]

Hi,
The only way I know of would be to create 2 Date parameters
for Start and End.
If NO Date type parameters can be used ( not sure)
then use 2 Strings and convert to Date in the Command's Sql code.
(formatting may be an issue unless your users can have examples or instructions)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Sange]

Thanks Turkbear, I tried to create two date parameters instead of the date range but had no luck getting it to work. Unfortunately SQL is not my strong point so I'm sure how I'm supposed to actually write the parameters into the command to also include the specific conditions I require (ie. ensure people arriving prior to the start date range but leaving during or after start date range are also captured). Do you happen to know how this part of the SQL should be written?

 

[jmd0252]

One thing you might try,, when writting SQL commands I find it a lot easier,, if I actually go into the SQL management studio and create a view,, that way I can get my view that I want,, and creating the view also shows me the sql that is used to create it,, and that sql becomes my "command",, in Crystal..

 

[lbass]

Create the {?start} and {?end} date parameters within the command, on the right. Then double click on each to add to the command. The syntax should be the same as in a regular selection formula. This should work fine as long as your date field is an actual date or datetime datatype.

-LB

 

[Sange]

Hi all,

Thanks so much for your assistance. I've managed to put something together which kind of works - well it's bringing back some records but it's definitely missing any records where the end date is null. I thought I'd paste the entire command just in case there is something I missed. In addition, I would like the actual parameter to appear as a date parameter not a date/time. I know how to do it normally but not in a Command. Also, is there any reason why when I go back and edit the command, the parameter boxes will appear but there is no where to actually input the date range or agency code? Is this normal? I end having to paste the command into a new document and start again.

This is the SQL I've written:

select
bfp.booking_number,
p.start_date,
p2.end_date,
df.facility_name,
mn.lname,
mn.fname,
mn.dob,
mn.sex_sex_code,
b.person_id


from
bookings_facility_placement bfp,
placements p,
placements p2,
detention_facilities df,
bookings b,
master_names mn


where
p.placement_id = bfp.in_placement_id and
p2.placement_id = bfp.out_placement_id and
df.facility_id = bfp.facility_id and
b.booking_number = bfp.booking_number and
mn.per_person_id = b.person_id and
mn.mn_id = b.mn_id and
(p.start_date <= {?Date1} OR
p.start_date between {?Date1} and {?Date2}) and
(p.end_date is null or p.end_date between {?Date1} and {?Date2}) and
df.agency_code = '{?Agency Code}'

 

[lbass]

You might be missing the null dates because you are using all inner joins, but without know the logic of the table joins and how the fields work in relation to each other, it is hard to make a recommendation. Not sure why you are joining in the where clause instead of the from clauses, but you might consider using a left join TO the p table. Is it also possible that the end date has some other non-null date to indicate no end date?

In the main report, go into the edit parameter screens for those parameters where you see no place to enter selections and make sure the "allow custom values" is set to true.

-LB

 

[Sange]

Thank you so much Lbass,

I forgot all about the inner joins, so rejigged the command to take this into account and it's now bringing back null end dates as well.

I did go to the main report to edit the parameters and everything was ghosted out so I couldn't set anything. However, I found that if I refreshed the parameters and went into "Default Values" it would work again! The problem only arises when I edit the SQL Command so hopefully there won't be too many more edits.

With regard to adding my joins in the "Where" clause - this is the only way I knew (limited SQL training). I did some research however and found examples of joins under the "From" clause and was able to rewrite the Command which now looks like this:

Select
bfp.booking_number,
df.facility_name,
df.agency_code,
p.start_date,
p2.end_date,
mn.lname,
mn.fname,
mn.mname,
mn.dob,
mn.sex_sex_code,
b.person_id

From
bookings_facility_placement bfp
join detention_facilities df
on df.facility_id = bfp.facility_id
join placements p
on p.placement_id = bfp.in_placement_id
left outer join placements p2
on p2.placement_id = bfp.out_placement_id
join bookings b
on b.booking_number = bfp.booking_number
join master_names mn
on mn.per_person_id = b.person_id and
mn.mn_id = b.mn_id

Where
(p.start_date <= {?Date1} or
p.start_date between {?Date1} and {?Date2}) and
(p2.end_date is null or p2.end_date between {?Date1} and {?Date2}) and df.agency_code = '{?Agency Code}'

Unfortunately, I still have no idea how to get only the date component of the parameter to appear. The actual field is a date/time but the users want to enter a date without having to include the time which is understandable. Any ideas?

 

[lbass]

When you set up the date parms within the command, choose date, not datetime. This will work correctly even if your field is datetime.

-LB

 

[lbass]

Actually, I would probably set up the where clause like this:

Where
(
p.start_date <= {?Date1} or
p.start_date >= {?Date1} and
p.start_date < {?Date2} + 1
) and
(
p2.end_date is null or
p2.end_date >= {?Date1} and
p2.end_date <{?Date2} + 1
) and
df.agency_code = '{?Agency Code}'

-LB

 

[Sange]

Lbass,

As always, you've been extremely helpful. Your suggestions have done the trick and the report is running beautifully.

Thanks for your all your assistance.
Sandra