Need suggestions to write a date logic

Database
N/A

Need suggestions to write a date logic

HI All,

I am new to Teradata coding and to this forum. If i am not posting on the right place request the moderator to move accordingly. Thanks

I wanted to write the below logic in teradata coding.

Logic : if today minus 3 month falls on sunday then +1 else today minus 3 months falls on monday then +2 else today minus 3 months end.

i have written below logic but it is giving me error, i request some one please help me where i am missing or suggest me the best practices:

case when (CAST(CAST( CASE WHEN (cast (@Prompt('1) From Date (mm/dd/yyyy)','A',,,) as date format 'mm/dd/yyyy'))='01/01/1900' THEN (cast (ADD_MONTHS(DATE,-3)-EXTRACT(DAY FROM DATE)+1 as date format 'mm/dd/yyyy'))

    else cast (@Prompt('1) From Date (mm/dd/yyyy)','A',,,) as date format 'mm/dd/yyyy')  END AS FORMAT 'E4') AS CHAR(9)) WeekDay)='Friday' then (cast( CASE WHEN (cast (@Prompt('1) From Date (mm/dd/yyyy)','A',,,) as date format 'mm/dd/yyyy'))='01/01/1900' THEN ( CASE WHEN (cast (@Prompt('1) From Date (mm/dd/yyyy)','A',,,) as date format 'mm/dd/yyyy'))='01/01/1900' THEN (cast (ADD_MONTHS(DATE,-3)-EXTRACT(DAY FROM DATE)+1 as date format 'mm/dd/yyyy'))

    else cast (@Prompt('1) From Date (mm/dd/yyyy)','A',,,) as date format 'mm/dd/yyyy')  END+1  END)  else '2' end 

Thanks for your help in advance. Please let me know if you have any questions?

Tags (1)
2 REPLIES
N/A

Re: Need suggestions to write a date logic

Please 

Re: Need suggestions to write a date logic

Select 
CASE
WHEN (Day_Of_Week =1) then (ADD_MONTHS(Current_Date,-3) + 1) 
WHEN (Day_Of_Week =2) then (ADD_MONTHS(Current_Date,-3) + 2) 
ELSE ADD_MONTHS(Current_Date,-3)
END
From Sys_Calendar."CALENDAR"
Where Calendar_Date = ADD_MONTHS(Current_Date,-3)

--1 Sun --2 Mon --3 Tue --4 Wed --5 thur --6 Fri  --7 Sat

HTH!

Ranga