Known SpInfo goods table as shown in figure

For the main table SpCRK as shown in figure:

From the table for ins and outs of SpCRK2 as shown in figure:


SpInfo. SpID=SpCRK2. SpCode and SpCRK BillCode.=SpCRK2 BillCode SpCRK. BillType (1 on behalf of the outbound, 2 representative warehousing)
SpCRK2 SpQty for loading and unloading quantity goods, goods inventory;

CodePudding user response:

Finally solved,

CodePudding user response:

To solve the good, congratulations, pick up points,

CodePudding user response:

The original poster is using SQL statements or Delphi code to implement?

CodePudding user response:

reference 1st floor xaxa2166 response:

finally solved,

Has a bug when only outbound without put in storage, can not display, or have inventory no dispatch can't display, everybody to help me see
DM. SpCXQuery. SQL. Add (' select SpID, SumRK - SumCK as SpQty from (SpInfo A inner join (');
DM. SpCXQuery. SQL. Add (' select SpCode, Sum (SpQty) as SumRK from SpCRK2 Where BillCode in (');
DM. SpCXQuery. SQL. The Add (' select BillCode from SpCRK where BillType=2) group by SpCode) on A.S pID=B B.S pCode) inner ');
DM. SpCXQuery. SQL. The Add (' join (select SpCode, Sum (SpQty) as SumCK from SpCRK2 Where BillCode in (select BillCode from SpCRK Where BillType=1) group by SpCode) C on c.s. pCode A.S pID=');

CodePudding user response:

refer to the second floor lyhoo163 response:

solved, congratulations, pick up points,

Has a bug when only outbound without put in storage, can not display, or have inventory no dispatch can't display, everybody to help me see
DM. SpCXQuery. SQL. Add (' select SpID, SumRK - SumCK as SpQty from (SpInfo A inner join (');
DM. SpCXQuery. SQL. Add (' select SpCode, Sum (SpQty) as SumRK from SpCRK2 Where BillCode in (');
DM. SpCXQuery. SQL. The Add (' select BillCode from SpCRK where BillType=2) group by SpCode) on A.S pID=B B.S pCode) inner ');
DM. SpCXQuery. SQL. The Add (' join (select SpCode, Sum (SpQty) as SumCK from SpCRK2 Where BillCode in (select BillCode from SpCRK Where BillType=1) group by SpCode) C on c.s. pCode A.S pID=');

CodePudding user response:

Best processing in the database, written in several parameters to calculate the inventory information,

CodePudding user response:

This writing roughly:

 select a.S pID, isnull (SpQty2, 0) - isnull (SpQty1, 0) SpQty from SpInfo a 
Left the join 
(select SpCode, sum (SpQty) SpQty2 from SpCRK2 where BillType=2 group by SpCode) b 
On a.S pID=b.S pCode 
Left the join 
(select SpCode, sum (SpQty) SpQty1 from SpCRK2 where BillType=1 group by SpCode) c 
On c.s. pCode a.S pID=

CodePudding user response:

The select spid spname,
Isnull (select sum (spqty) from SpCRK2, SpCRK where SpCRK2. Billcode.=SpCRK billcode and BillType=2 and spcode=a.s pid), 0) -
Isnull (select sum (spqty) from SpCRK2, SpCRK where SpCRK2. Billcode.=SpCRK billcode and BillType=1 and spcode=a.s pid), 0) as kc
The from spinfo a

Such written there is no problem, you said but don't know how efficiency! You try a large amount of data. Anyway, I generally is such!

CodePudding user response:

Control of the building Lord how nice ah, is dbgrideh no, this color how to set up