question

Hi, If GlbStock greater then M1 then it will perform this @TDoc=@TDoc+30 and GlbStock=GlbStock-M1 check if remianing GlbStock is greater then M2 then it will perform this @TDoc=@TDoc+30 and GlbStock=GlbStock-M2 and it continue the comparison till GlbStock become less then Month Once GlbStock become less them Month it will perform @IncDoc=GlbStock/Month*30 @TDoc=@TDoc+@IncDoc and stop the calculation Row Suppose GlbStock is become less then M3 then @IncDoc=GlbStock/M3*30 The below calculation is working perfectly but as the update is performing row by row it is time consuming. I have more then 50L records, so it takes more then 8hr to update Is there any alternate way to implement above case Below is the code : CREATE TABLE [dbo].[StockDetails]( [Name] [varchar](100) NULL, [GlbStock] [int] NULL, [M1] [int] NULL, [M2] [int] NULL, [M3] [int] NULL, [M4] [int] NULL, [M5] [int] NULL, [M6] [int] NULL, [M7] [int] NULL, [M8] [int] NULL, [M9] [int] NULL, [M10] [int] NULL, [M11] [int] NULL, [M12] [int] NULL, ) INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'ABC', 100, 10, 50, 60, 20, 80, 90, 10, 20, 80, 90, 10, 10) INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'XYZ', 20, 50, 20, 10, 20, 30, 10, 80, 90, 10, 40, 50, 60) INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'PQR', 1000, 10, 50, 60, 70, 10, 10, 20, 30, 40, 20, 10, 10) INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'PQR', 0, 10, 20, 30, 40, 50, 80, 10, 2, 0, 0, 0, 0) INSERT [dbo].[StockDetails] ([Name], [GlbStock], [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8], [M9], [M10], [M11], [M12]) VALUES (N'P', 1, 10, 20, 30, 40, 50, 80, 10, 2, 0, 0, 0, 0) CREATE TABLE #STOCK_DETAIL ( RowID int IDENTITY(1, 1), NAME VARCHAR(100), GlbStock int, M1 int, M2 int, M3 int, M4 int, M5 int, M6 int, M7 int, M8 int, M9 int, M10 int, M11 int, M12 int, IncDoc int, TDoc int ) DECLARE @NumberRecords int, @RowCount int DECLARE @NAME varchar(50) DECLARE @GS int, @IncDoc int,@TDoc int DECLARE @M1 BIGINT,@M2 BIGINT,@M3 BIGINT,@M4 BIGINT,@M5 BIGINT,@M6 BIGINT,@M7 BIGINT,@M8 BIGINT,@M9 BIGINT,@M10 BIGINT,@M11 BIGINT,@M12 BIGINT INSERT INTO #STOCK_DETAIL(NAME,GlbStock,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT [NAME],[GlbStock],[M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12] FROM [dbo].STOCK_DETAIL SET @NumberRecords = @@ROWCOUNT SET @RowCount = 1 SET @GS=0 WHILE @RowCount <= @NumberRecords BEGIN SELECT @NAME=NAME,@GS=GlbStock,@M1=M1,@M2=M2,@M3=M3,@M4=M4,@M5=M5,@M6=M6,@M7=M7,@M8=M8,@M9=M9,@M10=M10,@M11=M11,@M12=M12 FROM #STOCK_DETAIL WHERE RowID = @RowCount set @TDoc=0 IF @GS>@M1 BEGIN set @TDoc=@TDoc+30 set @ GS=@GS-@M1 end else BEGIN SET @ IncDoc=@GS/@M1*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M2 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M2 end else BEGIN SET @ IncDoc=@GS/@M2*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M3 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M3 end else BEGIN SET @ IncDoc=@GS/@M3*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M4 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M4 end else BEGIN SET @ IncDoc=@GS/@M4*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M5 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M5 end else BEGIN SET @ IncDoc=@GS/@M5*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M6 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M6 end else BEGIN SET @ IncDoc=@GS/@M6*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M7 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M7 end else BEGIN SET @ IncDoc=@GS/@M7*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M8 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M8 end else BEGIN SET @ IncDoc=@GS/@M8*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M9 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M9 end else BEGIN SET @ IncDoc=@GS/@M9*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M10 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M10 end else BEGIN SET @ IncDoc=@GS/@M10*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M11 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M11 end else BEGIN SET @ IncDoc=@GS/@M11*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END IF @GS>@M12 BEGIN SET @TDoc=@TDoc+30 set @ GS=@GS-@M12 end else BEGIN PRINT @GS --PRINT @M12 SET @ IncDoc=@GS/@M12*30 SET @TDoc=@TDoc+@IncDoc GOTO Branch_One END Branch_One: UPDATE #STOCK_DETAIL SET IncDoc=@IncDoc,TDoc=@TDoc WHERE RowID = @RowCount SET @GS=0 SET @RowCount = @RowCount + 1 END