Issue with using iff in a query

Question

Post reply

Issue with using iff in a query

pwalter83

SSChampion

Points: 14575
More actions
July 21, 2011 at 8:19 am

#241118

Hi,
I have to convert the following query into an 'if' or 'case' statement in a textbox expression:
If ARRIVAL_SCHEDULE_DT = ‘Today's date + 90 days' and VESSEL_NAME = 'BEZEE',
then display ‘N’
If LAST_UPDATE_DT = ‘Today's date' and VESSEL_NAME is either 'BEZEE' or ‘GBSOU’ or ‘MXVER’ or ‘USGLS’ or ‘USJAX’ or ‘USBAL’ or ‘USCHS’ or ‘USSSI then display ‘C’
If neither of the above applies then the textbox should be left blank.
Would appreciate your suggestions.
Regards,
Paul

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike Menser Hall of Fame Points: 3679 More actions · Answer 1

Untested -- try this case statement:

case when ARRIVAL_SCHEDULE_DT = DATEADD (month, 3, getdate()) and VESSEL_NAME = 'BEZEE' then 'N' else '' end +

case when LAST_UPDATE_DT = getdate() and VESSEL_NAME in('BEZEE','GBSOU','MXVER','USGLS','USJAX','USBAL','USCHS','USSSI') then 'C' else '' end as DateStatus

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

CASE http://msdn.microsoft.com/en-us/library/ms181765.aspx

pwalter83 SSChampion Points: 14575 More actions · Answer 3

Hi,

Thanks for your reply. I just needed one clarification- What does '+' signify in the syntax that you have mentioned ?

case when ARRIVAL_SCHEDULE_DT = DATEADD (month, 3, getdate()) and PORT_CD = 'BEZEE' then 'N' else '' end +case when MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT = getdate() and PORT_CD in ('BEZEE','GBSOU','MXVER','USGLS','USJAX','USBAL','USCHS','USSSI') then 'C' else '' end) as DateStatus

Mike Menser Hall of Fame Points: 3679 More actions · Answer 4

The + symbol means you are adding to the string which you are building. We are doing 2 different case statements both outputting to the same string. So it is the value of the first case statement PLUS the value of the second, however, only one case statement if any will return something, the other will return nothing (that is what the '' signifies). So we are stringing together the values and placing them in one string regardless of if something is returned or not.

pwalter83 SSChampion Points: 14575 More actions · Answer 5

Mike Menser (7/21/2011)
The + symbol means you are adding to the string which you are building. We are doing 2 different case statements both outputting to the same string. So it is the value of the first case statement PLUS the value of the second, however, only one case statement if any will return something, the other will return nothing (that is what the '' signifies). So we are stringing together the values and placing them in one string regardless of if something is returned or not.

Thanks a lot for your explanation and your earlier solution !! It really worked well !! Appreciate your precious time and effort.

pwalter83 SSChampion Points: 14575 More actions · Answer 6

Mike Menser (7/21/2011)
Untested -- try this case statement:
case when ARRIVAL_SCHEDULE_DT = DATEADD (month, 3, getdate()) and VESSEL_NAME = 'BEZEE' then 'N' else '' end +
case when LAST_UPDATE_DT = getdate() and VESSEL_NAME in('BEZEE','GBSOU','MXVER','USGLS','USJAX','USBAL','USCHS','USSSI') then 'C' else '' end as DateStatus

Sometime back you gave me the above solution for a problem. Your solution is correct, however, there is a requirement to add another condition- if there is a clash of dates and 'NC' is displayed on the report, then only 'N' should show up instead of 'NC'.

Is there a way to do this ? Would appreciate if somebody else could also give their views.

Thanks,

Paul