March 11, 2015 at 1:26 pm
Hello all,
I have run into a perplexing issue with how to UPDATE some rows in my table correctly.
I have a Appointment table which has Appointment Times and a Appointment Names. However the Name is only showing on the Appt start Time line. I need it to show for its duration. So for example in my DDL Morning Appt only shows on at 8:00 I need it to show one each line until the next Appt Starts and so on. In this case Morning Appt should show at 8:00,8:15, 8:30. Have yet to wrap my head around how to do this.
CREATE TABLE #TEST ( ApptTime TIME, ApptName VARCHAR (20), DURATION TINYINT)
INSERT INTO #TEST VALUES ('8:00', 'Morning Appt', 45), ('8:15', NULL, NULL),('8:30', NULL,NULL),('8:45', 'Brunch Appt', 45),('9:00', NULL,NULL),('9:15', NULL,NULL),
('9:30', 'Afternoon Appt', 30),('9:45', NULL,NULL),('10:00', NULL,NULL)
SELECT * FROM #TEST
***SQL born on date Spring 2013:-)
March 11, 2015 at 2:00 pm
The best way to deal with this would be properly normalized data. This seems to me like it should have a table for appointments with 3 columns (StartTime, Duration, AppointmentName).
Then this becomes trivial to deal with using a tally table. I keep a cteTally as a view on most databases so I don't have to worry about creating them.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO
Now here is your code normalized and greatly simplified because you aren't fighting it.
create table #Appt
(
AppointmentName varchar(50)
, StartTime time
, Duration tinyint
)
insert #Appt (AppointmentName, StartTime, Duration)
VALUES ('Morning Appt', '08:00', 45), ('Brunch Appt', '08:45', 45), ('Afternoon Appt', '09:30', 30)
select *, DATEADD(MINUTE, (t.N - 1) * 15, a.StartTime)
from #Appt a
join cteTally t on t.N <= a.Duration / 15
order by StartTime
_______________________________________________________________
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/
March 12, 2015 at 9:05 am
Sean,
Thank you so much. This did work and provided my solution to a very hard to understand table structure and requested query I was dealing with. I have incorporated the TallyTable into my package and the customer is testing the report this morning.
On a side note if for some reason I could not use a Tally Table is there other ways to solve a issue like this ? I am on SQL server 2008 r2. Just trying to broaden my skill set!
Thanks again you saved me a lot of head pounding on the wall.:-D
***SQL born on date Spring 2013:-)
March 12, 2015 at 9:18 am
thomashohner (3/12/2015)
Sean,Thank you so much. This did work and provided my solution to a very hard to understand table structure and requested query I was dealing with. I have incorporated the TallyTable into my package and the customer is testing the report this morning.
On a side note if for some reason I could not use a Tally Table is there other ways to solve a issue like this ? I am on SQL server 2008 r2. Just trying to broaden my skill set!
Thanks again you saved me a lot of head pounding on the wall.:-D
Glad that worked for you. Honestly the tally table is probably the best way to deal with type of situation. As you discovered it is challenging with anything other than a loop. You could use a loop here but it is a lot more code, more difficult to understand and performs a lot worse. 😛
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply