货龄计算举例:
已知数据:
Item A
2009-01-01采购 1000 pcs
2009-01-07发出 500 pcs
2009-01-28采购 1200 pcs
2009-02-10发出 1000 pcs
2009-03-28采购 800 pcs
2009-04-01发出 200 pcs
------------------------------------
2009-04-28结存 1300 pcs产生报告如下:
Item On-Hand Aging (1 Month) Aging (3 Months) Aging (6 Months) Aging (1 Year)
A 1300 800 500 0 0
CREATE TABLE ta
(Item varchar(100),FDate datetime,Type varchar(100),quantity int,unit varchar(100))CREATE TABLE tb
(Item varchar(100),FDate datetime,Type varchar(100),quantity int,unit varchar(100))INSERT INTO ta
SELECT 'A','2009-01-01','采购',1000,'PCS' UNION ALL
SELECT 'A','2009-01-07','发出',500,'PCS' UNION ALL
SELECT 'A','2009-01-28','采购',1200,'PCS' UNION ALL
SELECT 'A','2009-02-10','发出',1000,'PCS' UNION ALL
SELECT 'A','2009-03-28','采购',800,'PCS' UNION ALL
SELECT 'A','2009-04-01','发出',200,'PCS'
INSERT INTO tb
SELECT 'A','2009-04-28','结存',1300,'PCS'
已知数据:
Item A
2009-01-01采购 1000 pcs
2009-01-07发出 500 pcs
2009-01-28采购 1200 pcs
2009-02-10发出 1000 pcs
2009-03-28采购 800 pcs
2009-04-01发出 200 pcs
------------------------------------
2009-04-28结存 1300 pcs产生报告如下:
Item On-Hand Aging (1 Month) Aging (3 Months) Aging (6 Months) Aging (1 Year)
A 1300 800 500 0 0
CREATE TABLE ta
(Item varchar(100),FDate datetime,Type varchar(100),quantity int,unit varchar(100))CREATE TABLE tb
(Item varchar(100),FDate datetime,Type varchar(100),quantity int,unit varchar(100))INSERT INTO ta
SELECT 'A','2009-01-01','采购',1000,'PCS' UNION ALL
SELECT 'A','2009-01-07','发出',500,'PCS' UNION ALL
SELECT 'A','2009-01-28','采购',1200,'PCS' UNION ALL
SELECT 'A','2009-02-10','发出',1000,'PCS' UNION ALL
SELECT 'A','2009-03-28','采购',800,'PCS' UNION ALL
SELECT 'A','2009-04-01','发出',200,'PCS'
INSERT INTO tb
SELECT 'A','2009-04-28','结存',1300,'PCS'
Item On-Hand Aging(1 Month) Aging (3 Months) Aging (6 Months) Aging (1 Year)
A 1300 800 500 0 0On-Hand就是现有库存,
1 Month就是距2009-4-28前一个月,也就是2009-3-28到2009-4-28的数据
3 Month......
6 Month......
1 Year......
FROM (
汇总语句1
) T1
INNER JOIN (
汇总语句2
) T2 ON T1.ID=T2.ID
...
,sum(case when datediff(month,ta.FDate,getdate()) between 1 and 3 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month1
,sum(case when datediff(month,ta.FDate,getdate()) between 3 and 6 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month3
,sum(case when datediff(month,ta.FDate,getdate()) between 6 and 12 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month6
,sum(case when datediff(month,ta.FDate,getdate())>12 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month12
from ta
left join tb on ta.Item=tb.Item
group by ta.Item,tb.quantity
select ta.Item,tb.quantity
,sum(case when datediff(month,ta.FDate,tb.FDate) between 1 and 3 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month1
,sum(case when datediff(month,ta.FDate,tb.FDate) between 3 and 6 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month3
,sum(case when datediff(month,ta.FDate,tb.FDate) between 6 and 12 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month6
,sum(case when datediff(month,ta.FDate,tb.FDate)>12 then
case when ta.Type='采购' then ta.quantity else -ta.quantity end
else 0 end) as Month12
from ta
left join tb on ta.Item=tb.Item
group by ta.Item,tb.quantity
Item A
2009-01-01采购 1000 pcs
2009-01-07发出 500 pcs
2009-01-28采购 1200 pcs
2009-02-10发出 1000 pcs
2009-03-28采购 800 pcs
2009-04-01发出 200 pcs
------------------------------------
2009-04-28结存 1300 pcs计算过程:
第一步:以2009-04-28的结存为计算基础,即在4月28日有存货1300 pcs
第二步:往前倒推,找到于3月28日发生的第一笔存货增加的交易(在本例中为采购收货,下同),数量为800 pcs。根据先进先出的假设,目前的存货为1300,那么这1300当中,有800是在3月28日进来的。所以属于1个月货龄的情况。剩余的500 pcs是在此之前进来的。
第三步:再继续往前倒推,找到于1月28日发生的第二笔存货增加的交易,数量为1200 pcs。由此可推断第二步中剩余的500 pcs是跟这一笔交易有关。即500 pcs属于3个月货龄的情况。因全部数量已经分配完毕,所以不再继续倒推计算。
第四步:产生报告如下:
Item On-Hand Aging(1 Month) Aging (3 Months) Aging (6 Months) Aging (1 Year)
A 1300 800 500 0 0
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TA
(Item varchar(100),FDate datetime,Type varchar(100),quantity int,unit varchar(100))CREATE TABLE TB
(Item varchar(100),FDate datetime,Type varchar(100),quantity int,unit varchar(100))INSERT INTO TA
SELECT 'A','2009-01-01','采购',1000,'PCS' UNION ALL
SELECT 'A','2009-01-07','发出',500,'PCS' UNION ALL
SELECT 'A','2009-01-28','采购',1200,'PCS' UNION ALL
SELECT 'A','2009-02-10','发出',1000,'PCS' UNION ALL
SELECT 'A','2009-03-28','采购',800,'PCS' UNION ALL
SELECT 'A','2009-04-01','发出',200,'PCS'
INSERT INTO TB
SELECT 'A','2009-04-28','结存',1300,'PCS'
SELECT T1.quantity
,CASE WHEN T1.quantity-ISNULL(T2.quantity,0)>0 THEN ISNULL(T2.quantity,0) ELSE 0 END
,CASE WHEN T1.quantity-ISNULL(T2.quantity,0)-ISNULL(T3.quantity,0)>0 THEN ISNULL(T3.quantity,0) ELSE CASE WHEN T1.quantity-ISNULL(T2.quantity,0)>0 THEN T1.quantity-ISNULL(T2.quantity,0) ELSE 0 END END
,CASE WHEN T1.quantity-ISNULL(T2.quantity,0)-ISNULL(T3.quantity,0)-ISNULL(T4.quantity,0)>0 THEN ISNULL(T4.quantity,0) ELSE CASE WHEN T1.quantity-ISNULL(T2.quantity,0)-ISNULL(T3.quantity,0)>0 THEN T1.quantity-ISNULL(T2.quantity,0)-ISNULL(T3.quantity,0) ELSE 0 END END
,CASE WHEN T1.quantity-ISNULL(T2.quantity,0)-ISNULL(T3.quantity,0)-ISNULL(T4.quantity,0)-ISNULL(T5.quantity,0)>0 THEN ISNULL(T5.quantity,0) ELSE CASE WHEN T1.quantity-ISNULL(T2.quantity,0)-ISNULL(T3.quantity,0)-ISNULL(T4.quantity,0)>0 THEN T1.quantity-ISNULL(T2.quantity,0)-ISNULL(T3.quantity,0)-ISNULL(T4.quantity,0) ELSE 0 END END
FROM (
SELECT Item,quantity
FROM TB
) T1
LEFT JOIN (
SELECT TOP 1 TA.ITEM,TA.QUANTITY
FROM TA
INNER JOIN TB ON TA.Item=TB.Item
WHERE DATEDIFF(MONTH,TA.FDate,TB.FDate )>=1 AND TA.Type='采购'
ORDER BY TA.FDate DESC
) T2 ON T1.Item=T2.Item
LEFT JOIN (
SELECT TOP 1 TA.ITEM,TA.QUANTITY
FROM TA
INNER JOIN TB ON TA.Item=TB.Item
WHERE DATEDIFF(MONTH,TA.FDate,TB.FDate )>=3 AND TA.Type='采购'
ORDER BY TA.FDate DESC
) T3 ON T1.Item=T3.Item
LEFT JOIN (
SELECT TOP 1 TA.ITEM,TA.QUANTITY
FROM TA
INNER JOIN TB ON TA.Item=TB.Item
WHERE DATEDIFF(MONTH,TA.FDate,TB.FDate )>=6 AND TA.Type='采购'
ORDER BY TA.FDate DESC
) T4 ON T1.Item=T4.Item
LEFT JOIN (
SELECT TOP 1 TA.ITEM,TA.QUANTITY
FROM TA
INNER JOIN TB ON TA.Item=TB.Item
WHERE DATEDIFF(MONTH,TA.FDate,TB.FDate )>=12 AND TA.Type='采购'
ORDER BY TA.FDate DESC
) T5 ON T1.Item=T5.Item
/*
1300 800 500 0 0
*/