declare @t table (产品 char(1),年 int,月 int,期初 int,购入 int,发出 int,期末 int)
insert into @t
select 'A', 2008, 1, 0, 10, 5, 5 union
select 'A', 2008, 2, 5, 20, 2, 23 union
select 'A', 2008, 3, 23, 20, 0, 43 union
select 'A', 2008, 4, 43, 30, 10, 63 union
select 'B', 2008, 1, 0, 10, 5, 5 union
select 'B', 2008, 2, 5, 20, 2, 23 union
select 'B', 2008, 3, 23, 20, 0, 43 union
select 'B', 2008, 4, 43, 30, 10, 63declare @ym datetime
set @ym = '2008-04-30' --期未select 产品,
sum(case when datediff(mm,年月,@ym)=0 then 期末 else 0 end) as 期未库存,
sum(case when datediff(mm,年月,@ym)=0 then case when 总发出+发出>=累计购入+购入 then 0
else case when 累计购入>=总发出 then 购入
else 购入-(总发出-累计购入)
end
end
else 0
end) as 账龄1个月内的库存,
sum(case when datediff(mm,年月,@ym)=1 then case when 总发出+发出>=累计购入+购入 then 0
else case when 累计购入>=总发出 then 购入
else 购入-(总发出-累计购入)
end
end
else 0
end) as 账龄1到2个月,
sum(case when datediff(mm,年月,@ym)=2 then case when 总发出+发出>=累计购入+购入 then 0
else case when 累计购入>=总发出 then 购入
else 购入-(总发出-累计购入)
end
end
else 0
end) as 账龄2到3个月,
sum(case when datediff(mm,年月,@ym)>2 then case when 总发出+发出>=累计购入+购入 then 0
else case when 累计购入>=总发出 then 购入
else 购入-(总发出-累计购入)
end
end
else 0
end) as 账龄大于3个月
from
(
select a.产品,cast(a.年 as varchar(4))+'-'+cast(a.月 as varchar(2))+'-1' as 年月,
a.期初,a.购入,a.发出,a.期末,sum(isnull(b.购入,0)) as 累计购入,c.总发出
from @t a
left join @t b on a.产品=b.产品 and
cast(a.年 as varchar(4))+'-'+cast(a.月 as varchar(2))>cast(b.年 as varchar(4))+'-'+cast(b.月 as varchar(2))
left join (select 产品,sum(发出) as 总发出
from @t
where cast(年 as varchar(4))+'-'+cast(月 as varchar(2))+'-1'<=@ym
group by 产品
) c on a.产品=c.产品
group by a.产品,a.年,a.月,a.期初,a.购入,a.发出,a.期末,c.总发出
) d
where 年月<=@ym
group by 产品/*
产品 期未库存 账龄1个月内的库存 账龄1到2个月 账龄2到3个月 账龄大于3个月
---- ----------- ----------- ----------- ----------- -----------
A 63 30 20 13 0
B 63 30 20 13 0（所影响的行数为 2 行）
*/
declare @t table (产品 char(1),年 int,月 int,期初 int,购入 int,发出 int,期末 int)
insert into @t
select 'A', 2008, 1, 0, 10, 5, 5 union
select 'A', 2008, 2, 5, 20, 2, 23 union
select 'A', 2008, 3, 23, 20, 0, 43 union
select 'A', 2008, 4, 43, 30, 10, 63 union
select 'B', 2008, 1, 0, 10, 5, 5 union
select 'B', 2008, 2, 5, 20, 2, 23 union
select 'B', 2008, 3, 23, 20, 0, 43 union
select 'B', 2008, 4, 43, 30, 10, 63declare @ym datetime
set @ym = '2008-04-30' --期未select 产品,期末库存,
case when 账龄1个月内的库存>0 then 账龄1个月内的库存 else 0 end as 账龄1个月内的库存,
case when 账龄1到2个月 >0 then 账龄1到2个月 else 0 end as 账龄1到2个月,
case when 账龄2到3个月 >0 then 账龄2到3个月 else 0 end as 账龄2到3个月,
case when 账龄大于3个月>0 then 账龄大于3个月 else 0 end as 账龄大于3个月
from (
select 产品,期末库存,
case when 期末库存>一月内购 then 一月内购 else 期末库存 end as 账龄1个月内的库存,
case when 期末库存-一月内购>一月前购 then 一月前购 else 期末库存-一月内购 end as 账龄1到2个月,
case when 期末库存-一月内购-一月前购>二月前购 then 二月前购 else 期末库存-一月内购-一月前购 end as 账龄2到3个月,
case when 期末库存-一月内购-一月前购-二月前购>三月前购 then 三月前购 else 期末库存-一月内购-一月前购-二月前购 end as 账龄大于3个月
from (
select 产品,
sum(case when datediff(mm,年月,@ym)=0 then 期末 else 0 end) as 期末库存,
sum(case when datediff(mm,年月,@ym)=0 then 购入 else 0 end) as 一月内购,
sum(case when datediff(mm,年月,@ym)=1 then 购入 else 0 end) as 一月前购,
sum(case when datediff(mm,年月,@ym)=2 then 购入 else 0 end) as 二月前购,
sum(case when datediff(mm,年月,@ym)>2 then 购入 else 0 end) as 三月前购
from (select *,cast(年 as varchar(4))+'-'+cast(月 as varchar(2))+'-1' as 年月 from @t) a
group by 产品
) b
) c/*
产品 期末库存 账龄1个月内的库存 账龄1到2个月 账龄2到3个月 账龄大于3个月
---- ----------- --------------- ----------- ----------- -----------
A 63 30 20 13 0
B 63 30 20 13 0（所影响的行数为 2 行）
*/