Date Parameter choice SQL/Crystal Reports

[sandeep2013]

I have two sets of date parameters in Crystal Reports each has a from and to. If they chose 1 date parameter they do not need to pick the other and vica versa or they can pick both

process date from/process date to
Claim Live Service Date From/Claim Live Service Date to.

Can i do this in the command in my SQL statment?

 

[drlex]

In this situation, I'd use a COALESCE on the dates in the query:

SELECT servicedate from My_Data_Table where servicedate between coalesce(@claimfrom,@claimto) and coalesce(@claimto,@claimfrom)

This requires servicedate to hold just a date, and not a time component. Where there is a time value to consider, I'd wrap a DATEADD around the upper extremity to take it to one second before midnight.

SELECT servicedate from My_Data_Table where servicedate between coalesce(@claimfrom,@claimto) and  DATEADD(ss,86399,coalesce(@claimto,@claimfrom))

(Should the @claimto be earlier than the @claimfrom, no results would be returned.)

soi là, soi carré

 

[philhege]

One of the things you could assume from the description is that the user can place a lower bound date only, and expect that the query will return records from that date on. (Likewise vice-versa for the upper bound.) So you'd have to code for a range of dates instead of just the submitted date.

-----------
With business clients like mine, you'd be better off herding cats.