Scheduled date of appointments for different frequency of appointment

Transact-SQL
1

Most of our appointments are weekly schedule, but some of them are fortnightly, and some monthly. I have a small script that generates the weekly scheduled day, in this case a Tuesday, weekday 3, using date dimension table.

There is a flag in the appointments table that highlights if the schedule is fortnightly, monthly or weekly, and if it is null it gives weekly schedule date at the moment.

A min date is generated in the code and this is used in the filter to set the date range to look under and generates the weekly dates for scheduled appointments.

I'm trying to figure out a way to set the dates to fortnightly and monthly when the flag highlights it but can't seem to get it to work.

This is a simplified example of the query and the data:

 select a.AppReason, a.AssessmentDate, a.PersonID, a.Frequency, a.Weeks, dd.date AS Scheduled_date
from 
	(select scht.AppReason
	      , scht.AssesstDate
	      , scht.PersonID
	      , scht.Frequency
	      , scht.Weeks
	      , MIN(scht.WeekCommence1) min_date
	from view_ScheduledTuesday scht
	group by scht.AppReason, scht.AssesstDate, scht.PersonID, scht.Frequency, scht.Weeks
	) a
cross join dbo.DateDimension dd
where dd.DayOfWeek = 3  
and dd.date BETWEEN a.min_date AND GETDATE()
AppReason AssessmentDate ClientID Frequency1 Weeks1 Scheduled_date
Loan_Review 2020-02-25 16:43:00.000 9876547 freq_week 7 2020-03-17
Cheque_Clr 2020-02-25 16:43:00.000 9876547 freq_week 7 2020-03-24
Savings_Rev 2020-02-25 16:43:00.000 9876547 freq_week 7 2020-03-31

But if the scheduled was fortnightly as below it should be different schedule day
:

AppReason AssessmentDate ClientID Frequency1 Weeks1 Scheduled_date
Loan_Review 2020-02-25 16:43:00.000 9876547 freq_fnight 7 2020-03-17
Cheque_Clr 2020-02-25 16:43:00.000 9876547 freq_fnight 7 2020-03-24
Savings_Rev 2020-02-25 16:43:00.000 9876547 freq_fnight 7 2020-03-31

First date ok as first date of schedule
Second date in bold Should be 2020-03-31
Third date in bold Should be 2020-04-14

Not entirely sure how to include all of the conditions into the query, as I said it defaults to weekly schedule date, so some sort of alteration to also work with the other schedules is needed.

Thanks
G

2

hi

seeing your post .. i tried to come up with .. sample data ..it WORKED ..
please have a look and see if it helps you !!!

please click arrow to the left for Sample data 
----------------------
-- create table 

create table schedule
(
schedule_type varchar(100),
schedule_days_add int 
)

create table Sample_Data
(
appointment_date date , 
schedule_type varchar(100)
)
go 

---------------------------
-- insert into data 

insert into schedule select 'Weekly',7
insert into schedule select 'FortNightly',14

insert into Sample_Data select '2010-01-12','Weekly'
insert into Sample_Data select '2009-07-15','Weekly'
insert into Sample_Data select '2015-05-03','Weekly'
insert into Sample_Data select '2013-09-12','FortNightly'
insert into Sample_Data select '2019-02-09','FortNightly'
insert into Sample_Data select '2007-06-01','FortNightly'

go

select 
      'SQL Output'
	, appointment_date
	, a.schedule_type
	, dateadd(dd,schedule_days_add,appointment_date) as Scheduled_date 
from 
      Sample_Data a 
	     join 
	  Schedule b 
	     on a.schedule_type = b.schedule_type

image
image431×705 13 KB

1 Like
3

Thanks, I see what you've done there and it gave me a bit more of an idea what to do. The query was a bit more complex as I also had to consider weeks duration as well, so have re-wrote it using case statements with similar to what you've shown, this looks at the different scenarios and now works as I want it to.