Got a case statement looking at 4 different frequency of appointments (daily, weekly, fortnight and Monthly) then looking at duration in weeks, then working out the scheduled end date depending on the weeks duration.
The output is date time, but I just want date, so I tried cast as DATE but it doesn't work. Can anyone suggest how to do this or spot an error in my code:
CASE WHEN Frequency = 'fortnight' THEN CAST(dateadd(week, [Weeks Duration]*2, [Entered Date]) AS DATE)
'Weeks duration' = an INT say 2, meaning 2 weeks duration
'Entered date' = when the appointment schedule starts.
Frequency = how frequent appointment is i.e.daily, weekly, fortnightly, Monthly etc.
The 2nd question I have is:
One of the duration's will be monthly intervals, but I realise that there is not exactly 4 weeks in every month, so how can I determine what the end date is dynamically using date add, or whatever is suitable?.
dateadd(week, [Weeks Duration]/4, [Entered Date])
The weeks duration is not dependent on the frequency, i.e. if weeks duration is 8 weeks and frequency is monthly then that would effectively mean 2 appointments, not 8 as it's based on weeks, so I change weeks duration from 8 to 2, effectively saying 2 appointments in 2 Month, or 8 week period.