15,939,620 members
1.00/5 (1 vote)
See more:
It's a an Order Processing related query. There is a sales order which will have different products with some specific quantity. Each qty will have schedule for delivery. In this case, for 55 nos it has the schedule as 10, 15, 30. same way it has once more set of schedule. In Sales details table you get detail of the material sold against each item. You will get details like sale order no and item line no. We need to take schedule qty in FIFO basis and allocate the required qty. I need to get a result set as shown and have to update the sales order detail table. Please help me
Posted
Updated 27-Jun-14 18:07pm
v2
Andrius Leonavicius 27-Jun-14 23:28pm
Hi,

I think you're missing some important information. Please check your question (use "Improve question" button).
Sandeep Mogaveer 28-Jun-14 0:07am
Question updated.

## Solution 1

hi,

I think that you will need to push history of your inserted data.
The question is not clear can you explain more plz ?

Bechir

## Solution 2

SQL
```<blockquote class="quote"><div class="op">Quote:</div>ALTER PROCEDURE [dbo].[ISP_INT_DELFIFO]
@ENTRY_TY VARCHAR(2),@TRAN_CD INT
AS
BEGIN
SET NOCOUNT ON;

IF OBJECT_ID('TEMPDB..#MAIN') IS NOT NULL
DROP TABLE #MAIN

DELETE FROM I_DELIVER_REF WHERE ENTRY_TY=@ENTRY_TY AND TRAN_CD=@TRAN_CD

SELECT I.entry_ty SENTRY_TY,I.TRAN_CD STRAN_CD,I.ITSERIAL SITSERIAL,I.QTY SQTY,O.entry_ty OENTRY_TY,O.Tran_cd OTran_cd,O.itserial Oitserial
INTO #MAIN
FROM STITEM I
INNER JOIN STITREF R ON (I.ENTRY_TY=R.ENTRY_TY AND I.TRAN_CD=R.TRAN_CD AND I.ITSERIAL=R.ITSERIAL)
INNER JOIN SOITEM O ON (O.ENTRY_TY=R.rentry_ty AND O.TRAN_CD=R.Itref_tran AND O.ITSERIAL=R.RITSERIAL)
WHERE I.ENTRY_TY=@ENTRY_TY AND I.Tran_cd=@TRAN_CD
ORDER BY I.DATE,I.INV_NO,I.TRAN_CD,I.ITSERIAL,I.QTY,O.Tran_cd,O.itserial,O.qty
--select * from #MAIN
DECLARE @SENTRY_TY VARCHAR(2),@STRAN_CD INT,@SITSERIAL VARCHAR(5),@SQTY DECIMAL(20,2),@OENTRY_TY VARCHAR(2),@OTRAN_CD INT,@OITSERIAL VARCHAR(5)
DECLARE MAIN_CURSOR CURSOR FOR SELECT * FROM #MAIN
OPEN MAIN_CURSOR
FETCH NEXT FROM MAIN_CURSOR INTO @SENTRY_TY,@STRAN_CD,@SITSERIAL,@SQTY,@OENTRY_TY,@OTRAN_CD,@OITSERIAL
WHILE @@FETCH_STATUS = 0
BEGIN

IF OBJECT_ID('TEMPDB..#DEL') IS NOT NULL
DROP TABLE #DEL

SELECT D.DEL_ID,D.sched_qty-SUM(ISNULL(R.QTY,0.00)) QTY
INTO #DEL
FROM I_DELIVER D
LEFT JOIN I_DELIVER_REF R ON (D.del_id=R.DEL_ID)
WHERE D.entry_ty=@OENTRY_TY AND D.tran_cd=@OTRAN_CD AND D.ITSERIAL=@OITSERIAL
GROUP BY D.DEL_ID,D.sched_qty,D.sched_dt
HAVING D.sched_qty-SUM(ISNULL(R.QTY,0.00))>0
ORDER BY D.sched_dt,D.del_id

--SELECT * FROM #DEL

DECLARE @DEL_ID INT,@QTY DECIMAL(20,2)
DECLARE DEL_CURSOR CURSOR FOR SELECT * FROM #DEL
OPEN DEL_CURSOR
FETCH NEXT FROM DEL_CURSOR INTO @DEL_ID,@QTY
WHILE @@FETCH_STATUS = 0 AND @SQTY>0
BEGIN
IF @QTY>=@SQTY
BEGIN
INSERT INTO I_DELIVER_REF VALUES(@DEL_ID,@SENTRY_TY,@STRAN_CD,@SITSERIAL,@SQTY)
SET @SQTY=0
END
ELSE
BEGIN
INSERT INTO I_DELIVER_REF VALUES(@DEL_ID,@SENTRY_TY,@STRAN_CD,@SITSERIAL,@QTY)
SET @SQTY=@SQTY-@QTY
END

FETCH NEXT FROM DEL_CURSOR INTO @DEL_ID,@QTY
END
CLOSE DEL_CURSOR
DEALLOCATE DEL_CURSOR

DROP TABLE #DEL

FETCH NEXT FROM MAIN_CURSOR INTO @SENTRY_TY,@STRAN_CD,@SITSERIAL,@SQTY,@OENTRY_TY,@OTRAN_CD,@OITSERIAL
END
CLOSE MAIN_CURSOR
DEALLOCATE MAIN_CURSOR

DROP TABLE #MAIN

END
</blockquote>```