库存账龄报表开发总结——Sqlserver LAG()的使用

2022-10-26 20:35:33

库存账龄报表开发总结——Sqlserver LAG()的使用

开发需求

根据出入库流水明细，查询对应产品库存状态：包含库龄、库存量及其金额等。

注意:出库按照先入先出的规则进行。

例如:

物品	数量	日期
A	10	2020-01-01
A	20	2020-02-01
A	30	2020-03-03
A	-5	2020-04-10
A	-35	2020-05-15
A	300	2020-06-18
A	-60	2020-11-10
A	90	2020-11-30
A	-100	2020-12-12
B	20	2020-07-15
B	40	2020-10-11
B	-30	2020-11-11

上面表示一个出入库流水明细

-- 建表，并插入数据
CREATE TABLE T_StkFlowDetail(
	id int primary key not null,
	goods_code varchar(20) null,
	num decimal(16,7) null,
	stk_date date not null
)

INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (1, 'A', 10.0000000, '2020-01-01');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (2, 'A', 20.0000000, '2020-02-01');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (3, 'A', 30.0000000, '2020-03-03');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (4, 'A', -5.0000000, '2020-04-10');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (5, 'A', -35.0000000, '2020-05-15');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (6, 'A', 300.0000000, '2020-06-18');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (7, 'A', -60.0000000, '2020-11-10');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (8, 'A', 90.0000000, '2020-11-30');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (9, 'A', -100.0000000, '2020-12-12');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (10, 'B', 20.0000000, '2020-07-15');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (11, 'B', 40.0000000, '2020-10-11');
INSERT INTO [dbo].[T_StkFlowDetail]([id], [goods_code], [num], [stk_date]) VALUES (12, 'B', -30.0000000, '2020-11-11');

以2020-12-31为截止日期去判定最终展示报表形式

物品	库存	0-60天	61-120天	121-180天	181-240天	241-300天	301-360天
A	250	90	0	0	160	0	0
B	30	0	30	0	0	0	0

需求分析
- 根据每次入库时间获取各时间段的库龄
- 根据出库按照先入先出的规则扣减库存
- 根据库龄将对应的库存、金额等相关数据展示
- 综上：该报表的难度在于如何先入先出的扣减库存
函数讲解
- LAG 详细介绍:https://www.yiibai.com/sqlserver/sql-server-lag-function.html
  
  SQL Server LAG()是一个Window函数，它提供对当前行之前的指定物理偏移量的行的访问。
  
  换句话说，通过使用LAG()函数，可以从当前行访问上一行的数据或上一行之前的行，依此类推。
  
  LAG()函数对于将当前行的值与前一行的值进行比较非常有用
```
-- 使用语法
LAG(return_value ,offset [,default]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
```
- DATEDIFF 详细介绍:https://www.yiibai.com/sqlserver/sql-server-datediff-function.html
  
  计算年，月，周等两个日期之间的差值
```
-- 使用语法
DATEDIFF( date_part , start_date , end_date
```

解决方案

查询每个物品各时间段有库存情况下的库龄

SELECT
	goods_code,
	stk_date,
	diff_days,
	current_stk_nums,
	current_stk_nums - ISNULL( LAG ( current_stk_nums ) OVER ( partition BY goods_code ORDER BY stk_date ), 0 ) current_stk_row_nums 
FROM
	(
	SELECT
		goods_code,
		num,
		stk_date,
		DATEDIFF( DAY, stk_date, '2020-12-31' ) diff_days,
		SUM ( CASE WHEN num < 0 THEN num ELSE 0 END ) OVER ( PARTITION BY goods_code ) + SUM ( CASE WHEN num > 0 THEN num ELSE 0 END ) OVER ( PARTITION BY goods_code ORDER BY stk_date ) current_stk_nums 
	FROM
		T_StkFlowDetail 
	WHERE
		stk_date <= '2020-12-31' 
	) t 
WHERE
	current_stk_nums > 0 
	AND num > 0

执行结果

此处建议将上述sql写成函数，方便报表输出

CREATE FUNCTION [dbo].[FUNC_GetStockAge] ( @end_date DATE ) RETURNS TABLE AS RETURN (
	SELECT
		goods_code,
		stk_date,
		diff_days,
		current_stk_nums,
		current_stk_nums - ISNULL( LAG ( current_stk_nums ) OVER ( partition BY goods_code ORDER BY stk_date ), 0 ) current_stk_row_nums 
	FROM
		(
		SELECT
			goods_code,
			num,
			stk_date,
			DATEDIFF( DAY, stk_date, @end_date ) diff_days,
			SUM ( CASE WHEN num < 0 THEN num ELSE 0 END ) OVER ( PARTITION BY goods_code ) + SUM ( CASE WHEN num > 0 THEN num ELSE 0 END ) OVER ( PARTITION BY goods_code ORDER BY stk_date ) current_stk_nums 
		FROM
			T_StkFlowDetail 
		WHERE
			stk_date <= @end_date
		) t 
	WHERE
		current_stk_nums > 0 
	AND num > 0 
)

报表输出语句

SELECT 
	t.goods_code '物品', 
	t.num '库存',
	SUM(IIF(t1.diff_days BETWEEN 0 AND 60,t1.current_stk_row_nums,0)) AS '0-60天',
	SUM(IIF(t1.diff_days BETWEEN 61 AND 120,t1.current_stk_row_nums,0)) AS '61-120天',
	SUM(IIF(t1.diff_days BETWEEN 121 AND 180,t1.current_stk_row_nums,0)) AS '121-180天',
	SUM(IIF(t1.diff_days BETWEEN 181 AND 240,t1.current_stk_row_nums,0)) AS '181-240天',
	SUM(IIF(t1.diff_days BETWEEN 241 AND 300,t1.current_stk_row_nums,0)) AS '241-300天',
	SUM(IIF(t1.diff_days BETWEEN 301 AND 360,t1.current_stk_row_nums,0)) AS '301-360天'
FROM (
	-- 截止2020-12-31各物品的库存
	SELECT goods_code,SUM(num) num FROM T_StkFlowDetail
	WHERE stk_date < '2020-12-31'
	GROUP BY goods_code
	HAVING SUM(num) > 0 
)t
LEFT JOIN FUNC_GetStockAge('2020-12-31') t1 ON t1.goods_code = t.goods_code
WHERE t.num > 0
GROUP BY t.goods_code,t.num

输出结果

码农公寓

库存账龄报表开发总结——Sqlserver LAG()的使用

相关文章