About this question
In got a programming task in the area of T-SQL. Task: People want to get inside an elevator every person has a certain weight. The order of the people waiting in line is determined by the column turn. The elevator has a max capacity of <= 1000 lbs. Return the last person's name that is able to enter the elevator before it gets too heavy! Return type should be table
Question: What is the most efficient way to solve this problem? If looping is correct is there any room for improvement?
I used a loop and # temp tables, here my solution:
set rowcount 0 -- THE SOURCE TABLE "LINE" HAS THE SAME SCHEMA AS #RESULT AND #TEMP use Northwind go declare @sum int declare @curr int set @sum = 0 declare @id int IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL DROP TABLE #temp IF OBJECT_ID('tempdb..#result','u') IS NOT NULL DROP TABLE #result create table #result( id int not null, [name] varchar(255) not null, weight int not null, turn int not null ) create table #temp( id int not null, [name] varchar(255) not null, weight int not null, turn int not null ) INSERT into #temp SELECT * FROM line order by turn WHILE EXISTS (SELECT 1 FROM #temp) BEGIN -- Get the top record SELECT TOP 1 @curr = r.weight FROM #temp r order by turn SELECT TOP 1 @id = r.id FROM #temp r order by turn --print @curr print @sum IF(@sum + @curr <= 1000) BEGIN print 'entering........ again' --print @curr set @sum = @sum + @curr --print @sum INSERT INTO #result SELECT * FROM #temp where [id] = @id --id, [name], turn DELETE FROM #temp WHERE id = @id END ELSE BEGIN print 'breaking.-----' BREAK END END SELECT TOP 1 [name] FROM #result r order by r.turn desc
Here the Create script for the table I used Northwind for testing:
USE [Northwind] GO /****** Object: Table [dbo].[line] Script Date: 28.05.2018 21:56:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[line]( [id] [int] NOT NULL, [name] [varchar](255) NOT NULL, [weight] [int] NOT NULL, [turn] [int] NOT NULL, 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], UNIQUE NONCLUSTERED ( [turn] 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 ALTER TABLE [dbo].[line] WITH CHECK ADD CHECK (([weight]>(0))) GO INSERT INTO [dbo].[line] ([id], [name], [weight], [turn]) VALUES (5, 'gary', 800, 1), (3, 'jo', 350, 2), (6, 'thomas', 400, 3), (2, 'will', 200, 4), (4, 'mark', 175, 5), (1, 'james', 100, 6) ;
