How to add to the inventory receipt in the ERP system data in the table-CodePudding

GRN # 11, 12 data (identical) auxiliary information in addition to table C into a column, and according to the balance to calculate the inventory of the previous period, how to write SQL! Novice,,, please the great god write clear point

CodePudding user response:

Didn't understand what you mean

CodePudding user response:

 
Color, product name, SELECT a. a. a. batch number, ISNULL (which OTAL, 0) AS into, a. inventory of the previous period, a. the previous inventory - ISNULL (which OTAL, 0) AS the balance 
The FROM C 
LEFT the JOIN 
(SELECT the product name, color, batch number, 
SUM (CASE WHEN return=1 THEN number 1 * ELSE material quantity END) AS the TOTAL 
JOIN the FROM B 
ON b. a. receipt number=a. receipt number 
WHERE storage time BETWEEN '2019-10-11' AND '2019-10-13 s' 
AND the examination AND approval=1 
GROUP BY product name, color, batch number) AS D ON a. product name=C. The product name AND a. color=c. color AND a. batch number=c. batch number

CodePudding user response:

I understand it is a little bit,
Seems to be a month to do the balance report,

The beginning balance

CodePudding user response:

1. Design a inventory list separately, later will be more convenient
2. Use a stored procedure, at the time of receipt to review, at the same time the data is inserted into the inventory table,

CodePudding user response:

Correct once, is a dynamic balance statements,

This issue still occurs
The final balance

According to the payment days query deadline as the basis of the balance summary,

CodePudding user response:

C table is inventory list, how to write an update statement? Or as a stored procedure, how to write

CodePudding user response:

refer to 6th floor robin_dll response:

C table is inventory list, how to write an update statement? Or as a stored procedure, how to write

# 2 to write some of the issues last time, when you look at the following the SELECT of the balance right, right to use an UPDATE after

 
SELECT C. The product name, c. color, c. batch number, ISNULL (which OTAL, 0) AS into, c. inventory of the previous period, c. the previous inventory + ISNULL (which OTAL, 0) AS the balance 
The FROM C 
LEFT the JOIN 
(SELECT the product name, color, batch number, 
SUM (CASE WHEN return=1 THEN number 1 * ELSE material quantity END) AS the TOTAL 
JOIN the FROM B 
ON b. a. receipt number=a. receipt number 
WHERE storage time BETWEEN '2019-10-11' AND '2019-10-13 s' 
AND the examination AND approval=1 
GROUP BY product name, color, batch number) AS D ON c. product name=d. product name AND c. color=d. color AND c. batch number=d. batch number 


The UPDATE C 
SET the balance=c. the previous inventory + ISNULL (which OTAL, 0) 
The FROM C 
LEFT the JOIN 
(SELECT the product name, color, batch number, 
SUM (CASE WHEN return=1 THEN number 1 * ELSE material quantity END) AS the TOTAL 
JOIN the FROM B 
ON b. a. receipt number=a. receipt number 
WHERE storage time BETWEEN '2019-10-11' AND '2019-10-13 s' 
AND the examination AND approval=1 
GROUP BY product name, color, batch number) AS D ON c. product name=d. product name AND c. color=d. color AND c. batch number=d. batch number

CodePudding user response:

 update lgbzbal c set c.f _ir_qty1=d.f _qty1 from 
(select a.f _wh_no, a.f _ven_no, b. _ord_no, b. _item_no, b. _col_no, b. _fx_no, b. _ph, sum (case when a.f _tl=1 then - 1 * f_qty1 else f_qty1 end) as f_qty1 
The from ivrcvmas a, ivrcvdtl b where a.f _rcv_no=b. _rcv_no and f_wh_no='010' and f_rcv_date & gt; 
='20191001'Group by a.f _wh_no, a.f _ven_no f_ord_no, f_item_no, f_col_no f_fx_no f_ph) d 
Where c.p eriod='201910' 
And c.f _wh_no=d.f _wh_no 
And c.f _ord_no=d.f _ord_no 
And c.f _item_no=d.f _item_no 
And c.f _col_no=d.f _col_no 
And c.f _ven_no=d.f _ven_no 
And c.f _fx_no=d.f _fx_no 
And c.f _ph=d.f _ph

Help to have a look at, I why this code error

CodePudding user response:

refer to the eighth floor robin_dll response:

 update lgbzbal c set c.f _ir_qty1=d.f _qty1 from 
(select a.f _wh_no, a.f _ven_no, b. _ord_no, b. _item_no, b. _col_no, b. _fx_no, b. _ph, sum (case when a.f _tl=1 then - 1 * f_qty1 else f_qty1 end) as f_qty1 
The from ivrcvmas a, ivrcvdtl b where a.f _rcv_no=b. _rcv_no and f_wh_no='010' and f_rcv_date & gt; 
='20191001'Group by a.f _wh_no, a.f _ven_no f_ord_no, f_item_no, f_col_no f_fx_no f_ph) d 
Where c.p eriod='201910' 
And c.f _wh_no=d.f _wh_no 
And c.f _ord_no=d.f _ord_no 
And c.f _item_no=d.f _item_no 
And c.f _col_no=d.f _col_no 
And c.f _ven_no=d.f _ven_no 
And c.f _fx_no=d.f _fx_no 
And c.f _ph=d.f _ph

Help to look at, I why this code error

Try the following

 
The update lgbzbal set f_ir_qty1=d.f _qty1 from 
(select a.f _wh_no, a.f _ven_no, b. _ord_no, b. _item_no, b. _col_no, b. _fx_no, b. _ph, sum (case when a.f _tl=1 then - 1 * f_qty1 else f_qty1 end) as f_qty1 
The from ivrcvmas a, ivrcvdtl b where a.f _rcv_no=b. _rcv_no and f_wh_no='010' and f_rcv_date & gt; 
='20191001'Group by a.f _wh_no, a.f _ven_no f_ord_no, f_item_no, f_col_no, f_fx_no lgbzbal f_ph) d, c 
Where c.p eriod='201910' 
And c.f _wh_no=d.f _wh_no 
And c.f _ord_no=d.f _ord_no 
And c.f _item_no=d.f _item_no 
And c.f _col_no=d.f _col_no 
And c.f _ven_no=d.f _ven_no 
And c.f _fx_no=d.f _fx_no 
And c.f _ph=d.f _ph