求个关于库龄分析的算法_MSSQL_编程问答 问题: 求个关于库龄分析的算法

解决方案1:


use Tempdb
go
--> --> 听雨停了-->测试数据
 
if not object_id(N'Tempdb..#tabA') is null
drop table #tabA
Go
Create table #tabA([产品] nvarchar(21),[入库时间] nvarchar(26),[入库数量] int)
Insert #tabA
select N'A',N'12月1日',20 union all
select N'A',N'11月18日',10 union all
select N'A',N'10月18日',15 union ALL --A改成了18号
select N'A',N'9月20日',30 union all
select N'A',N'8月3日',25 union all
select N'B',N'12月1日',20 union all
select N'B',N'11月18日',10 union all
select N'B',N'10月1日',15 union all --B没改(数据上面复制下来的)
select N'B',N'9月20日',30 union all
select N'B',N'8月3日',25
if not object_id(N'Tempdb..#tabB') is null
drop table #tabB
Go
Create table #tabB([产品] nvarchar(21),[结存数] int)
Insert #tabB
select N'A',50 UNION ALL
select N'B',80
--测试数据结束
--先删除#tabC表,因为后面会用到
if not object_id(N'Tempdb..#tabC') is null
drop table #tabC
GO
--说实话你这里面的时间搞成这样也是醉了,好好的日期类型不用搞成几月几日干嘛啊。
;WITH cte AS (
SELECT *,cast(('2017-'+replace(replace(入库时间,'月','-'),'日','')) as date) as dt,ROW_NUMBER() OVER(PARTITION BY 产品 order BY cast(('2017-'+replace(replace(入库时间,'月','-'),'日','')) as date) desc) as rn
FROM #tabA
),
cte2 AS(
SELECT *,
CASE WHEN dt>DATEADD(DAY,-30,GETDATE())
THEN '1'
WHEN dt<DATEADD(DAY,-30,GETDATE()) AND dt>DATEADD(DAY,-60,GETDATE())
THEN '2'
WHEN dt<DATEADD(DAY,-60,GETDATE()) AND dt>DATEADD(DAY,-90,GETDATE())
THEN '3'
WHEN dt<DATEADD(DAY,-90,GETDATE())
THEN '4'
END AS title
FROM cte
),
cte3 AS (
SELECT 产品,title,sum(入库数量) as cnt,row_number() over(PARTITION by 产品 order by title) as rn
FROM cte2 GROUP BY  产品,title
),
cte4 AS (
SELECT a.*, b.结存数-a.cnt as total_cnt FROM cte3 a
INNER JOIN #tabb b ON a.产品=b.产品
WHERE rn=1
UNION ALL
SELECT a.*, b.total_cnt-a.cnt        
FROM cte3 a
INNER JOIN cte4 b ON a.rn=b.rn+1 AND a.产品=b.产品
),
cte5 AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY 产品 order by total_cnt desc) as rn_2 FROM cte4
WHERE total_cnt<=0
),
--统计每个产品在 (30天内 30-60天 61到90天 91天以上) 的真实库存数据
cte6 AS (
SELECT 产品,title,cnt FROM cte4
WHERE total_cnt>0
UNION ALL
SELECT 产品,title,cnt-abs(total_cnt) FROM cte5 
WHERE rn_2=1
),
cte7 AS (
SELECT a.*,b.结存数, b.结存数-a.入库数量 as cnt FROM cte a
INNER JOIN #tabb b ON a.产品=b.产品
WHERE rn=1
UNION ALL
SELECT a.*,b.结存数, b.cnt-a.入库数量        
FROM cte a
INNER JOIN cte7 b ON a.rn=b.rn+1 AND a.产品=b.产品 
),
cte8 AS (
SELECT 产品,入库时间,结存数,row_number() over(PARTITION by 产品 order by dt desc )  as rn
FROM cte7 
WHERE cnt<=0
),
--获取每个产品的最早入库时间
cte9 AS (
SELECT 产品,入库时间,结存数 FROM cte8 
WHERE rn=1
),
--建立辅助表,用以连接上面的cte6
cte10 AS (
SELECT 1 AS id ,'30天内' as title union all
SELECT 2 AS id ,'31-60天' as title union all
SELECT 3 AS id ,'61到90天' as title union all
SELECT 4 AS id ,'91天以上' as title
)
--最终数据插入#tabC中
SELECT a.产品,a.cnt,b.入库时间,b.结存数,c.title 
into #tabC 
FROM cte6  a
INNER JOIN cte9 b ON a.产品=b.产品
RIGHT JOIN cte10 c ON a.title=c.id
--查询最终数据
SELECT * FROM #tabC
--#tabC表行转列显示
DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[title]+']'  from #tabC for xml PATH('')),1,1,'')
set @sql ='SELECT * from #tabC pivot(max(cnt)for title in('+@name+'))a where isnull(产品,'''')<>'''''
PRINT @sql
EXEC( @sql) 

 求个关于库龄分析的算法

1 2 下一页
上一篇SQL2000编号排列问题
下一篇SQL语句求教。审批流程中,如果查出刚到轮到自己的审批数据
明星图片
相关文章
《 求个关于库龄分析的算法》由码蚁之家搜集整理于网络,
联系邮箱:mxgf168#qq.com(#改为@)