SQL statement for logistics warehouse management? Thank you.

恶魔右指

select cargo category, cargo name, cargo specification, cargo price,
Inbound receipt number = max (case out of inbound state when 1 then document number end), inbound amount = sum (case out inbound state when 1 then amount else 0 end), inward amount = sum (case out of inbound state when 1 then price of goods * quantity else 0 end),
Outbound document number = max (case out of storage state when 0 then document number end), outbound amount = sum (case out of storage state when 0 then number else 0 end), outbound amount = sum (case out of storage state when 0 then price of goods * quantity else 0 end),
Returning Document No. = max (case out of storage state when 2 then document number end), number of withdrawals = sum (case out of storage state when 2 then number else 0 end), amount of returning amount = sum (case out of storage state when 2 then price of goods * quantity else 0 end),
Hiring department, hiring person, date = date of warehousing,
Balance quantity = (select sum (case when out of storage status in (1,2) then quantity else-quantity end) from test_t where id!> Ta.id and goods name = ta. Goods name and goods specifications = ta. Goods specifications and price of goods = ta. price of goods),
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 id!> Ta.id and goods name = ta. Goods name and goods Specification = ta. Cargo specification and price of goods = ta. Price of goods)
from test_t ta
group by ta.id, type of goods, name of the goods, specifications of the goods, price of the goods, purchasing department, user, date of storage
/ *
Class A a1 1 * 2 1000.0000 rk-1 10 10000.0000 NULL 0 .0000 NULL 0 .0000 NULL NULL 2007-01-01 00: 00: 00.000 10 10000.0000
Class a a1 1 * 2 1500.0000 rk-2 15 22500.0000 NULL 0 .0000 NULL 0 .0000 NULL NULL 2007-01-01 00: 00: 00.000 15 22500.0000
Class A a2 1 * 3 2000.0000 rk-3 20 40000.0000 NULL 0 .0000 NULL 0 .0000 NULL NULL 2007-01-01 00: 00: 00.000 20 40000.0000
Class a a1 1 * 2 1000.0000 NULL 0 .0000 ck-1 5 5000.0000 NULL 0 .0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 5 5000.0000
Class A a1 1 * 2 1500.0000 NULL 0 .0000 ck-2 10 15000.0000 NULL 0 .0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 5 7500.0000
Class A a2 1 * 3 2000.0000 NULL 0 .0000 ck-3 10 20000.0000 NULL 0 .0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 10 20000.0000
Class A a1 1 * 2 1000.0000 NULL 0 .0000 NULL 0 .0000 tk-1 1 1000.0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 6 6000.0000
Class A a1 1 * 2 1500.0000 NULL 0 .0000 NULL 0 .0000 tk-2 2 3000.0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 7 10500.0000
Class a2 1 * 3 2000.0000 NULL 0 .0000 NULL 0 .0000 tk-3 3 6000.0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 13 26000.0000
* /

fanfan1980

select cargo category, cargo name, cargo specification, cargo price,
Inbound document number = case when out of storage state = 1 then document number end, inbound quantity = case when out of storage state = 1 then quantity end, amount of storage = case when out of storage state = 1 then goods price * quantity end,
Outbound document number = case when outbound and inbound status = 0 then document number end, outbound amount = case when outbound and outbound status = 0 then quantity end, outbound amount = case when outbound and outbound status = 0 then goods price * quantity end,
Returning Document No. = case when in and out state = 2 then document number end, number of withdrawals = case when in and out state = 2 then quantity end, amount of withdrawal = case when in and out state = 2 then goods price * quantity end,
Hiring department, hiring person, date = date of warehousing,
Balance number = (select sum (case when out of storage status = 0 then -number else number end) from test where id <= A.id and
Cargo category = A. Cargo category and cargo name = A. Cargo name and cargo specification = A. Cargo specification and cargo price = A. Cargo price),
Balance Amount = Goods Price * (select sum (case when out of storage status = 0 then-quantity else quantity end) from test where id <= A.id and
Cargo category = A. Cargo category and cargo name = A. Cargo name and cargo specification = A. Cargo specification and cargo price = A. Cargo price)
from test as A

--result
Cargo category Cargo name Cargo specifications Goods price No. of storage documents No. of storage No. of storage No. of storage no. Of documents No. of storage No. of withdrawals No. of withdrawals No. of withdrawals No. of withdrawals Receiving department Date of use Balance amount
-------------------- -------------------- ---------- ---------- --------------------- ------------------- ------------ --------------------- ----------------- --- ----------- --------------------- --------------- ----- ----------- --------------------- ------------- ------- -------------------- ----------------------- ------------------------------- ----------- -------- -------------
Class a a1 1 * 2 1000.0000 rk-1 10 10000.0000 NULL NULL NULL NULL NULL NULL NULL NULL 2007-01-01 00: 00: 00.000 10 10000.0000
Class a a1 1 * 2 1500.0000 rk-2 15 22500.0000 NULL NULL NULL NULL NULL NULL NULL NULL 2007-01-01 00: 00: 00.000 15 22500.0000
Class A a2 1 * 3 2000.0000 rk-3 20 40000.0000 NULL NULL NULL NULL NULL NULL NULL NULL 2007-01-01 00: 00: 00.000 20 40000.0000
Class a a1 1 * 2 1000.0000 NULL NULL NULL ck-1 5 5000.0000 NULL NULL NULL Department 1 sheet three 2007-01-01 00: 00: 00.000 5 5000.0000
Class A a1 1 * 2 1500.0000 NULL NULL NULL ck-2 10 15000.0000 NULL NULL NULL Department 1 Zhang San 2007-01-01 00: 00: 00.000 5 7500.0000
a class a2 1 * 3 2000.0000 NULL NULL NULL ck-3 10 20000.0000 NULL NULL NULL Department 1 Zhang 2007-01-01 00: 00: 00.000 10 20000.0000
Class a a1 1 * 2 1000.0000 NULL NULL NULL NULL NULL NULL tk-1 1 1000.0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 6 6000.0000
Class a a1 1 * 2 1500.0000 NULL NULL NULL NULL NULL NULL tk-2 2 3000.0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 7 10500.0000
Class A a2 1 * 3 2000.0000 NULL NULL NULL NULL NULL NULL tk-3 3 6000.0000 Department 1 Zhang San 2007-01-01 00: 00: 00.000 13 26000.0000

(9 row (s) affected)

恶魔右指

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)

恶魔右指

The answer has been tested, the landlord can post
select cargo category, cargo name, cargo specification, cargo price,
Inbound receipt number = max (case out of inbound state when 1 then document number end), inbound amount = sum (case out inbound state when 1 then amount else 0 end), inward amount = sum (case out of inbound state when 1 then price of goods * quantity else 0 end),
Outbound document number = max (case out of storage state when 0 then document number end), outbound amount = sum (case out of storage state when 0 then number else 0 end), outbound amount = sum (case out of storage state when 0 then price of goods * quantity else 0 end),
Returning Document No. = max (case out of storage state when 2 then document number end), number of withdrawals = sum (case out of storage state when 2 then number else 0 end), amount of returning amount = sum (case out of storage state when 2 then price of goods * quantity else 0 end),
Hiring department, hiring person, date = date of warehousing,
Balance quantity = (select sum (case when out of storage status in (1,2) then quantity else-quantity end) from test where id!> Ta.id and goods name = ta. Goods name and goods specifications = ta. Goods specifications and price of goods = ta. price of goods),
Balance Amount = (select sum (case when in and out status in (1,2) then quantity * goods price else-quantity * goods price end) from test where id!> Ta.id and goods name = ta. Goods name and goods Specification = ta. Cargo specification and price of goods = ta. Price of goods)
from test ta
group by ta.id, type of goods, name of the goods, specifications of the goods, price of the goods, purchasing department, user, date of storage

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 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 where goods name = ta. Goods name and goods specifications = ta. and price of goods = ta. price of goods),
Warehousing date
from test ta
group by cargo category, cargo name, cargo specification, cargo price

潇洒的月光

Wow, it's too long ~~
Everyone is a master ~~

恶魔右指

There are many column names, and it is necessary to add more judgments, haha
The landlord can generate views, which is much more convenient for future queries
Add before the sentence
create view view A
as

When querying later
select * from view A