none
Executing below query takes 1:30 mins to 2 hours, pls. help me get out of this problem

    Question

  • My query:

    Declare @F_WH nvarchar(20), 
     @T_WH nvarchar(20), 
     @F_ItmGrp nvarchar(20), 
     @T_ItmGrp nvarchar(20),  
     @F_Date nvarchar(10),  
     @T_Date nvarchar(10) 
     Select @F_WH = (isnull(T0.whscode, '')) from [dbo].[owhs] T0 
     where T0.whscode = '' 
     Select @T_WH = (isnull(T1.whscode, '')) from [dbo].[owhs] T1 
     where T1.whscode = '' 
     SELECT @F_ItmGrp = ( isnull(T2.ItmsGrpCod, '')) FROM [dbo].[OITB] T2 
     WHERE T2.ItmsGrpCod = '103' 
     SELECT @T_ItmGrp = (isnull(T3.ItmsGrpCod, '')) FROM [dbo].[OITB] T3 
     WHERE T3.ItmsGrpCod = '103' Select @F_Date =  '2012-04-01' 
     Select @T_Date =  '2012-04-01' if @F_WH is null set @F_WH = '' 
     if @T_WH is null set @T_WH = '' if @F_ItmGrp is null set @F_ItmGrp = ''  
     if @T_ItmGrp is null set @T_ItmGrp = '' if @F_Date is null set @F_Date = ''  
     if @T_Date is null set @T_Date = ''  
     select tt.ItemGroup,tt.ItemCode,tt.ItemDescription,sum(tt.OpeningStock) 
     as OpeningStock,sum(tt.TotalInwardQty) as TotalInwardQty,  
     sum(tt.TotalOutwardQty) as TotalOutwardQty,sum(tt.ClosingStockValue) 
     as ClosingStockValue,  closing = (sum(tt.OpeningStock)+ sum(tt.TotalInwardQty) - sum(tt.TotalOutwardQty)),   
     price = case 
     when (sum(tt.ClosingStockValue)) = 0 then 0   
     else (sum(tt.ClosingStockValue)/(sum(tt.OpeningStock) + sum(tt.TotalInwardQty) - sum(tt.TotalOutwardQty))) 
     end   
     from 
     (Select WH AS 'WarehouseCode', ITEMGROUPNAME AS 'ItemGroup', 
     ITEMCODE AS 'ItemCode', ITEMDESC AS 'ItemDescription', OB AS 'OpeningStock',   
     OB_VAL AS 'OpeningStockValue', IN_QTY AS 'TotalInwardQty', 
     IN_VAL AS 'TotalInwardValue',  ITMGRP_IN_QTY AS 'ItemGroupwiseInwardQtyTotal',   
     OUT_QTY AS 'TotalOutwardQty', OUT_VAL AS 'TotalOutwardValue', 
     ITMGRP_OUT_QTY AS 'ItemGroupwiseOutwardQtyTotal',  
     CB_QTY AS 'ClosingStock',   
     CB_VAL AS 'ClosingStockValue' from [dbo].[FUNC_STOCK_SUMMARY_NEW](@F_WH,@T_WH,@F_ItmGrp,@T_ItmGrp,@F_Date,@T_Date)) tt    
     group by tt.ItemGroup,tt.ItemCode,tt.ItemDescription

    The Function:

    USE [SFP_09.10.2012]
    GO
    /****** Object:  UserDefinedFunction [dbo].[FUNC_STOCK_SUMMARY_NEW]    Script Date: 01/05/2013 14:52:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER	FUNCTION [dbo].[FUNC_STOCK_SUMMARY_NEW](@F_WAREHOUSE NVARCHAR(20), @T_WAREHOUSE NVARCHAR(20), @F_ITEMGROUP NVARCHAR(20), @T_ITEMGROUP NVARCHAR(20), @F_DATE DATETIME, @T_DATE DATETIME)
    Returns
    @TAB_STOCK Table (WH NVARCHAR(20),ITEMGROUPNAME NVARCHAR(100),ITEMCODE NVARCHAR(20), ITEMDESC NVARCHAR(100), OB NUMERIC(16,4), OB_VAL NUMERIC(16,4), IN_QTY NUMERIC(16,4), IN_VAL NUMERIC(16,4), ITMGRP_IN_QTY NUMERIC(16,4), ITMGRP_IN_VAL NUMERIC(16,4), OUT_QTY NUMERIC(16,4), OUT_VAL NUMERIC(16,4), ITMGRP_OUT_QTY NUMERIC(16,4), ITMGRP_OUT_VAL NUMERIC(16,4), CB_QTY NUMERIC(16,4), CB_VAL NUMERIC(16,4),packcartons NUMERIC(16,4),itmprice NUMERIC(16,4),ITMGRPCD NVARCHAR(20),DTE DATETIME)
    AS
    BEGIN
    	--Cursor
    	--------
    	--Warehouse
    	--	Item Group
    	--		Item
    	--			Date
    	--
    	--			End Date
    	--		End Item
    	--	End Item Group
    	--End Warehouse
    	--Declaration Part
    	DECLARE @WAREHOUSE NVARCHAR(20), @ITEMCODE NVARCHAR(20), @ITEMDESC NVARCHAR(100), @ITEMGROUP NVARCHAR(20), @OPENQTY NUMERIC(16,4), @OPENVALUE NUMERIC(16,4), @RECEIPTQTY NUMERIC(16,4), @RECEIPTVALUE NUMERIC(16,4), @GROUPTOT_RECEIPTQTY NUMERIC(16,4), @GROUPTOT_RECEIPTVALUE NUMERIC(16,4), @ISSUEDQTY NUMERIC(16,4), @ISSUEDVALUE NUMERIC(16,4), @CLOSINGQTY NUMERIC(16,4), @CLOSINGVALUE NUMERIC(16,4), @GROUPTOT_ISSUEDQTY NUMERIC(16,4), @GROUPTOT_ISSUEDVALUE NUMERIC(16,4)
    	DECLARE @DATE DATETIME --NVARCHAR(10)
    	DECLARE @INQTY NUMERIC(16,4), @OUTQTY NUMERIC(16,4), @TRANSTYPE NVARCHAR(6), @CALCPRICE NUMERIC(16,4)
    	DECLARE @TOT_IN NUMERIC(16,4), @TOT_OUT NUMERIC(16,4), @TOT_IN_PRICE NUMERIC(16,4), @TOT_OUT_PRICE NUMERIC(16,4)
    	DECLARE @OPENQTYPRICE NUMERIC(16,4), @OpQty NUMERIC(16,4), @OpVal NUMERIC(16,4), @ClQty NUMERIC(16,4), @ClVal NUMERIC(16,4)
    	DECLARE @PREVDAYCLOSING NUMERIC(16,4), @PREVDAYCLOSINGVAL NUMERIC(16,4), @DAYOPENINGBALANCE NUMERIC(16,4), @DAYOPENINGBALANCEVAL NUMERIC(16,4), @DAYCLOSINGBALANCE NUMERIC(16,4), @DAYCLOSINGBALANCEVAL NUMERIC(16,4)
    	DECLARE @TAB_DAY_STOCK TABLE (WH NVARCHAR(20),ITMCORTON NUMERIC(16,4), ITEMGROUP NVARCHAR(20),ITEMCODE NVARCHAR(20), DATE DATETIME, OB NUMERIC(16,4), OB_VAL NUMERIC(16,4), IN_QTY NUMERIC(16,4), IN_QTY_VAL NUMERIC(16,4), OUT_QTY NUMERIC(16,4), OUT_QTY_VAL NUMERIC(16,4), CB_QTY NUMERIC(16,4), CB_QTY_VAL NUMERIC(16,4), INV_REV_FLAG INT,itmprice NUMERIC(16,4))
    	DECLARE @ST_DATE DATETIME, @EN_DATE DATETIME, @IN_DATE DATETIME
    	DECLARE @ITMCOD NVARCHAR(20)
    	DECLARE @INV_REVALUE NUMERIC(16,4), @INV_REV_FLAG INT
    	DECLARE @TOT_BALANCE NUMERIC(16,4), @TOT_TRANS_BALANCE NUMERIC(16,4)
        declare @packunit NUMERIC(16,4), @packcartons NUMERIC(16,4),@itmprice NUMERIC(16,4) 
        SET @packunit = 0
        SET @packcartons = 0
    	SET @TOT_BALANCE = 0
    	SET @TOT_TRANS_BALANCE = 0
        
    	SET @INV_REV_FLAG = 0
    	SET @ST_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE)
    	SET @EN_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE DESC)
    	SET @DATE = @ST_DATE
    	--Warehouse Cursor
    	DECLARE CUR_WAREHOUSE CURSOR FOR SELECT WHSCODE FROM OWHS WHERE ((WHSCODE >= @F_WAREHOUSE AND WHSCODE <= @T_WAREHOUSE) OR (@F_WAREHOUSE = '' AND @T_WAREHOUSE = '')) ORDER BY WHSCODE
    	OPEN CUR_WAREHOUSE
    	FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
    	WHILE(@@FETCH_STATUS = 0)
    	BEGIN
    		--Item Group Cursor
    		DECLARE CUR_ITEMGROUP CURSOR FOR SELECT ITMSGRPCOD FROM OITB 
    WHERE ((ITMSGRPCOD = @F_ITEMGROUP AND ITMSGRPCOD <= @T_ITEMGROUP) 
    OR (@F_ITEMGROUP = ''AND @T_ITEMGROUP = '')) 
    ORDER BY ITMSGRPCOD
    		OPEN CUR_ITEMGROUP
    		FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
    		WHILE(@@FETCH_STATUS = 0)
    		BEGIN
    			--Item Cursor
    			DECLARE CUR_ITEM CURSOR FOR SELECT DISTINCT T6.ITEMCODE FROM OINM T6 WHERE ((T6.ITEMCODE IN (SELECT T0.[ItemCode] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.ITMSGRPCOD = @ITEMGROUP)) AND (T6.WAREHOUSE = @WAREHOUSE)) ORDER BY T6.ITEMCODE -- AND ((T6.DOCDATE >= @F_DATE AND T6.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )) ORDER BY T6.ITEMCODE
    			OPEN CUR_ITEM
    			FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
    			WHILE(@@FETCH_STATUS = 0)
    			BEGIN
    				SET @PREVDAYCLOSING = 0
    				SET @PREVDAYCLOSINGVAL = 0
    				SET @ST_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE)
    				SET @EN_DATE = (SELECT TOP 1 DOCDATE FROM OINM ORDER BY DOCDATE DESC)
    				SET @DATE = @ST_DATE
    				--DECLARE CUR_DATE CURSOR FOR SELECT DISTINCT T0.DOCDATE FROM OINM T0 ORDER BY T0.DOCDATE --WHERE  ((T0.DOCDATE >= @F_DATE AND T0.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') ) ORDER BY T0.DOCDATE
    				--OPEN CUR_DATE
    				--FETCH NEXT FROM CUR_DATE INTO @DATE
    				--WHILE (@@FETCH_STATUS = 0)
    				WHILE (@DATE <= @EN_DATE)
    				BEGIN
    					SET @INQTY = 0
    					SET @OUTQTY = 0
    					SET @CALCPRICE = 0
    					SET @TOT_IN = 0
    					SET @TOT_OUT = 0
    					SET @TOT_IN_PRICE = 0
    					SET @TOT_OUT_PRICE = 0
    					SET @OPENQTY = 0
    					SET @OPENQTYPRICE = 0
    					SET @DAYOPENINGBALANCE = 0
    					SET @DAYOPENINGBALANCEVAL = 0
    					SET @DAYCLOSINGBALANCE = 0
    					SET @DAYCLOSINGBALANCEVAL = 0
    					SET @INV_REVALUE = 0
    --modified for excluding the transtype = 67   starts on 11-10-2011
    					DECLARE CUR_ITEM_IN CURSOR FOR SELECT  (CONVERT(NUMERIC(16,4), T6.INQTY)), (CONVERT(NUMERIC(16,4), T6.OUTQTY)), (CONVERT(NUMERIC(16,4), T6.TRANSVALUE)), T6.TRANSTYPE  FROM OINM T6 WHERE ((T6.ITEMCODE = @ITEMCODE) AND (T6.WAREHOUSE = @WAREHOUSE) AND (T6.DOCDATE = @DATE) and t6.transtype <> 67)
    --end		
    			OPEN CUR_ITEM_IN
    					FETCH NEXT FROM CUR_ITEM_IN INTO @INQTY, @OUTQTY, @CALCPRICE, @TRANSTYPE
    					WHILE(@@FETCH_STATUS = 0)
    					BEGIN
    						IF @INQTY = 0 AND @OUTQTY = 0 
    							SET @INV_REVALUE = @INV_REVALUE + @CALCPRICE
    						IF @CALCPRICE < 0 
    							SET @CALCPRICE = @CALCPRICE * (-1)
    						IF (@INQTY > 0) --AND (@TRANSTYPE != '-2')
    						BEGIN
    					
    							SET @TOT_IN = @TOT_IN + @INQTY
    							SET @TOT_IN_PRICE  = @TOT_IN_PRICE + @CALCPRICE
    						END
    						IF (@OUTQTY > 0) --AND (@TRANSTYPE != '-2')
    						BEGIN
    					
    							SET @TOT_OUT = @TOT_OUT + @OUTQTY
    							SET @TOT_OUT_PRICE  = @TOT_OUT_PRICE + @CALCPRICE
    						END
    --						IF (@INQTY > 0) AND (@TRANSTYPE = '-2')
    --						BEGIN
    --							
    --							SET @OPENQTY = @OPENQTY + @INQTY
    --							SET @OPENQTYPRICE = @OPENQTYPRICE + @CALCPRICE	
    --
    --						END
    						FETCH NEXT FROM CUR_ITEM_IN INTO @INQTY, @OUTQTY, @CALCPRICE, @TRANSTYPE
    					END
    					CLOSE CUR_ITEM_IN
    					DEALLOCATE CUR_ITEM_IN
    					SET @INV_REV_FLAG = 0
    					IF @OPENQTY != 0 OR @TOT_IN != 0 OR @TOT_OUT != 0 OR @F_DATE = @DATE OR @T_DATE = @DATE OR @INV_REVALUE != 0
    					BEGIN
    	
    						IF @INV_REVALUE != 0 AND @TOT_IN = 0 AND @TOT_OUT = 0
    							SET @INV_REV_FLAG = 1
    	
    						SET @DAYOPENINGBALANCE = @PREVDAYCLOSING
    						SET @DAYOPENINGBALANCEVAL = @PREVDAYCLOSINGVAL
    						SET @DAYCLOSINGBALANCE = @DAYOPENINGBALANCE + (@OPENQTY + @TOT_IN) - @TOT_OUT
    						SET @DAYCLOSINGBALANCEVAL = @DAYOPENINGBALANCEVAL + (@OPENQTYPRICE + @TOT_IN_PRICE) - @TOT_OUT_PRICE + @INV_REVALUE
    						--IF @OPENQTY != 0 OR @TOT_IN != 0 OR @TOT_OUT != 0
    						--IF @DAYOPENINGBALANCE != 0 OR @DAYCLOSINGBALANCE != 0
    INSERT INTO @TAB_DAY_STOCK VALUES(@WAREHOUSE,@packcartons, @ITEMGROUP, @ITEMCODE, @DATE, @DAYOPENINGBALANCE, @DAYOPENINGBALANCEVAL, (@OPENQTY + @TOT_IN), (@OPENQTYPRICE + @TOT_IN_PRICE), @TOT_OUT, @TOT_OUT_PRICE, @DAYCLOSINGBALANCE, @DAYCLOSINGBALANCEVAL, @INV_REV_FLAG,@itmprice)
    						SET @PREVDAYCLOSING = @DAYCLOSINGBALANCE
    						SET @PREVDAYCLOSINGVAL = @DAYCLOSINGBALANCEVAL	
    			
    						IF @T_DATE = @DATE			
    							SET @TOT_BALANCE = @TOT_BALANCE + @DAYCLOSINGBALANCEVAL
    	
    					END
    					--FETCH NEXT FROM CUR_DATE INTO @DATE
    					SET @DATE = @DATE + 1				
    				END
    				--CLOSE CUR_DATE
    				--DEALLOCATE CUR_DATE
    --SALE PACKUNIT
    					
    				FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
    			END
    			CLOSE CUR_ITEM
    			DEALLOCATE CUR_ITEM
    			FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
    		END
    		CLOSE CUR_ITEMGROUP
    		DEALLOCATE CUR_ITEMGROUP
    		FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
    	END
    	CLOSE CUR_WAREHOUSE
    	DEALLOCATE CUR_WAREHOUSE
    	DECLARE @WH_TS NVARCHAR(20), @ITEMGROUP_TS NVARCHAR(20),@ITEMCODE_TS NVARCHAR(20), @DATE_TS DATETIME, @OB_TS NUMERIC(16,4), @OB_VAL_TS NUMERIC(16,4), @IN_QTY_TS NUMERIC(16,4), @IN_QTY_VAL_TS NUMERIC(16,4), @OUT_QTY_TS NUMERIC(16,4), @OUT_QTY_VAL_TS NUMERIC(16,4), @CB_QTY_TS NUMERIC(16,4), @CB_QTY_VAL_TS NUMERIC(16,4), @INV_REV_FLAG_TS INT
    	DECLARE @TEMP NUMERIC(16,4)
    	DECLARE @DATE_IN DATETIME
    	DECLARE @FIRST_REC INT
    	SET @FIRST_REC = 0
    	DECLARE @OB_QTY_IN NUMERIC(16,4), @OB_VAL_IN NUMERIC(16,4), @CL_QTY_IN NUMERIC(16,4), @CL_VAL_IN NUMERIC(16,4)
    	DECLARE @TOT_IN_QTY_IN NUMERIC(16,4), @TOT_IN_VAL_IN NUMERIC(16,4), @TOT_OUT_QTY_IN NUMERIC(16,4), @TOT_OUT_VAL_IN NUMERIC(16,4), @TOT_INV_REV_FALG INT
    	DECLARE @GTOT_IN_QTY_IN NUMERIC(16,4), @GTOT_IN_VAL_IN NUMERIC(16,4), @GTOT_OUT_QTY_IN NUMERIC(16,4), @GTOT_OUT_VAL_IN NUMERIC(16,4)
    	DECLARE @GRPNAME NVARCHAR(100), @ITMDESC NVARCHAR(100)
    	DECLARE @GRAND_TOT_IN NUMERIC(16,4), @GRAND_TOT_OUT NUMERIC(16,4)
    	SET @GRAND_TOT_IN = 0
    	SET @GRAND_TOT_OUT = 0
    	--Warehouse Cursor
    	DECLARE CUR_WAREHOUSE CURSOR FOR SELECT WHSCODE FROM OWHS WHERE ((WHSCODE >= @F_WAREHOUSE AND WHSCODE <= @T_WAREHOUSE) OR (@F_WAREHOUSE = '' AND @T_WAREHOUSE = '')) ORDER BY WHSCODE
    	OPEN CUR_WAREHOUSE
    	FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
    	WHILE(@@FETCH_STATUS = 0)
    	BEGIN
    		--Item Group Cursor
    		DECLARE CUR_ITEMGROUP CURSOR FOR SELECT ITMSGRPCOD FROM OITB 
    WHERE ((ITMSGRPCOD = @F_ITEMGROUP AND ITMSGRPCOD <= @T_ITEMGROUP) 
    OR (@F_ITEMGROUP = ''AND @T_ITEMGROUP = '')) 
    ORDER BY ITMSGRPCOD
    		OPEN CUR_ITEMGROUP
    		FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
    		WHILE(@@FETCH_STATUS = 0)
    		BEGIN
    			SET @GTOT_IN_QTY_IN = 0
    			SET @GTOT_IN_VAL_IN = 0
    			SET @GTOT_OUT_QTY_IN = 0
    			SET @GTOT_OUT_VAL_IN = 0
    			--Item Cursor
    			DECLARE CUR_ITEM CURSOR FOR SELECT DISTINCT T6.ITEMCODE FROM OINM T6 WHERE ((T6.ITEMCODE IN (SELECT T0.[ItemCode] FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod WHERE T1.ITMSGRPCOD = @ITEMGROUP)) AND (T6.WAREHOUSE = @WAREHOUSE)) ORDER BY T6.ITEMCODE -- AND ((T6.DOCDATE >= @F_DATE AND T6.DOCDATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )) ORDER BY T6.ITEMCODE
    			OPEN CUR_ITEM
    			FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
    			WHILE(@@FETCH_STATUS = 0)
    			BEGIN
    				SET @TOT_IN_QTY_IN = 0
    				SET @TOT_IN_VAL_IN = 0
    				SET @TOT_OUT_QTY_IN = 0
    				SET @TOT_OUT_VAL_IN = 0
    				SET @TOT_INV_REV_FALG = 0
    				SET @FIRST_REC = 0
    		
    				DECLARE CUR_DATE_IN CURSOR FOR SELECT DISTINCT DATE FROM @TAB_DAY_STOCK WHERE (WH = @WAREHOUSE) AND (ITEMGROUP = @ITEMGROUP)AND (ITEMCODE = @ITEMCODE) AND ((DATE >= @F_DATE AND DATE <= @T_DATE) OR (@F_DATE = '' AND @T_DATE = '') )
    				OPEN CUR_DATE_IN
    				FETCH NEXT FROM CUR_DATE_IN INTO @DATE_IN
    				WHILE(@@FETCH_STATUS = 0)
    				BEGIN
    					DECLARE CUR_TRANS CURSOR FOR SELECT * FROM @TAB_DAY_STOCK WHERE ((WH = @WAREHOUSE) AND (ITEMGROUP = @ITEMGROUP) AND (DATE = @DATE_IN) AND (ITEMCODE = @ITEMCODE))
    					OPEN CUR_TRANS
    FETCH NEXT FROM CUR_TRANS INTO @WH_TS,@packcartons, @ITEMGROUP_TS,@ITEMCODE_TS, @DATE_TS, @OB_TS, @OB_VAL_TS, @IN_QTY_TS, @IN_QTY_VAL_TS, @OUT_QTY_TS, @OUT_QTY_VAL_TS, @CB_QTY_TS, @CB_QTY_VAL_TS, @INV_REV_FLAG_TS,@itmprice
    					WHILE (@@FETCH_STATUS = 0)
    					BEGIN
    						SET @FIRST_REC = @FIRST_REC + 1
    						IF @FIRST_REC = 1 
    						BEGIN
    							SET @OB_QTY_IN = @OB_TS
    							SET @OB_VAL_IN = @OB_VAL_TS
    						END
    						SET @TOT_IN_QTY_IN = @TOT_IN_QTY_IN + @IN_QTY_TS
    						SET @TOT_IN_VAL_IN = @TOT_IN_VAL_IN + @IN_QTY_VAL_TS
    						SET @TOT_OUT_QTY_IN = @TOT_OUT_QTY_IN + @OUT_QTY_TS
    						SET @TOT_OUT_VAL_IN = @TOT_OUT_VAL_IN + @OUT_QTY_VAL_TS
    						SET @GTOT_IN_QTY_IN = @GTOT_IN_QTY_IN + @IN_QTY_TS
    						SET @GTOT_IN_VAL_IN = @GTOT_IN_VAL_IN + @IN_QTY_VAL_TS
    						SET @GTOT_OUT_QTY_IN = @GTOT_OUT_QTY_IN + @OUT_QTY_TS
    						SET @GTOT_OUT_VAL_IN = @GTOT_OUT_VAL_IN + @OUT_QTY_VAL_TS
    						SET @CL_QTY_IN = @CB_QTY_TS
    						SET @CL_VAL_IN = @CB_QTY_VAL_TS
    						SET @TOT_INV_REV_FALG = @TOT_INV_REV_FALG + @INV_REV_FLAG_TS
    						
    						FETCH NEXT FROM CUR_TRANS INTO @WH_TS,@packcartons, @ITEMGROUP_TS,@ITEMCODE_TS, @DATE_TS, @OB_TS, @OB_VAL_TS, @IN_QTY_TS, @IN_QTY_VAL_TS, @OUT_QTY_TS, @OUT_QTY_VAL_TS, @CB_QTY_TS, @CB_QTY_VAL_TS, @INV_REV_FLAG_TS,@itmprice
    					END	
    					CLOSE CUR_TRANS
    					DEALLOCATE CUR_TRANS
    				FETCH NEXT FROM CUR_DATE_IN INTO @DATE_IN
    				END
    				CLOSE CUR_DATE_IN
    				DEALLOCATE CUR_DATE_IN
    				SET @GRPNAME = (SELECT ITMSGRPNAM FROM OITB WHERE ITMSGRPCOD = @ITEMGROUP)
    				SET @ITMDESC = (SELECT ITEMNAME FROM OITM WHERE ITEMCODE = @ITEMCODE)
    -----------==========================================
    declare cur_item_packunit cursor for select SALPACKUN from oitm  where itemcode = @ITEMCODE
    					open cur_item_packunit
    					fetch  next from cur_item_packunit into @packunit
    					while(@@FETCH_STATUS = 0)
    				begin
    					SET @packcartons = @CL_QTY_IN/@packunit
    					fetch next from cur_item_packunit into @packunit
                    end
    					close cur_item_packunit
    					deallocate cur_item_packunit
    ---
    ---- item price
    declare cur_item_price cursor for select price from itm1  where itemcode = @ITEMCODE
    					open cur_item_price
    					fetch  next from cur_item_price into @itmprice
    					set @itmprice=@itmprice
    					close cur_item_price
    					deallocate cur_item_price
    -----------------
    				
    				--IF @TOT_IN_QTY_IN != 0 OR @TOT_OUT_QTY_IN != 0 OR @TOT_INV_REV_FALG > 0
    				--BEGIN
    INSERT INTO @TAB_STOCK VALUES(@WH_TS,@GRPNAME, @ITEMCODE_TS, @ITMDESC, @OB_QTY_IN, @OB_VAL_IN, @TOT_IN_QTY_IN, @TOT_IN_VAL_IN, @GTOT_IN_QTY_IN, @GTOT_IN_VAL_IN, @TOT_OUT_QTY_IN, @TOT_OUT_VAL_IN, @GTOT_OUT_QTY_IN, @GTOT_OUT_VAL_IN, @CL_QTY_IN, @CL_VAL_IN,@packcartons,@itmprice,@ITEMGROUP,@DATE)
    					--SET @TOT_TRANS_BALANCE = @TOT_TRANS_BALANCE + @CL_VAL_IN
    				--END
    				IF @TOT_IN_QTY_IN != 0 OR @TOT_OUT_QTY_IN != 0 OR @TOT_INV_REV_FALG > 0
    				BEGIN
    					SET @TOT_TRANS_BALANCE = @TOT_TRANS_BALANCE + @CL_VAL_IN
    				END
    				FETCH NEXT FROM CUR_ITEM INTO @ITEMCODE
    			END
    			CLOSE CUR_ITEM
    			DEALLOCATE CUR_ITEM
    			IF @GTOT_IN_VAL_IN != 0 OR @GTOT_OUT_VAL_IN != 0
    			BEGIN
    				--INSERT INTO @TAB_STOCK VALUES('', '', '', 'Sub Total(' + @GRPNAME + ')', NULL, NULL, NULL, @GTOT_IN_VAL_IN, NULL, NULL, NULL, @GTOT_OUT_VAL_IN, NULL, NULL, NULL, NULL)
    				SET @GRAND_TOT_IN = @GRAND_TOT_IN + @GTOT_IN_VAL_IN
    				SET @GRAND_TOT_OUT = @GRAND_TOT_OUT + @GTOT_OUT_VAL_IN
    			END
    			FETCH NEXT FROM CUR_ITEMGROUP INTO @ITEMGROUP
    		END
    		CLOSE CUR_ITEMGROUP
    		DEALLOCATE CUR_ITEMGROUP
    		FETCH NEXT FROM CUR_WAREHOUSE INTO @WAREHOUSE
    	END
    	CLOSE CUR_WAREHOUSE
    	DEALLOCATE CUR_WAREHOUSE
    --
    --	IF @GRAND_TOT_IN != 0 OR @GRAND_TOT_OUT != 0
    --	BEGIN
    --		INSERT INTO @TAB_STOCK VALUES('','', '', 'Grand Total', NULL, NULL, NULL, @GRAND_TOT_IN, NULL, NULL, NULL, @GRAND_TOT_OUT, NULL, NULL, NULL, NULL,'','','','')
    --		--INSERT INTO @TAB_STOCK VALUES('','','',NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    --		--INSERT INTO @TAB_STOCK VALUES('','','','Total Transaction Balance',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @TOT_TRANS_BALANCE)
    --		--INSERT INTO @TAB_STOCK VALUES('','','',NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    --		--INSERT INTO @TAB_STOCK VALUES('','','','G/L Balance',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @TOT_BALANCE)
      Return
    	END
    --
    --
    --
    --select whscode from owhs
    --select itemcode,itmsgrpcod from oitm
    --select itemcode,whscode from oibt
    --select itmsgrpcod,itmsgrpnam from oitb
    --
    --select distinct wh.whscode,tb.itmsgrpnam,tb.itmsgrpcod,tm.salpackun from owhs wh inner join oibt bt
    --on wh.whscode=bt.whscode
    --inner join oitm tm on tm.itemcode=bt.itemcode
    --inner join oitb tb on tm.itmsgrpcod=tb.itmsgrpcod
    --where tb.itmsgrpnam='Eau De Parfum FG'

    Saturday, January 05, 2013 9:57 AM

Answers

  • Hi Sanju576, 

    Total duration of the query can be broken in to compile time, CPU time and Wait time. Before starting troubleshooting the query which is running for longer duration, Identify if the query is slow because it is long waiting or Long running or long compiling. Please refer to the following article about troubleshooting SQL query: http://mssqlwiki.com/tag/sql-query-tuning/

    Thanks,
    Eileen


    Eileen Zhao
    TechNet Community Support

    • Marked as answer by Eileen Zhao Thursday, January 24, 2013 2:39 AM
    Thursday, January 10, 2013 8:42 AM