4
\$\begingroup\$

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 SlideOrder for Bar. If there is no gap (Foo is 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 SlideOrder of Foo, the SlideOrder that 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

SlideList:

CREATE TABLE [dbo].[SlideList](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SlideName] [nvarchar](50) NULL,
    [SlideDesc] [nvarchar](255) NULL,
    [ExcelWksht] [nvarchar](255) NULL
GO

ReportList:

CREATE TABLE [dbo].[ReportList](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ClinicID] [int] NULL,
    [SlideID] [int] NULL,
    [SlideOrder] [int] NULL
GO
\$\endgroup\$
  • \$\begingroup\$ One thing I just discovered - I should probably be using SET @Local = (SELECT...) instead of just SELECT @Local = ... \$\endgroup\$ – FreeMan Jun 12 '18 at 14:49
  • \$\begingroup\$ So confusing. I bet row_number() could be your friend. Or a recursive CTE. You should post a better explanation of what you need to do with some data and desired output on dba.stackexchange \$\endgroup\$ – paparazzo Jun 12 '18 at 15:05
  • \$\begingroup\$ @paparazzo - can you be a bit more explicit? What's confusing you? In what way would Row_number be my friend? \$\endgroup\$ – FreeMan Jun 12 '18 at 15:07
  • \$\begingroup\$ Follow my suggestion to post on dba or not. \$\endgroup\$ – paparazzo Jun 12 '18 at 15:10
  • \$\begingroup\$ Ah. Somehow, your edit hadn't appeared when I posted my response. If I don't get satisfaction here in the next couple of days, I'll certainly move to Database Administrators \$\endgroup\$ – FreeMan Jun 12 '18 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.