Replace if-then-else with a better method in selecting records 1

[crystaldev1]

Hello. I am using CR 9 and SQL Server 2005. I would like to get some feedsbacks regarding a better approach rather than using 10 if-then-else statements. I have 10 fields which I would need to sort through in the record selection for each record. I have a date range parameters @StartDate and @EndDate which the report is based off.

For example, I have a table called "Software". In that table, I have 10 different fields (Date1, Date2, Date3, ...,Date10) in date ascending respectively. What I would like to do is first, select the latest Date field that is not null (so check Date10, then Date9, then Date8 ...). Once the Date field is selected and if the selected Date field falls within the date range parameter, then I need to select that record.

So I have if statement as follows:

(if not isnull(Date10) then Date10
else if not isnull(Date9) then Date9
else if not isnull(Date8) then Date8
.............
else if not isnull(Date1) then Date1) in
[@StartDate,@EndDate].

I would like to know if there's a better approach to this. Thanks.

 

[lbass]

You could try:

maximum([Date1,Date2,Date3]) = {?DateRange}

-LB

 

[crystaldev1]

That is a great idea to use maximum(). That would work but another issue is that I have another field I have to pull depending on which Date field I select. For ex, if I selected Date5 then there's a field called Status5 that I have to pull as well from that table. So Date1 then Status 1, Date 2 then Status 2, ... By using maximum(), how can I determine which Date field I selected so that I can select the Status field that corresponds to that Date field. Thanks.

 

[lbass]

Are there any other requirements? You should have laid them all out to begin with.

-LB

 

[crystaldev1]

That is it. I just have to pull the corresponding Status field along with the Date field in the main report. For record selection, I only need to use the maximum() to select the "Software" records.

 

[lbass]

Actually, I think you should go to file->report options->check "convert nulls to default values."

Then try a formula like this:

datevar array x := [date1,date2,date3];
stringvar array y := [status1,status2,status3];
numbervar i;
numbervar j := ubound(x);
stringvar z := "";
for i := 1 to j do (
if x = maximum(x) then
z := status;
);
z

-LB

 

[crystaldev1]

Thanks lbass. That worked great.