July 21, 2011 at 8:19 am
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
July 21, 2011 at 8:28 am
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
July 21, 2011 at 8:42 am
July 21, 2011 at 9:00 am
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
July 21, 2011 at 9:15 am
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.
July 22, 2011 at 3:25 am
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.
August 23, 2011 at 9:20 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply