CODEWITHSUNDEEP
c#sql-server
Bill Smyth
Bill Smyth

Reputation: 23

Using C# to create a "monthly timetable/schedule calender" in a SQL table

For context I am creating a database for a vet clinic.

I have a SQL table in Microsoft SQL Server Management studio 2014 with relational model (schema) as follows:ScheduleSlot(s̲l̲o̲t̲I̲D̲, room, date, startTime, endTime, appointment_id, staff_id) that i would like to partially pre fill.

I would like to create a diary structure that allows you for each day to define the possible slots that a vet might be available for.

For example I would like to produce something like this for all 26 not Sunday days in May 2016:

slotId  date        startTime   endTime room    appointment_id  staff_id
1       5/2/2016    8:00        8:30    1       null            null
2       5/2/2016    8:00        8:30    2       null            null
3       5/2/2016    8:00        8:30    3       null            null
4       5/2/2016    8:00        8:30    4       null            null
5       5/2/2016    8:30        9:00    1       null            null
6       5/2/2016    8:30        9:00    2       null            null
7       5/2/2016    8:30        9:00    3       null            null
8       5/2/2016    8:30        9:00    4       null            null
9       5/2/2016    9:00        9:30    1       null            null
10      5/2/2016    9:00        9:30    2       null            null
11      5/2/2016    9:00        9:30    3       null            null
12      5/2/2016    9:30        9:30    4       null            null
...     ...         ...         ...     ...     ...             ...

For reference there should be 72(18 intervals x 4 room posiblites) slots per day for any month, and for the month May 2016 around 1872 slots (72 x 26).

My possible solution a using a modulo (loop) that checks if i % 7 == 0 and skips it? - The for loop could be passed a parameter as the first date and work until the end of the month.

Upvotes: 2

Views: 1051

Answers (3)

schlonzo
schlonzo

Reputation: 1406

If you really want to prefill a table with the desired values, you could use this

DECLARE @desiredMonth int = 5;
DECLARE @desiredYear int = 2016;

SELECT StartDate, 
DATEADD(minute, 30, StartDate) AS EndDate, 
rooms AS Room 
FROM
(
    SELECT 
    /* Construct StartDate */
    dateadd(minute, minutes, dateadd(hour, hours, dateadd(day, days - 1, dateadd(month, @desiredMonth - 1, dateadd(year, @desiredYear - 1900, 0))))) AS StartDate,
    minutes,
    rooms
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31)) AS W(days)
    CROSS JOIN (VALUES (8), (9), (10), (12), (12), (13), (14), (15), (16)) AS X(hours)
    CROSS JOIN (VALUES (00), (30)) AS Y(minutes)
    CROSS JOIN (VALUES (1), (2), (3), (4)) AS Z(rooms)
    /* Check, that only days are included for desired month */
    WHERE DATEPART(m, dateadd(hour, hours, dateadd(day, days - 1, dateadd(month, @desiredMonth - 1, dateadd(year, @desiredYear - 1900, 0))))) = @desiredMonth
) AS SubQuery01

But I agree with the other opinions, that usually you should not do such stuff. Maybe it's better for using in a view?

Upvotes: 0

Mahdi Farhani
Mahdi Farhani

Reputation: 1012

As @Patrick said Don't do this, because after a while you have lots of garbage data you should remove it.this kind of garbage would be make some trouble in reporting.

So, If I were you, I would create a table for scheduling like rooms,the duration,start time,end time,... and when i want to insert new appointment, i try to create new appointment by the schedule rule and the last appointment.

for example the last appointment is in 5/2/2016 at 13:00 in room #1, and in schedule table i know the duration of meeting is half and hour,

so i create new appointment for room #1 in 5/2/2016 at 14:30

Also you can find lots of solution to do this in the best way.

Upvotes: 0

Patrick
Patrick

Reputation: 5836

Don't do this.

Just have an appointments table that stores when appointments are scheduled.

SQL should not generally be responsible for business rules.

Do this in your middle tier, or wherever you are handling your business logic, prior to handing it to the database for storage.

If on any given day, you only have 2 appointments, you should only have 2 rows for that day. Not a bunch of rows with all available timeslots and NULLs. That is a waste of space and needless overhead.

If you want to force integrity from the database side, to ensure that nothing invalid is getting through from your data-access layer (such as appointments outside of business hours, or overlapping appointments), you can employ triggers and check constraints that check for valid logic, or send everything through a stored procedure that does the same.

Upvotes: 3

Related Questions