SQL statement for logistics warehouse management? Thank you.

牛牛很乖

/ * How to generate inventory details and inventory count tables * /

--Table Structure

create table test-in and out of the 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 select 'class a', 'a1', '1 * 2', 1000, '2007-1-1', 10, 'rk-1', '1', null, null
insert into test select 'class a', 'a1', '1 * 2', 1500, '2007-1-1', 15, 'rk-2', '1', null, null
insert into test 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 select 'Class a', 'a1', '1 * 2', 1000, '2007-1-1', 5, 'ck-1', '0', 'Department 1', 'Zhang San'
insert into test select 'a', 'a1', '1 * 2', 1500, '2007-1-1', 10, 'ck-2', '0', 'department 1', 'Zhang San'
insert into test select '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 select 'a', 'a1', '1 * 2', 1000, '2007-1-1', 1, 'tk-1', '2', 'department 1', 'Zhang San'
insert into test select 'a', 'a1', '1 * 2', 1500, '2007-1-1', 2, 'tk-2', '2', 'department 1', 'Zhang San'
insert into test select 'Class a', 'a2', '1 * 3', 2000, '2007-1-1', 3, 'tk-3', '2', 'Department 1', 'Zhang San'


select * from test
drop table test
/ *
Request for inventory account in the following two formats
1. Details of goods entering and leaving the warehouse

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 storage no.
Class a a1 1 * 2 1000 rk-1 10 10000 null 0 0 null 0 0 null null 2007-1-1 10 10000
Class a a1 1 * 2 1500 rk-2 15 22500 null 0 0 null 0 0 null null 2007-1-1 15 22500
Class a2 1 * 3 2000 rk-3 20 40000 null 0 0 null 0 0 null null 2007-1-1 20 40000

Class A a1 1 * 2 1000 null 0 0 ck-1 5 5000 null 0 0 Department 1 Zhang San 2007-1-1 5 5000
Class A a1 1 * 2 1500 null 0 0 ck-2 10 15000 null 0 0 Department 1 Zhang San 2007-1-1 5 7500
Class a2 1 * 3 2000 null 0 0 ck-3 10 20000 null 0 0 Department 1 Zhang San 2007-1-1 10 20000

Class A a1 1 * 2 1000 null 0 0 null 0 0 tk-1 1 1000 Department 1 Zhang San 2007-1-1 6 6000
Class A a1 1 * 2 1500 null 0 0 null 0 0 tk-2 2 3000 Department 1 Zhang San 2007-1-1 7 10 500
Class A a2 1 * 3 2000 null 0 0 null 0 0 tk-3 3 6000 Department 1 Zhang San 2007-1-1 13 26000


2. Inventory of goods

Cargo category Cargo name Cargo specifications Cargo price Stock balance amount Stock balance amount Date

Class a a1 1 * 2 1000 6 6000 2007-1-1

Class a a1 1 * 2 1500 7 10500 2007-1-1

Class a2 1 * 3 2000 13 26000 2007-1-1
* /

/ *

Explanation:

The detailed account of goods entering and leaving the warehouse can be queried by date range
The inventory of goods can be accurately calculated on any day. For example, if there is no record of inbound and outbound on the day of 2007-1-2, then it will be performed on
When querying, you can also get the above report data

* /

fanfan1980

--1
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,
Receiving Department, Recruiting Person, Date = Date of Warehousing, Balance Quantity = Quantity, Balance Amount = Goods Price * Quantity
from test as A

fanfan1980

How much is the balance of the returned goods and how is the balance calculated?

fanfan1980

--2
select cargo category, cargo name, cargo specification, cargo price, inventory balance quantity = sum (case when storage status = 0 then-quantity else quantity end),
Inventory balance amount = sum (case when out of storage status = 0 then -quantity else quantity end) * goods price, date = max
from test
group by cargo category, cargo name, cargo specification, price
--result
Cargo category Cargo name Cargo specifications Cargo price Stock balance quantity Stock balance amount Date
-------------------- -------------------- ---------- ---------- --------------------- ----------- -------- ------------- ------------------------------------- -----------------
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 row (s) affected)

牛牛很乖

fanfan1980
-------------------------------------
Outbound returns balance = last balance + returns

In fact, the return from the warehouse is the process of re-entry.

fanfan1980

The first question is there a problem with the amount of balance you gave?

牛牛很乖

fanfan1980
--------------------------------
There is no problem with the balance of the first question, in fact the balance of the first question is the same as the balance of the second question.

All 6, 7, 13

fanfan1980

The balance of incoming and outgoing balances?

牛牛很乖

The entire detailed ledger process is performed in batches, that is, it is incremented according to the id column in the test table.

Balance amount = (incoming number + withdrawal number)-outgoing number

smallwarrior

Haha, look at the accounting bill design