January 6, 2016 at 10:14 am
I have a Table called TblClientPOCV2 with fields as follows
CREATE TABLE [dbo].[TblClientPOCV2](
[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,
[StaffID] [int] NULL,
CONSTRAINT [PK_TblClientPOCV2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Test Data
INSERT INTO [TblClientPOCV2]
([ClientID]
,[StartDate]
,[ApptTime]
,[Duration]
,[Frequency]
,[EndDate]
,[StaffID]
VALUES
(2528,'2015-12-28','10:00',30,1,'2016-03-24',2200176)
(2528,'2015-12-28','15:00',45,1,'2016-03-24',2200176)
GO
I want to take each row in turn and create a number of row for each date between the StartDate and the EndDate
and INSERT into TblAppointments
CREATE TABLE [dbo].[TblAppointments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NOT NULL,
[ApptDate] [date] NOT NULL,
[ApptTime] [time](4) NOT NULL,
[StaffID] [int] NOT NULL,
[Duration] [int] NULL,
CONSTRAINT [PK_TblAppointments] PRIMARY KEY CLUSTERED
I tried creating a calendar of date and then inserting the above data using a cursor but isn't working well and taking a long time.
Can it be done with set?
With the above test data we should end up with 48 new rows in the TblAppointments!
Thanking you
January 6, 2016 at 1:00 pm
With the above test data we should end up with 48 new rows in the TblAppointments!
sorry bit confused......does the frequency indicate a single day or every day.
perhaps if you helped by providing your expected results please
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 6, 2016 at 1:28 pm
This can easily be done using a tally table. The problem is that it is not clear what you want to do here. You say there should be 48 new rows from your sample data. I can't begin to figure that out. If you created an appointment every day there would be 87 new appointments for each of your sample rows of data. Can you explain where the number 48 comes from? Is this excluding weekends and holidays? If so, you need to add a calendar table to the mix in addition to a tally table. Help us understand the details of the problem and we can help you find a workable solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 6, 2016 at 2:52 pm
Hi, Yes in this case Frequency is 1 which means a date for every date between the two dates and the number of new rows inserted into TblAppointment should be 56 rows (not 48) as there are 28 days between the StartDate and EndDate and there are 2 rows in the test data. Yes weekend etc are included.
Example out put would be
2528,'2015-12-28','10:00',30,2200176
2528,'2015-12-29','10:00',30,2200176
2528,'2015-12-30','10:00',30,2200176
2528,'2015-12-31','10:00',30,2200176
2528,'2016-01-01','10:00',30,2200176
....
....
....
2528,'2016-01-24','15:00',45,2200176
2528,'2015-12-28','15:00',45,2200176
2528,'2015-12-29','15:00',45,2200176
2528,'2015-12-30','15:00',45,2200176
2528,'2015-12-31','15:00',45,2200176
2528,'2016-01-01','15:00',45,2200176
....
....
....
2528,'2016-01-24','15:00',45,2200176
January 6, 2016 at 3:26 pm
Tallboy (1/6/2016)
Hi, Yes in this case Frequency is 1 which means a date for every date between the two dates and the number of new rows inserted into TblAppointment should be 56 rows (not 48) as there are 28 days between the StartDate and EndDate and there are 2 rows in the test data. Yes weekend etc are included.Example out put would be
2528,'2015-12-28','10:00',30,2200176
2528,'2015-12-29','10:00',30,2200176
2528,'2015-12-30','10:00',30,2200176
2528,'2015-12-31','10:00',30,2200176
2528,'2016-01-01','10:00',30,2200176
....
....
....
2528,'2016-01-24','15:00',45,2200176
2528,'2015-12-28','15:00',45,2200176
2528,'2015-12-29','15:00',45,2200176
2528,'2015-12-30','15:00',45,2200176
2528,'2015-12-31','15:00',45,2200176
2528,'2016-01-01','15:00',45,2200176
....
....
....
2528,'2016-01-24','15:00',45,2200176
uummmm....start date is 2015-12-28 and the enddate is 2016-03-24. That is 87 days. Assuming you want a row for each appointment time this is 174 rows. Now in your desired output it looks like maybe it should be ending on 2016-01-24 but that is 31 days or a total of 62 rows. Something here just doesn't line up.
I would use a tally table and DATEADD where tally.N < DATEDIFF(day, StartDate, Endate)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 6, 2016 at 7:37 pm
Something along these lines should do the trick...
IF OBJECT_ID('tempdb..#TblClientPOCV2', 'U') IS NOT NULL
DROP TABLE #TblClientPOCV2;
CREATE TABLE #TblClientPOCV2 (
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
ClientID NCHAR(10) NULL,
StartDate DATE NULL,
ApptTime TIME(7) NULL,
Duration INT NULL,
Frequency INT NULL,
EndDate DATE NULL,
StaffID INT NULL,
);
INSERT #TblClientPOCV2 (ClientID,StartDate,ApptTime,Duration,Frequency,EndDate,StaffID) VALUES
(2528,'2015-12-28','10:00',30,1,'2016-03-24',2200176), (2529,'2015-12-28','15:00',45,1,'2016-03-24',2200176);
--==================================================================
IF OBJECT_ID('tempdb..#TblAppointments', 'U') IS NOT NULL
DROP TABLE #TblAppointments;
CREATE TABLE #TblAppointments (
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
ClientID INT NOT NULL,
ApptDate DATE NOT NULL,
ApptTime TIME(4) NOT NULL,
StaffID INT NOT NULL,
Duration INT NULL
);
INSERT #TblAppointments (ClientID,ApptDate,ApptTime,StaffID,Duration)
SELECT
tcp.ClientID,
Dates.ApptDate,
tcp.ApptTime,
tcp.StaffID,
tcp.Duration
FROM
#TblClientPOCV2 tcp
CROSS APPLY (
SELECT TOP (DATEDIFF(dd, tcp.StartDate, tcp.EndDate) + 1)
ApptDate = DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1, tcp.StartDate)
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n1 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n2 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n3 (n)
) Dates;
SELECT * FROM #TblAppointments ta;
Edit: As a side note... There is an implicit conversion happening because you have ClientID defined an NCHAR(10) in the client table and as an INT in the appointments table. Be sure to define them both as INTs in the final design.
January 7, 2016 at 3:21 am
Yes Sean, my bad the test data dates should have been...
INSERT INTO [TblClientPOCV2]([ClientID],[StartDate],[ApptTime],[Duration],[Frequency],[EndDate],[StaffID]
VALUES
(2528,'2015-12-28','10:00',30,1,'2016-01-24',2200176)
(2528,'2015-12-28','15:00',45,1,'2016-01-24',2200176)
Each 28 days apart.
Hi Sean, Thank you for your solutions looks great will test later and let you know! Your correct about the ClientID it should be int in both cases, dont know how that crept in their but thanks for pointintg it out!
January 7, 2016 at 3:57 am
Hi, Sean solution is correct but I have failed to make it clear what I am looking for. Its the old adage "Thats what I asked for, but its not what I'm looking for":crazy:
To clarify the Start and End Dates in TblClientPOCV2 can be different for each row, therefor in 1 row they may be 24 days apart in another 7 days apart there a total of 31 new rows should be added tot he TblAppointments.
Correct Test Data shoudl be...
INSERT INTO [TblClientPOCV2]([ClientID],[StartDate],[ApptTime],[Duration],[Frequency],[EndDate],[StaffID]
VALUES
(2528,'2015-12-28','10:00',30,1,'2016-01-24',2200176)
(2528,'2016-01-07','15:00',45,1,'2016-01-13',2200176)
Adding 31 new rows...quite a bit trickier!
January 7, 2016 at 5:14 am
Since you already have a calendar table, generating the rows to be inserted should be easy with a join:
INSERT INTO dbo.Appointments (column list)
SELECT s.ClientID, c.Date, other, columns
FROM dbo.SourceTable AS s
INNER JOIN dbo.Calendar AS c
ON c.Date BETWEEN s.StartDate AND s.EndDate;
(If you use datetime columns, the join condition becomes a bit more complex)
January 7, 2016 at 7:28 am
Hi Hugo,
Your right, but every row from the source table could and will have different dates , therefor I need to use a cursor to iterate over them! Which will be slow!
That's why I was asking could it be done in sets! I think not tho!
January 7, 2016 at 7:37 am
Tallboy (1/7/2016)
Hi Hugo,Your right, but every row from the source table could and will have different dates , therefor I need to use a cursor to iterate over them! Which will be slow!
That's why I was asking could it be done in sets! I think not tho!
You do not need a cursor for this. Did you try what Hugo posted? What he posted is exactly what you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2016 at 7:55 am
Tallboy (1/7/2016)
Hi, Sean solution is correct but I have failed to make it clear what I am looking for. Its the old adage "Thats what I asked for, but its not what I'm looking for":crazy:To clarify the Start and End Dates in TblClientPOCV2 can be different for each row, therefor in 1 row they may be 24 days apart in another 7 days apart there a total of 31 new rows should be added tot he TblAppointments.
Correct Test Data shoudl be...
INSERT INTO [TblClientPOCV2]([ClientID],[StartDate],[ApptTime],[Duration],[Frequency],[EndDate],[StaffID]
VALUES
(2528,'2015-12-28','10:00',30,1,'2016-01-24',2200176)
(2528,'2016-01-07','15:00',45,1,'2016-01-13',2200176)
Adding 31 new rows...quite a bit trickier!
are you sure you have your row count correct....I think it should be 35
assuming your dates are inclusive, then '2015-12-28' to '2016-01-24' is 28 days,
'2016-01-07' to '2016-01-13 is 7 days (which are contained in the upper and lower boundaries above) but will still add 7 rows
28 + 7 = 35 ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 7, 2016 at 10:05 am
Tallboy (1/7/2016)
Hi Hugo,Your right, but every row from the source table could and will have different dates , therefor I need to use a cursor to iterate over them! Which will be slow!
That's why I was asking could it be done in sets! I think not tho!
My solution joins each row to the Calendar table, generating the correct series of rows for every row. Even (especially!) if they are different.
If you tested it and found it not to work, then please post a full repro script (create table, insert, select - i.e. we should be able to cut, paste, and run), plus expected results, and I'll have another look.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply