December 22, 2015 at 3:12 am
Hi, This is a follow on form the previous post. Its related but not directly so I have created a separate posting for this question.
I want to create a structure such that the user input directly into a table a row that records a series of client appointments with a ClientID, StartDate, ApptTime, Duration, Frequency (i.e. 1.daily or 2. weekly, 3. Biweekly, 4.once off) and an EndDate (or if it is to run indefinitely leave blank)
Called TableClientPOC.
Once entered I then need a procedure to 'transpose' the data into another table called TblAppointments and create a seperate entry for each appointment for up to 12 weeks in advance of today date.
So if its a daily appointment then there will be 7 * 12 = 84 entries.
If its a weekly appointment then it will be 1* 12 entries = 12 entries
and it its 2 weekly there will be 1 * 6 entries = 6 entries and for a once off appointment there will be only 1 entry! (Thats all provided the Appt 'EndDate' is after the 12 weeks or indefinite.
If 'EndDate' its less than the 12 week then all entries must cease at the End Date!
My thinking here is that it is easier for user to enter a range of dates and then populate a much larger table with the actual appointment, than the user having to repeatedly enter each day.
Once in the TblAppointments I then have a third table where I can allocate/deallocate the staff attending each appointment, the number of staff can be 1 ,2 or 3 and thus I am storing that information a table called TblApptStaff.
The reason for this is that I can quickly retrieve the time each member of staff has spent with a client for billing and payroll purposes.
Structure are as follows;
CREATE TABLE [TblClientPOC](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [nchar](10) NULL,
[StartDate] [date] NULL,
[ApptTime] [time](7) NULL,
[Duration] [int] NULL,
[Frequency] [int] NULL,
[EndDate] [date] NULL)
INSERT INTO [TblClientPOC]
([ClientID],[StartDate],[ApptTime],[Duration],[Frequency])
VALUES
(2528,'2015-01-20', '10:00',30,1),
(2528,'2015-01-20', '17:00',30,1)
(2528,'2015-01-25', '17:30',30,2)
(2528,'2015-01-30', '17:30',30,4)
CREATE TABLE [dbo].[TblAppointments](
[ID] [int] NOT NULL,
[OriginalApptID] [int] NULL,
[ClientID] [int] NULL,
[ApptDate] [date] NULL,
[ApptTime] [time](7) NULL,
[Duration] [int] NULL,
[Approved] [bit] NULL,
[ApprovedBy] [nvarchar](50) NULL,
[ApprovedWhen] [datetime] NULL,
So I am asking is there a better way to structure this and what is the best way yo transpose the data in bulk to enter into the new table?
I was thinking using a tally table fro the dates and then bulk select non empty date and insert into new table!
All suggestions appreciated
kind regards
December 22, 2015 at 10:13 am
I think the structure is good overall.
Here are some possible adjustments to consider:
TblClientPOC
1) Add a description / comments column
2) I'd get rid of the identity column here -- contrary to popular myth, there is no db rule which requires every table to have an identity column
3) Presumably no need for the clientid to be unicode, unless you assign special chars to your own id (??)
4) Can't imagine why you'd need to schedule to the fraction of a second -- time(0) should be fine.
5) Can't imagine duration needing to be more than smallint.
6) Can't imagine frequency needing to be more than tinyint.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 22, 2015 at 4:45 pm
I'd suggest some changes to the design:
CREATE TABLE [ClientApptSeries](
[ID] [int] IDENTITY(1,1) NOT NULL, -- it's good to have a unique ID to use it in JOINs
[ClientID] int NOT NULL, -- do not use char data typed for ID columns. They're horribly slow in JOINs comparing to integers
[StartDate] [DATE] NOT NULL, -- do not allow NULLs where they should not be allowed
[ApptTime] [time](7) NOT NULL,
[Duration] [int] NULL, -- may be null if you have some kind of "default" duration
[Frequency] [int] NOT NULL,
[EndDate] [date] NULL
PRIMARY KEY NONCLUSTERED (ID),
CONSTRAINT FK_ClientApptSeries_Client FOREIGN KEY (ClientID) REFERENCES dbo.Client(ID) -- I assume you have such a table
)
CREATE CLUSTERED INDEX CX_ClientApptSeries ON [ClientApptSeries](StartDate, ClientID)
CREATE TABLE [dbo].[Appointment](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientApptSeriesID] [int] NOT NULL,
-- [ClientID] [int] NULL, not needed, you know it from [ClientApptSeries]
[ApptDateTime] [datetime2] NOT NULL, -- must be [datetime], not separate date and time; you'll thank me later
[Duration] [INT] NOT NULL, -- I'd rather use datetime, but it's a matter of personal preferences
PRIMARY KEY NONCLUSTERED (ID),
UNIQUE CLUSTERED (ClientApptSeriesID, [ApptDateTime]) -- cannot have 2 appointments for the same client scheduled for the same time
FOREIGN KEY ([ClientApptSeriesID]) REFERENCES [ClientApptSeries](ID)
)
/*
"Approved" is not the only status you'll need to apply to appointments.
So you better move that part into a separate table:
*/
CREATE TABLE dbo.Appointment_Status (
AppointmentID INT NOT NULL,
TimeRecorded DATETIME2 not null DEFAULT(GETDATE()),
StatusID SMALLINT NOT NULL, -- refers to statuses like "Approved", "Scheduled", "Committed", "Finished", etc.
UserID INT NOT NULL, -- entry from a "User" table, must be a staff member (?)
PRIMARY KEY (AppointmentID, TimeRecorded),
UNIQUE KEY (TimeRecorded, AppointmentID),
FOREIGN KEY (StatusID) REFERENCES dbo.Status(ID)
)
It's just a draft.
Use of Tally table to populate Appointments from ApptSeries seems quite appropriate.
And you must consider an option to change an appointment without changing the whole series, or change the whole schedule of appointments which results in removing and re-populating of the whole set of correlated appointments.
_____________
Code for TallyGenerator
December 22, 2015 at 6:30 pm
Hi Guys,
Thank you so much for the replies its a great help. Your right I have also been thinking how to delete/amend a series of appointments or just 1 appt.
I have the so code to produce the three tally tables I think I will require.
First to give me all dates between 2 dates for daily appointments.
The second to give me all dates a week apart between 2 dates for weekly appointments.
Thirdly two weekly apart dates between 2dates.
For one off appointments there is no tally table required.
I will post the code tomorrow morning for your perusal and advice.
Kind regards
December 23, 2015 at 3:29 am
Hi, As promised here is the code I have prepared to give me the 3 tally tables I may need.
The first Tally example uses a number of cte's that (as far as I can tell!) allows the possible growth to 10,000 rows, which I think is some kind of sql limit!) to create all the dates I need.
Example 1
Declare
@SDate Date = '2015-06-01',
@EDate Date = '2015-12-31';
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
)
SELECT TOP(DATEDIFF( dd, @SDate, @EDate) + 1)
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @SDate) calDate
FROM E4
END
But here the same output with shorter code
Example 2
DECLARE @dt1 Datetime='2015-06-01'
DECLARE @dt2 Datetime='2015-12-31';
WITH
DailyDates
AS
(SELECT [Dates]= @dt1
UNION ALL
SELECT [dates] + 1
FROM DailyDates
WHERE [dates] + 1 <= @dt2)
SELECT CONVERT(date,[dates]) AS [Daily Dates]
FROM DailyDates
OPTION (maxrecursion 0)
END
Output for both are the same, but which one should I use? I like the look of the last one as its easier to read for me anyways!
For Tally table 2 for all dates a week apart I have the same dilemma as above. I can use Example 1 and change 'dd' to 'ww', or Example 2 and
But for Tally table3 where I need 2 weeks apart (known as a fortnight here) the DATEADD function does not do 2 weeks so I opted for
DECLARE @dt1 Datetime='2015-06-15'
DECLARE @dt2 Datetime='2015-12-31';
WITH
TwoWeeklyDates
AS
(SELECT [Dates]= @dt1
UNION ALL
SELECT [dates] + 14
FROM TwoWeeklyDates
WHERE [dates] + 14 <= @dt2)
SELECT CONVERT(date,[dates]) AS [Two Weekly Dates]
FROM TwoWeeklyDates
OPTION (maxrecursion 0)
END
I am not quite sure which one of the 2 examples to use and why so any advice is appreciated.
Obviously once I have my Tally tables I left join with my TblClientPOC table and INSERT into TblAppointments!
December 23, 2015 at 6:05 am
My advise - create a static Calendar table:
N int,
[Date] date,
DayOfWeek tinyint,
WeekNumber,
LastWeekdayOfMonth tinyint
etc.
Whatever property of a date you may need in your Scheduling tool.
Populate it once, using Tally table.
Calculate and store all the properties.
Use whatever slow and ineffective code you wish, loops, cursors, correlated subqueries, does not matter - you need to do it only once.
64k rows of Calendar table cover 176 years and occupy about 2MB of disk space.
Create as many indexes as you want - the tabe is not gonna be updated ever.
Even yoyu create 20 different covering indexes on every occasion they will take other 5MB, OK, may be 10MB of disk space.
Anyway - it's nothing.
And forget about those tricky CTE's once and forever.
_____________
Code for TallyGenerator
December 24, 2015 at 5:56 pm
Hi SSCarpal,
I love CTE they make perfect sense to me and I have all of my sp done that way already...so there's no going back...
But thanks for commenting anyways...
December 25, 2015 at 4:38 am
Tallboy (12/24/2015)
Hi SSCarpal,I love CTE they make perfect sense to me and I have all of my sp done that way already...so there's no going back...
But thanks for commenting anyways...
Tallboy,
For some reason you've chosen the worst option of them all.
You use recursive CTE's which are the worst performers amongst all possible options. Even a WHILE loop does better.
They have nothing to do with Tally table.
Having them in every procedure is definitely a path to killing the server.
Before it's too late - go back to the drawing board, learn how to do calendars and schedules in an effective way and redesign your solutions.
Before it's too late...
_____________
Code for TallyGenerator
December 25, 2015 at 4:54 am
Tallboy (12/23/2015)
But for Tally table3 where I need 2 weeks apart (known as a fortnight here) the DATEADD function does not do 2 weeks so I opted for
DECLARE @dt1 Datetime='2015-06-15'
DECLARE @dt2 Datetime='2015-12-31';
WITH
TwoWeeklyDates
AS
(SELECT [Dates]= @dt1
UNION ALL
SELECT [dates] + 14
FROM TwoWeeklyDates
WHERE [dates] + 14 <= @dt2)
SELECT CONVERT(date,[dates]) AS [Two Weekly Dates]
FROM TwoWeeklyDates
OPTION (maxrecursion 0)
END
I am not quite sure which one of the 2 examples to use and why so any advice is appreciated.
How about this?
SELECT DATEADD(dd, 14*N, @d1) FortnightDays
From Tally
Where N>= 0
And N<=DATEDIFF(dd, @d1,@d2)/14
The code looks a bit shorter to me.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply