I have a table
SlideList that defines all available slides that can be put into a PowerPoint presentation. I have a table
ReportList that defines which slides go into a presentation for a company and the order they're to be inserted.
I've been maintaining
ReportList by hand (yes, that's a nightmare and needs to be fixed, but it's not the point of this post), so upon initial creation of a report, I've left gaps of 10 in the
SlideOrder so it's easy to insert additional slides without having to renumber everything by hand.
I've written the following T-SQL to identify the
SlideOrder value to use when inserting a new slide immediately after an existing slide for every report that has that slide in it.
For example: I have existing slide
Foo, and I've created a new slide
Bar. I need to insert
Bar as the next slide after
Foo in every report that contains
Foo. Some reports may have
Foo as slide #50, others as slide #302 - this code will figure that out, split the gap to the next slide, and return that value as the
Bar. If there is no gap (
SlideOrder #50, and
Baz exists at
SlideOrder #51) it will simply print a message and I'll have to adjust that one by hand.
Here is my T-SQL code. While it's functional, I'm looking for improvements to this code and to learn more T-SQL. If you're feeling really generous and want to rewrite it to update all the
SlideOrder values >
Foo so that
Baz can be inserted and there are no gaps in
SlideOrder, go for it! I'll be very grateful, but that's not what I'm after at the moment. (Note that this code simply prints out the
Bar will go in, and the
SlideOrder of what follows - it doesn't do the actual insert.)
USE DevDB GO DECLARE @SlideToFollow as nvarchar(300) SELECT @SlideToFollow = 'Foo' DECLARE @SlideToFollowID as int SELECT @SlideToFollowID = SlideID FROM ReportList RL inner join SlideList SL on RL.SlideID = SL.ID WHERE SlideName = @SlideToFollow DECLARE @CurrentClinicID as int SELECT @CurrentClinicID = min(ClinicID) FROM ReportList WHERE SlideID = @SlideToFollowID WHILE @CurrentClinicID is not null BEGIN DECLARE @Slide1QOrder as int SELECT @Slide1QOrder = SlideOrder FROM ReportList WHERE SlideID = @SlideToFollowID and ClinicID = @CurrentClinicID DECLARE @NextSlideOrder as int SELECT @NextSlideOrder = min(SlideOrder) FROM ReportList WHERE ClinicID = @CurrentClinicID AND SlideOrder > @Slide1QOrder IF @NextSlideOrder = @Slide1QOrder + 1 PRINT N'ClinicID: ' + Cast(@currentClinicID as nvarchar) + ' does not have room to insert a new slide after order: ' + cast(@Slide1QOrder as nvarchar); ELSE BEGIN DECLARE @NewSlideOrder as int SELECT @NewSlideOrder = @Slide1QOrder + (@NextSlideOrder - @Slide1QOrder)/2 PRINT N'ClinicID: ' + Cast(@CurrentClinicID as nvarchar) + ' ' + @SlideToFollow + ' slide order: ' + Cast(@Slide1QOrder as nvarchar) + ' New Slide Order: ' + Cast(@NewSlideOrder as nvarchar) + ' Next Slide Order: ' + Cast(@NextSlideOrder as nvarchar); END SELECT @CurrentClinicID = min(ClinicID) FROM ReportList WHERE SlideID = @SlideToFollowID AND ClinicID > @CurrentClinicID END
CREATE TABLE [dbo].[SlideList]( [ID] [int] IDENTITY(1,1) NOT NULL, [SlideName] [nvarchar](50) NULL, [SlideDesc] [nvarchar](255) NULL, [ExcelWksht] [nvarchar](255) NULL GO
CREATE TABLE [dbo].[ReportList]( [ID] [int] IDENTITY(1,1) NOT NULL, [ClinicID] [int] NULL, [SlideID] [int] NULL, [SlideOrder] [int] NULL GO