SQLTeam.com | Weblogs | Forums

Frequency of appointments calculation and Cast to Date in CASE Statement

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.

Thanks

Andrew

The case statement is missing Else/End, otherwise it looks correct. The second part has a lot of assumptions; 8 weeks with frequency of 2 months = 2 appointments. How do you determine end date is using monthly? Is it something like end of month. Sample data and expected results will help

I wasn't sure what to put in ELSE as I am putting a case statement for each frequency so I just put NULL for ELSE - sorry I missed pasting that bit in. The CAST bit is me experimenting to remove the time value of the returned value so not applied it to all of the case statement.

CASE WHEN Frequency = 'freq_fnght' THEN CAST(dateadd(week, [Weeks Duration]*2, [Entered Date]) AS DATE)
	WHEN Frequency = 'freq_week' THEN dateadd(week, [Weeks Duration], [Entered Date])
	WHEN Frequency = 'Freq_Daily' THEN dateadd(week, [Weeks Duration]/7, [Entered Date])
	WHEN Frequency = 'freq_mnth' THEN dateadd(week, [Weeks Duration]*4, [Entered Date])
ELSE NULL
END AS [Schedule Ends]

The range is always entered from a list of numbers determined to be weeks, but then frequency is selected i.e. daily, week, fortnightly. Below is the table of frequency you can select.

Frequency

Some data from the one that works ok, 16 weeks duration, fortnightly frequency, start date 12-03-2020

Weeks Duration       Frequency         [Start Date]
--------------       ---------         -------------
16                    freq_fnght        2020-03-12 00:00:00.000

Expected Result from CASE statement as to when schedule should end:

Schedule Ends
-------------
2020-10-22 00:00:00.000

Further check of weeks date difference for start and end date

select datediff(week,'12-03-2020','22-10-2020')

32

Because it is fortnight frequency I multiply the No. of weeks value by 2 in the case statement.

As I'd said, I'm now trying to work out the calculation to put in CASE statement for Monthly, as a month isn't exactly 4 weeks.

As a business case the question would be I pick a schedule for say 25 weeks and a monthly frequency, how do I work out when that actually ends?

Hope that kind of clarifies it better as to what I am trying to achieve.

For the months calculation, you are multiplying the WeeksDuration by 4. From your description, the value in WeeksDuration is actually the number of months when they choose the Months Calculation. If that is the case, then why not just
dateadd(Month, [Weeks Duration], [Entered Date])

Really what I am trying to do is determine how many calendar weeks from start to end to work out the final date of the appointments. So it doesn't make sense to pick say 8 weeks and then say Monthly as that could be 8.5 weeks

I really need the business side to define what it is that was designed into it and what they imagine or assume this calculation to be (Have emailed them so we will see what they say), the form says "Duration in Weeks" but then you pick the frequency of appointments from a drop down, in this case if you pick Monthly, is that assumed to be Month * the duration In Weeks, which if we are assuming this is physical weeks doesn't really make sense as to how long the duration of appointments will be, plus you cannot calculate when the end date of last appointment will be, unless you assume the amount of weeks duration.

I'm just going to wait till they come back in response to my email about this because I'm making a lot of assumptions myself. The main thrust of the system was designed and then put on hold and the guy that done it left, so I'm imaging some more work was needed here to define the actual rules.