H2MmbxjY

1set rowcount 0
2-- THE SOURCE TABLE "LINE" HAS THE SAME SCHEMA AS #RESULT AND #TEMP
3use Northwind
4go
5
6declare @sum int
7declare @curr int
8set @sum = 0
9declare @id int
10
11IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
12    DROP TABLE #temp
13
14IF OBJECT_ID('tempdb..#result','u') IS NOT NULL
15    DROP TABLE #result
16
17create table #result( 
18    id int not null,
19    [name] varchar(255) not null,
20    weight int not null,
21    turn int not null
22)
23
24create table #temp( 
25    id int not null,
26    [name] varchar(255) not null,
27    weight int not null,
28    turn int not null
29)
30
31INSERT into #temp SELECT * FROM line order by turn
32
33 WHILE EXISTS (SELECT 1 FROM #temp)
34  BEGIN
35   -- Get the top record
36   SELECT TOP 1 @curr =  r.weight  FROM  #temp r order by turn  
37   SELECT TOP 1 @id =  r.id  FROM  #temp r order by turn
38
39    --print @curr
40    print @sum
41
42    IF(@sum + @curr <= 1000)
43    BEGIN
44    print 'entering........ again'
45    --print @curr
46      set @sum = @sum + @curr
47      --print @sum
48      INSERT INTO #result SELECT * FROM  #temp where [id] = @id  --id, [name], turn
49      DELETE FROM #temp WHERE id = @id
50    END
51     ELSE
52    BEGIN    
53    print 'breaaaking.-----'
54      BREAK
55    END 
56  END
57
58   SELECT TOP 1 [name] FROM #result r order by r.turn desc
59	
60USE [Northwind]
61GO
62
63/****** Object:  Table [dbo].[line]    Script Date: 28.05.2018 21:56:18 ******/
64SET ANSI_NULLS ON
65GO
66
67SET QUOTED_IDENTIFIER ON
68GO
69
70CREATE TABLE [dbo].[line](
71    [id] [int] NOT NULL,
72    [name] [varchar](255) NOT NULL,
73    [weight] [int] NOT NULL,
74    [turn] [int] NOT NULL,
75PRIMARY KEY CLUSTERED 
76(
77    [id] ASC
78)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
79UNIQUE NONCLUSTERED 
80(
81    [turn] ASC
82)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
83) ON [PRIMARY]
84
85GO
86
87ALTER TABLE [dbo].[line]  WITH CHECK ADD CHECK  (([weight]>(0)))
88GO
89	
90select a.id, sum(b.weight)
91from
92    table a
93left join 
94    table b
95on
96    a.turn > b.turn