库龄分析？

leitnt 2009-08-31 04:04:03
出库表：

编号 日期 数量
----------------------------
001 2009-1-1 30
001 2009-1-15 -2
001 2009-1-18 -5
001 2009-2-1 15
001 2009-2-2 -10
001 2009-2-15 -2

要求查询得出（查询截止日期2009-3-1），需要考虑先进先出：

编号 库龄小于30天 库龄30-89天 库龄90天以上
-------------------------------------------------------------
001 15 11 0

这样的sql语句怎样写？
...全文
1011 39 打赏 收藏 转发到动态 举报
写回复
用AI写文章
39 条回复
切换为时间正序
请发表友善的回复…
发表回复
toroam 2010-09-30
  • 打赏
  • 举报
 回复
SQL:
select
case
--如果大于30天的库存+总消耗小于0,证明总消耗超过30天以后的库存,要使用30天内的库存来进行运算
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and C >0)<0
then
(select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where c>0)
else
--'1'
(select Isnull(sum(c),0) from liupengtttt where datediff(day,b,'2009-03-1') <30 and c>0)
end as c1,
case
--如果大于90天的库存+总消耗小于0,证明总消耗超过90天以后的库存,要使用30-89天的来进行运算
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=90 and C >0)<0
then
case
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and C >0) >=0
then (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and C >0)
else
'0'
end
else
(select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=30 and datediff(day,b,'2009-03-1')<90 and C >0 )
end as c2,
case
--如果大于90天的库存+总消耗大于等于0 证明 90天以后的库存超过总消耗,显示90天后库存与消耗的值,否则显示0
when (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=90 and C >0)>=0
then (select Isnull(sum(c),0)+(select sum(c) from liupengtttt where c <0) from liupengtttt where datediff(day,b,'2009-03-1') >=90 and C >0)
else
'0'
end as c3

引一朋友写的，已验证结果正确（加多种情况验证）
toroam 2010-09-30
  • 打赏
  • 举报
 回复
我不懂SQL，可以这样做吗？
先按段搜出3个段的入库量：15，30，0
再sum（所有出库）=19
做先进先出减法：用sum减入库，从最早的开始，够减就清零，sum扣除。依次直到不够减：库存减去sum，sum＝0，新库存表就是所得
wcfboy1 2010-08-26
  • 打赏
  • 举报
 回复
楼主数据有问题，先进先出需要有批次的
htl258_Tony 2009-09-01
  • 打赏
  • 举报
 回复
[Quote=引用 34 楼 leitnt 的回复:]
引用 33 楼 victorcai2006 的回复:
从执行结果来看，语句应该是没有什么问题，但是发觉我所执行出来的结果和LZ给出的结果不太一致。
想知道LZ的结果是如何得出的？如果我整错了，大家就一阵地拍砖吧……

2009-01-01入库30个，之后的几次出库其实都应该先从这30个中出，共出库19个，所以30天至89天库龄的只有剩下的11个；2009-02-01入库15个，其实这15个都没出库，所以30天以下库龄的是这15个。所谓“先进先出”。
[/Quote]
解决了吗？
leitnt 2009-09-01
  • 打赏
  • 举报
 回复
[Quote=引用 33 楼 victorcai2006 的回复:]
从执行结果来看，语句应该是没有什么问题，但是发觉我所执行出来的结果和LZ给出的结果不太一致。
想知道LZ的结果是如何得出的？如果我整错了，大家就一阵地拍砖吧……
[/Quote]
2009-01-01入库30个，之后的几次出库其实都应该先从这30个中出，共出库19个，所以30天至89天库龄的只有剩下的11个；2009-02-01入库15个，其实这15个都没出库，所以30天以下库龄的是这15个。所谓“先进先出”。
xupeihuagudulei 2009-09-01
  • 打赏
  • 举报
 回复
晚上扫帖
victorcai2006 2009-08-31
  • 打赏
  • 举报
 回复
从执行结果来看，语句应该是没有什么问题，但是发觉我所执行出来的结果和LZ给出的结果不太一致。
想知道LZ的结果是如何得出的？如果我整错了，大家就一阵地拍砖吧……

在执行查询以前，我做了一个数据比对查询：
--查询结果与数据测试比对
select basedate,adddate=datediff(day,basedate,'2009-03-01'),baseAmount from @tmp

/*执行结果：
basedate adddate baseAmount
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000 59 30
2009-01-15 00:00:00.000 45 -2
2009-01-18 00:00:00.000 42 -5
2009-02-01 00:00:00.000 28 15
2009-02-02 00:00:00.000 27 -10
2009-02-15 00:00:00.000 14 -2

（所影响的行数为 6 行）
*/

这个查询就是为了验证自己的结果是否正确。

1、从数据上看，以2009－03－01为截至日期，库龄小于30天的记录如下：
basedate adddate baseAmount
------------------------------------------------------ ----------- ----------
2009-02-01 00:00:00.000 28 15
2009-02-02 00:00:00.000 27 -10
2009-02-15 00:00:00.000 14 -2

而库龄的合计为：15－10－2＝3

2、而库龄大于或等于30天而小于89天的记录如下：
basedate adddate baseAmount
------------------------------------------------------ ----------- ----------
2009-01-01 00:00:00.000 59 30
2009-01-15 00:00:00.000 45 -2
2009-01-18 00:00:00.000 42 -5

而库龄的合计为：30－2－5＝23

3、最后一个大于或等于90天的记录在测试数据中没有，所以这个结果和LZ给出的结果一致。

所以，想向楼主求证一下，到底楼主是不是要的这样的结果？
victorcai2006 2009-08-31
  • 打赏
  • 举报
 回复 


/*---------------------------------------

瘦狼阿亮 | 钱不是问题，问题是没钱！



我乐于享受学习的过程

---------------------------------------*/



--建立测试环境表

declare @tmp table 

(listID varchar(10),baseDate datetime,baseAmount smallint)



/*表字段说明

listID--编号

baseDate--日期

baseAmount--数量

*/



--插入测试数据

insert into @tmp 

select '001','2009-01-01',30 union all

select '001','2009-01-15',-2 union all

select '001','2009-01-18',-5 union all

select '001','2009-02-01',15 union all

select '001','2009-02-02',-10 union all

select '001','2009-02-15',-2 



/*执行结果：

（所影响的行数为 6 行）

*/



--检查测试数据

select * from @tmp



/*执行结果：

listID     baseDate                                               baseAmount 

---------- ------------------------------------------------------ ---------- 

001        2009-01-01 00:00:00.000                                30

001        2009-01-15 00:00:00.000                                -2

001        2009-01-18 00:00:00.000                                -5

001        2009-02-01 00:00:00.000                                15

001        2009-02-02 00:00:00.000                                -10

001        2009-02-15 00:00:00.000                                -2



（所影响的行数为 6 行）

*/



--查询结果与数据测试比对

select basedate,adddate=datediff(day,basedate,'2009-03-01'),baseAmount from @tmp



/*执行结果：

basedate                                               adddate     baseAmount 

------------------------------------------------------ ----------- ---------- 

2009-01-01 00:00:00.000                                59          30

2009-01-15 00:00:00.000                                45          -2

2009-01-18 00:00:00.000                                42          -5

2009-02-01 00:00:00.000                                28          15

2009-02-02 00:00:00.000                                27          -10

2009-02-15 00:00:00.000                                14          -2



（所影响的行数为 6 行）

*/



--执行查询

select 

	listID,

	smallin30_Amount=sum(case when datediff(dd,baseDate,'2009-03-01')<30 then baseAmount else 0 end),

	smallin89_Amount=sum(case when datediff(dd,baseDate,'2009-03-01') between 30 and 89 then baseAmount else 0 end),

	bigin90_Amount=sum(case when datediff(dd,baseDate,'2009-03-01')>=90 then baseAmount else 0 end) 

from @tmp 

group by listID



/*执行结果：

listID     smallin30_Amount smallin89_Amount bigin90_Amount 

---------- ---------------- ---------------- -------------- 

001        3                23               0



（所影响的行数为 1 行）

*/
lsd123 2009-08-31
  • 打赏
  • 举报
 回复
.
topest0302 2009-08-31
  • 打赏
  • 举报
 回复 


if object_id('[tab]') is not null drop table [tb]

go 

create table [tab]([编号] varchar(3),[日期] datetime,[数量] int)

insert [tab]

select '001','2009-1-1',30 union all

select '001','2009-1-15',-2 union all

select '001','2009-1-18',-5 union all

select '001','2009-2-1',15 union all

select '001','2009-2-2',-10 union all

select '001','2009-2-15',-2

--select * from tab

select [编号], sum(case when datediff(dd,[日期],'2009-03-01')<30 then [数量] else 0 end) as '30',

sum(case when datediff(dd,[日期],'2009-03-01') between 30 and 89 then [数量] else 0 end) as '3089',

sum(case when datediff(dd,[日期],'2009-03-01')>=90 then [数量] else 0 end) as '90'

into #in

from tab

where [数量]>0

group by [编号]



select [编号], sum(case when datediff(dd,[日期],'2009-03-01')<30 then [数量] else 0 end) as '30',

sum(case when datediff(dd,[日期],'2009-03-01') between 30 and 89 then [数量] else 0 end) as '3089',

sum(case when datediff(dd,[日期],'2009-03-01')>=90 then [数量] else 0 end) as '90'

into #out

from tab

where [数量]<0

group by [编号]

--select * from #in

--select * from #out



select a.[编号], case when a.[3089]+a.[90]+b.[30]+b.[3089]+b.[90]>=0 then a.[30] else a.[30]+a.[3089]+a.[90]+b.[30]+b.[3089]+b.[90] end as '库龄小于30天',

case when a.[90]+b.[30]+b.[3089]+b.[90]>=0 then a.[3089] else a.[3089]+a.[90]+b.[30]+b.[3089]+b.[90] end as '库龄30-89天',

case when a.[90]+b.[30]+b.[3089]+b.[90]>0 then a.[90]+b.[30]+b.[3089]+b.[90] else 0 end as  '库龄90天以上'

from #in a inner join #out b on a.[编号] = b.[编号]
百年树人 2009-08-31
  • 打赏
  • 举报
 回复 
---测试数据---

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([编号] varchar(3),[日期] datetime,[数量] int)

insert [tb]

select '001','2009-1-1',30 union all

select '001','2009-1-15',-2 union all

select '001','2009-1-18',-5 union all

select '001','2009-2-1',15 union all

select '001','2009-2-2',-10 union all

select '001','2009-2-15',-2

 

---查询---

select 

  a.编号,

  a.日期,

  a.入库数量,

  b.出库总量

into #

from

  (select 编号,日期,数量 as 入库数量 from tb where 数量>0) a

left join

  (select 编号,sum(数量) as 出库总量 from tb where 数量<0 group by 编号) b

on

  a.编号=b.编号



select

  编号,

  [库龄小于30天]=sum(case when datediff(day,日期,'2009-3-1')<30 then 库存 else 0 end),    

  [库龄30-89天]=sum(case when datediff(day,日期,'2009-3-1') between 30 and 89 then 库存 else 0 end),  

  [库龄90天以上]=sum(case when datediff(day,日期,'2009-3-1')>=90 then 库存 else 0 end)

from

(

select 

  编号,

  日期,

  库存=case when 库存<0 then 0 else case when 入库数量<库存 then 入库数量 else 库存 end end

from

(  

select *,(select sum(入库数量) from # where 日期<=t.日期)+出库总量 as 库存 from # t

) t) tt

group by

  编号



drop table #



---结果---

编号   库龄小于30天     库龄30-89天    库龄90天以上     

---- ----------- ----------- ----------- 

001  15          11          0



（所影响的行数为 1 行）
mbh0210 2009-08-31
  • 打赏
  • 举报
 回复 






declare @t1 table( 编号 varchar(10), 日期 date , 数量 int)



insert into @t1 values('001', '2009-1-1',       30 )

insert into @t1 values('001', '2009-1-15',      -2 )

insert into @t1 values('001', '2009-1-18',       -5 )

insert into @t1 values('001', '2009-2-1',       15 )

insert into @t1 values('001', '2009-2-2',     -10 )

insert into @t1 values('001', '2009-2-15',    -2 )







select b.编号,

case when b.[库龄90天以上]+b.[库龄3089天以上负]+b.库龄3089天以上正 + b.库龄小于30天负>=0 then b.库龄小于30天正

   else b.库龄小于30天正 + b.[库龄90天以上]+b.[库龄3089天以上负]+b.库龄3089天以上正 + b.库龄小于30天负

   end as '库龄小于30天',



 case when b.[库龄90天以上] + b.库龄3089天以上负 + b.库龄小于30天负 >=0 then b.库龄3089天以上正

      when b.[库龄90天以上] + b.库龄3089天以上负 + b.库龄小于30天负 + b.库龄3089天以上正 >=0 then 

           b.[库龄90天以上] + b.库龄3089天以上负 + b.库龄小于30天负 + b.库龄3089天以上正

           else 0

           end as '库龄3089天',

 b.[库龄90天以上]

 

 from(

select

a.编号,

(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and 

 DATEDIFF(day,日期,'2009-3-1') >89) as '库龄90天以上',

(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and 

 DATEDIFF(day,日期,'2009-3-1') between 30 and 89 and 数量>=0) as '库龄3089天以上正', 

(select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and 

 DATEDIFF(day,日期,'2009-3-1') between 30 and 89 and 数量<0) as '库龄3089天以上负',

 (select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and 

 DATEDIFF(day,日期,'2009-3-1') <30 and 数量>=0) as '库龄小于30天正',

 (select isnull(SUM(数量),0) from @t1 where 编号=a.编号 and 

 DATEDIFF(day,日期,'2009-3-1') <30 and 数量<0) as '库龄小于30天负'

from @t1 a group by a.编号)b

ks_reny 2009-08-31
  • 打赏
  • 举报
 回复
[Quote=引用 25 楼 leitnt 的回复:]
2009-01-01入库30个，之后的几次出库其实都应该先从这30个中出，共出库19个，所以30天至89天库龄的只有剩下的11个；2009-02-01入库15个，其实这15个都没出库，所以30天以下库龄的是这15个。所谓“先进先出”。再次加分求高人！
[/Quote]
我理解對意思了,但沒有描述正確. 是先進先出.最先入庫的東西優先出庫.
topest0302 2009-08-31
  • 打赏
  • 举报
 回复
期待高人出现
leitnt 2009-08-31
  • 打赏
  • 举报
 回复
2009-01-01入库30个，之后的几次出库其实都应该先从这30个中出，共出库19个，所以30天至89天库龄的只有剩下的11个；2009-02-01入库15个，其实这15个都没出库，所以30天以下库龄的是这15个。所谓“先进先出”。再次加分求高人！
ks_reny 2009-08-31
  • 打赏
  • 举报
 回复
樓主的表設計的不合理,最好加個出入庫流水號和當前庫存字段.
mbh0210 2009-08-31
  • 打赏
  • 举报
 回复
恩，明白了，比较麻烦
soft_wsx 2009-08-31
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 ks_reny 的回复:]
樓主的結果是正確的,只不過不是先進先出,是先進後出的.
[/Quote]相不到一道题目包含成本知识！呵呵，有档次！
soft_wsx 2009-08-31
  • 打赏
  • 举报
 回复
只是一个出库表怕是不可能实现需求！

ks_reny 2009-08-31
  • 打赏
  • 举报
 回复
樓主的結果是正確的,只不過不是先進先出,是先進後出的.
加载更多回复（19）
用友U8自定义库龄分析报表
用友U8自带的库龄分析报表中，若物料进行了调拨，则库龄又进行了重新计算，该自定义库龄分析报表，体现出物料的现存量，最后的采购、委外、其他入库时间。
用友库龄分析补丁
用友库龄分析补丁,U6 3.2PLUS1,U852等用友系统库龄分析补丁。
金蝶云星空库存账龄分析表(SQL)
金蝶云星空库存账龄分析表(SQL)
管理报表10-库存余额、库龄分析表模板.xlsx
管理报表10-库存余额、库龄分析表模板
商品库龄分析
商品库龄分析
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章