Link to home
Start Free TrialLog in
Avatar of lisa_mc
lisa_mcFlag for United Kingdom of Great Britain and Northern Ireland

asked on

using coalesce but showing greater value of two

Hi everyone

small problem and can't seem to figure it out and would love help

Code below shows a link from one of my tables my problem is I have found a case where the withdrawn week is considerably greater than the end week which shouldn't be the case.

To cater for this I was wondering can you say coalesce these two values but whatever value is less (except NULL) display that value instead bearing in mind that I need to subtract 1 from the value withdrawn week.  I think I need coalesce because alot of the times withdrawn week can be null - maybe there is another way to do this im not sure?

Help appreciated

thanks
and ( (a.week_no >= b.starting_week)
and (a.week_no <= (coalesce (b.withdrawn_week -1, b.end_week))) )

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lisa_mc

ASKER

worked a treat thanks very much for your help