quote:

---

Originally posted by waterduck

any1???helps?

---

BEGIN
PRINT CAST('No.' as char(5))+CAST('st_code' as char(17))+CAST('st_desc' as char(30))+CAST('@mon12' as char(10))+CAST('@mon11' as char(10))+CAST('@mon10' as char(10))+CAST('@mon9' as char(10))+CAST('@mon8' as char(10))+CAST('@mon7' as char(10))+CAST('@mon6' as char(10))+CAST('@mon5' as char(10))+CAST('@mon4' as char(10))+CAST('@mon3' as char(10))+CAST('@mon2' as char(10))+CAST('@mon1' as char(10))+CAST('@mon00' as char(10))
+CAST('balance' as char(13))+CAST('MRcost' as char(10))+CAST('WAcost' as char(10))+CAST('total_cost' as char(10))+CAST('total_wacost' as char(10))+CAST('total_atcost' as char(10))
		
		
	SET @x = 0

	OPEN STK_CURSOR
	
	FETCH NEXT FROM STK_CURSOR
	INTO @st_code, @st_desc, @cost, @aver_cost

	WHILE @@FETCH_STATUS = 0
	BEGIN 

		/*----------CREATE TEMP TABLE FOR FIFO----------*/
		CREATE TABLE #TempSt_trx(
		numbers INT,
		date DATETIME,
		qty DECIMAL(13,2),
		COST DECIMAL(13,2))
		/*----------CREATE TEMP TABLE FOR FIFO----------*/

		SET @x = @x + 1
		SET @balance = 0
		SET @prev_balance = 0
		SET @MRcost = 0
		SET @WAcost = 0
		SET @lybalance = 0
		SET @numberss = 1
		SET @mon1 = 0
		SET @mon2 = 0
		SET @mon3 = 0
		SET @mon4 = 0
		SET @mon5 = 0
		SET @mon6 = 0
		SET @mon7 = 0
		SET @mon8 = 0
		SET @mon9 = 0
		SET @mon10 = 0
		SET @mon11 = 0
		SET @mon12 = 0
		SET @mon00 = 0
/*--------------------------------------GET BALANCE, MRCOST, WACOST, TOTALWACOST, TOTALMACOST--------------------------------------*/
		DECLARE STK_CURSOR1 CURSOR FOR
		SELECT trx_type, quantity, qtt_out, unit_price, discount, total_price, do_date
		FROM st_trx
		WHERE do_date<=@enddate and st_code = @st_code
		ORDER BY do_date

		OPEN STK_CURSOR1

		FETCH NEXT FROM STK_CURSOR1
		INTO @trx_type, @quantity, @qtt_out, @unit_price, @discount, @total_price, @do_date

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @prev_balance = @balance
			SET @balance = @balance + (@quantity - @qtt_out)

			if @trx_type = 'PDO'
			BEGIN			
							
				if @discount = '' or @discount IS NULL
					SET @MRcost = @unit_price
				else
					SET @MRcost = NULLIF(@total_price,0)/NULLIF(@quantity,0)

				if @prev_balance >=0
				BEGIN
					if @WAcost = 0
						SET @WAcost = @MRcost
					else
						SET @WAcost = NULLIF((@prev_balance*@WAcost + @quantity*@MRcost),0)/NULLIF((@prev_balance+@quantity),0)
				END
				else
					SET @WAcost = @MRcost

				if @lybalance >= @quantity
					SET @lybalance = @lybalance - @quantity
				else
				BEGIN
					INSERT INTO #TempSt_trx VALUES(CONVERT(INT, @numberss), CONVERT(DATETIME,@do_date), CONVERT(DECIMAL(13,2), @quantity-@lybalance), @MRcost)
					SET @numberss = @numberss + 1
					SET @lybalance = 0
				END
			END

			if @trx_type IN ('GRO','DO','INV','CS','DN','POS')
			BEGIN
				SET @lybalance = @lybalance + @qtt_out

				DECLARE TEMP_CURSOR CURSOR FOR
				SELECT qty FROM #TempSt_trx order by numbers

				OPEN TEMP_CURSOR

				FETCH NEXT FROM TEMP_CURSOR
				INTO @tmpquantity			

				SET @numberss2 = 1
				WHILE @@FETCH_STATUS = 0
				BEGIN

						if @tmpquantity > @lybalance
						BEGIN
							UPDATE #TempSt_trx SET qty = @tmpquantity - @lybalance where numbers = CONVERT(int, @numberss2)
							SET @lybalance = 0
							BREAK
						END
						ELSE
						BEGIN
							SET @lybalance = @lybalance - @tmpquantity
							UPDATE #TempSt_trx SET qty = 0 where numbers = CONVERT(int, @numberss2)
						END

						SET @numberss2 = @numberss2 + 1

					FETCH NEXT FROM TEMP_CURSOR
					INTO @tmpquantity

				END

				CLOSE TEMP_CURSOR
				DEALLOCATE TEMP_CURSOR
			END

			if @trx_type IN ('ADJ','CN')
			BEGIN
				if @quantity <0 or @quantity > 0
				BEGIN
					if @lybalance >= @quantity
						SET @lybalance = @lybalance - @quantity
					else
					BEGIN
						INSERT INTO #TempSt_trx VALUES(CONVERT(INT, @numberss), CONVERT(DATETIME,@do_date), CONVERT(DECIMAL(13,2), @quantity-@lybalance), @MRcost)
						SET @numberss = @numberss + 1
						SET @lybalance = 0
					END
				END
				else
				BEGIN
					SET @lybalance = @lybalance + @qtt_out

					DECLARE TEMP_CURSOR CURSOR FOR
					SELECT qty FROM #TempSt_trx order by numbers

					OPEN TEMP_CURSOR

					FETCH NEXT FROM TEMP_CURSOR
					INTO @tmpquantity			

					SET @numberss2 = 1
					WHILE @@FETCH_STATUS = 0
					BEGIN
							if @tmpquantity > @lybalance
							BEGIN
								UPDATE #TempSt_trx SET qty = @tmpquantity - @lybalance where numbers = CONVERT(int, @numberss2)
								SET @lybalance = 0
								BREAK
							END
							ELSE
							BEGIN
								SET @lybalance = @lybalance - @tmpquantity
								UPDATE #TempSt_trx SET qty = 0 where numbers = CONVERT(int, @numberss2)
							END

							SET @numberss2 = @numberss2 + 1

							FETCH NEXT FROM TEMP_CURSOR
							INTO @tmpquantity
					END

					CLOSE TEMP_CURSOR
					DEALLOCATE TEMP_CURSOR
				END
			END

			FETCH NEXT FROM STK_CURSOR1
			INTO @trx_type, @quantity, @qtt_out, @unit_price, @discount, @total_price, @do_date
		END
		CLOSE STK_CURSOR1
		DEALLOCATE STK_CURSOR1

		/*-----MONTH JAN TO DEC-----*/
		DECLARE RTEMP_CURSOR CURSOR FOR
		SELECT	[1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13]
		FROM	(
				SELECT * FROM(
				SELECT MONTH(date)as date, sum(qty) as qty 
				FROM #TempSt_trx 
				WHERE date>=@startdate and date<=@enddate 
				GROUP BY date
				UNION
				SELECT 13 as date, SUM(qty)as qty FROM #TempSt_trx where date < @startdate)ABC
				)AS AA
		PIVOT	(
				SUM(qty) FOR date IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13])
				)AS pvt

		OPEN RTEMP_CURSOR
	
		FETCH NEXT FROM RTEMP_CURSOR
		INTO @mon1, @mon2, @mon3, @mon4, @mon5, @mon6, @mon7, @mon8, @mon9, @mon10, @mon11, @mon12, @mon00

		CLOSE RTEMP_CURSOR
		DEALLOCATE RTEMP_CURSOR
		/*-----MONTH JAN TO DEC-----*/

		/*-----TotalAT_Cost-----*/
		DECLARE RTEMP_CURSOR CURSOR FOR
		SELECT SUM(qty*cost) as totalat_cost
		FROM #TempSt_trx

		OPEN RTEMP_CURSOR
	
		FETCH NEXT FROM RTEMP_CURSOR
		INTO @total_atcost

		CLOSE RTEMP_CURSOR
		DEALLOCATE RTEMP_CURSOR
		/*-----TotalAT_Cost-----*/

		/*----------DROP FIFO TABLE----------*/
		drop table #TempSt_trx
		/*----------DROP FIFO TABLE----------*/

		if @mon1 IS NULL
		SET @mon1 = 0
		if @mon2 IS NULL
		SET @mon2 = 0
		if @mon3 IS NULL
		SET @mon3 = 0
		if @mon4 IS NULL
		SET @mon4 = 0
		if @mon5 IS NULL
		SET @mon5 = 0
		if @mon6 IS NULL
		SET @mon6 = 0
		if @mon7 IS NULL
		SET @mon7 = 0
		if @mon8 IS NULL
		SET @mon8 = 0
		if @mon9 IS NULL
		SET @mon9 = 0
		if @mon10 IS NULL
		SET @mon10 = 0
		if @mon11 IS NULL
		SET @mon11 = 0
		if @mon12 IS NULL
		SET @mon12 = 0
		if @mon00 IS NULL
		SET @mon00 = 0

		IF @st_desc IS NULL
		SET @st_desc = ' '
		if @MRcost = 0
		SET @MRcost = @cost
		if @WAcost = 0
		SET @WAcost = @aver_cost
		if @total_atcost IS NULL
		SET @total_atcost = 0

		SET @total_cost = @balance * @MRcost
		SET @total_wacost = @balance * @WAcost

		PRINT CAST(@x as char(5))+CAST(@st_code as char(17))+CAST(@st_desc as char(30))+CAST(@mon12 as char(10))+CAST(@mon11 as char(10))+CAST(@mon10 as char(10))+CAST(@mon9 as char(10))+CAST(@mon8 as char(10))+CAST(@mon7 as char(10))+CAST(@mon6 as char(10))+CAST(@mon5 as char(10))+CAST(@mon4 as char(10))+CAST(@mon3 as char(10))+CAST(@mon2 as char(10))+CAST(@mon1 as char(10))+CAST(@mon00 as char(10))
			+CAST(@balance as char(13))+CAST(@MRcost as char(10))+CAST(@WAcost as char(10))+CAST(@total_cost as char(10))+CAST(@total_wacost as char(10))+CAST(@total_atcost as char(10))
		
		FETCH NEXT FROM STK_CURSOR
		INTO @st_code, @st_desc, @cost, @aver_cost
	END
	CLOSE STK_CURSOR
	DEALLOCATE STK_CURSOR

END