|
The above statement changed the name of the landlord's table, and changed test_t to test.
create table test_t --in and out table
(
id int identity (1,1),-primary key
Cargo type varchar (20),
Name of the goods varchar (20),
Cargo specification varchar (20),
Price of goods,
Date of in / out datetime,
Quantity int,
Document number varchar (20),
Outgoing status char (1), --1 means the goods are in storage, 0 means the goods are out of the warehouse, and 2 means the goods are returned out
Receiving department varchar (20),
Receiver varchar (20)
)
--The following is the warehouse data
insert into test_t select 'Class a', 'a1', '1 * 2', 1000, '2007-1-1', 10, 'rk-1', '1', null, null
insert into test_t select 'Class a', 'a1', '1 * 2', 1500, '2007-1-1', 15, 'rk-2', '1', null, null
insert into test_t select 'Class a', 'a2', '1 * 3', 2000, '2007-1-1', 20, 'rk-3', '1', null, null
--The following is the outbound data
insert into test_t select 'Class a', 'a1', '1 * 2', 1000, '2007-1-1', 5, 'ck-1', '0', 'Department 1', 'Zhang San'
insert into test_t select 'Class a', 'a1', '1 * 2', 1500, '2007-1-1', 10, 'ck-2', '0', 'Department 1', 'Zhang San'
insert into test_t select 'Class a', 'a2', '1 * 3', 2000, '2007-1-1', 10, 'ck-3', '0', 'Department 1', 'Zhang San'
--The following is the outbound return data
insert into test_t select 'class a', 'a1', '1 * 2', 1000, '2007-1-1', 1, 'tk-1', '2', 'department 1', 'Zhang San'
insert into test_t select 'class a', 'a1', '1 * 2', 1500, '2007-1-1', 2, 'tk-2', '2', 'department 1', 'Zhang San'
insert into test_t select 'Class a', 'a2', '1 * 3', 2000, '2007-1-1', 3, 'tk-3', '2', 'Department 1', 'Zhang San' select cargo category, cargo name, cargo specification, cargo price,
Inventory balance quantity = (select sum (case when out of storage status in (1,2) then quantity else-quantity end) from test_t where goods name = ta. Goods name and goods specification = ta. Goods specifications and goods price = ta. Price of goods),
Inventory balance amount = (select sum (case when out of storage status in (1,2) then quantity * goods price else-quantity * goods price end) from test_t where goods name = ta. Goods name and goods specifications = ta. and price of goods = ta. price of goods),
Warehousing date
from test_t ta
group by cargo category, cargo name, cargo specification, cargo price
Cargo category Cargo name Cargo specifications Cargo price Stock balance quantity Stock balance amount
-------------------- -------------------- ---------- ---------- --------------------- ----------- -------- ------------- ------------------------------------- -----------------
Class a a1 1 * 2 1000.0000 6 6000.0000 2007-01-01 00: 00: 00.000
Class a a1 1 * 2 1500.0000 7 10500.0000 2007-01-01 00: 00: 00.000
Class a a2 1 * 3 2000.0000 13 26000.0000 2007-01-01 00: 00: 00.000
(3 rows affected) |
|