34,593
社区成员
发帖
与我相关
我的任务
分享
SELECT a.产品 ,
a.入库日期 ,
库存 =CASE WHEN a.库存 > b.出库数量 OR b.出库数量 IS NULL THEN 'Y' ELSE 'N' END
FROM ( SELECT 产品 ,
SUM(期初数) AS 库存 ,
MAX(入库日期) AS 入库日期
FROM ( SELECT 产品 ,
期初数 ,
期初入库日期 AS 入库日期
FROM 期初数据表
UNION ALL
SELECT 产品 ,
入库数量 ,
入库日期
FROM 期初数据表
)
GROUP BY 产品
) AS a
LEFT JOIN ( SELECT 产品 ,
SUM(出库数量) AS 出库数量
FROM 出库表
GROUP BY 产品
) AS b ON b.产品 = a.产品
--只显示有库存
SELECT a.产品 ,
a.入库日期 ,
库存 = 'Y'
FROM ( SELECT 产品 ,
SUM(期初数) AS 库存 ,
MAX(入库日期) AS 入库日期
FROM ( SELECT 产品 ,
期初数 ,
期初入库日期 AS 入库日期
FROM 期初数据表
UNION ALL
SELECT 产品 ,
入库数量 ,
入库日期
FROM 期初数据表
)
GROUP BY 产品
) AS a
LEFT JOIN ( SELECT 产品 ,
SUM(出库数量) AS 出库数量
FROM 出库表
GROUP BY 产品
) AS b ON b.产品 = a.产品
WHERE a.库存 > b.出库数量
OR b.出库数量 IS NULL
;with _init(ID,产品,期初数,期初入库日期)AS(
SELECT 1,'A',100,'2016-11-01' UNION ALL
SELECT 2,'B',0,'2016-11-01' UNION ALL
SELECT 3,'C',200,'2016-11-01'
),_in(ID,产品,入库日期,入库数量)AS(
SELECT 1,'B','2016-11-09',50 UNION ALL
SELECT 2,'C','2016-11-09',100 UNION ALL
SELECT 3,'D','2016-11-10',100
),_out(ID,产品,出库日期,出库数量)AS(
SELECT 1,'A','2016-11-09',100 UNION ALL
SELECT 2,'B','2016-11-10',50 UNION ALL
SELECT 3,'C','2016-11-10',100
),_Stock AS(
SELECT 产品,in_date,SUM(qty) AS qty
FROM (
SELECT o.产品,o.期初数 AS qty,o.期初入库日期 AS in_date FROM _init AS o UNION ALL
SELECT i.产品,i.入库数量 AS qty,i.入库日期 AS in_date FROM _in AS i
) t GROUP BY 产品,in_date
)
SELECT *,CASE WHEN s.qty+ISNULL(ls.pre_inqty,0)>ISNULL(o.out_qty,0) THEN 'Y' ELSE 'N' end FROM _Stock AS s
OUTER APPLY(SELECT SUM(qty) AS pre_inqty FROM _Stock AS ps WHERE ps.产品=s.产品 AND ps.in_date<s.in_date) ls
OUTER APPLY (SELECT SUM(oo.出库数量) AS out_qty FROM _out AS oo WHERE oo.产品=s.产品 AND oo.出库日期>=s.in_date) o
WHERE s.qty+ISNULL(ls.pre_inqty,0)-ISNULL(o.out_qty,0)>0
ORDER BY s.产品,s.in_date
/*
产品 in_date qty pre_inqty out_qty
---- ---------- ----------- ----------- ----------- ----
C 2016-11-01 200 NULL 100 Y
C 2016-11-09 100 200 100 Y
D 2016-11-10 100 NULL NULL Y
*/
;with _init(ID,产品,期初数,期初入库日期)AS(
SELECT 1,'A',100,'2016-11-01' UNION ALL
SELECT 2,'B',0,'2016-11-01' UNION ALL
SELECT 3,'C',200,'2016-11-01'
),_in(ID,产品,入库日期,入库数量)AS(
SELECT 1,'B','2016-11-09',50 UNION ALL
SELECT 2,'C','2016-11-09',100 UNION ALL
SELECT 3,'D','2016-11-10',100
),_out(ID,产品,出库日期,出库数量)AS(
SELECT 1,'A','2016-11-09',100 UNION ALL
SELECT 2,'B','2016-11-10',50 UNION ALL
SELECT 3,'C','2016-11-10',200
),_Stock AS(
SELECT 产品,in_date,SUM(qty) AS qty
FROM (
SELECT o.产品,o.期初数 AS qty,o.期初入库日期 AS in_date FROM _init AS o UNION ALL
SELECT i.产品,i.入库数量 AS qty,i.入库日期 AS in_date FROM _in AS i
) t GROUP BY 产品,in_date
)
SELECT *,CASE WHEN s.qty+ISNULL(ls.pre_inqty,0)>ISNULL(o.out_qty,0) THEN 'Y' ELSE 'N' end FROM _Stock AS s
OUTER APPLY(SELECT SUM(qty) AS pre_inqty FROM _Stock AS ps WHERE ps.产品=s.产品 AND ps.in_date<s.in_date) ls
OUTER APPLY (SELECT SUM(oo.出库数量) AS out_qty FROM _out AS oo WHERE oo.产品=s.产品 AND oo.出库日期>=s.in_date) o
WHERE s.qty+ISNULL(ls.pre_inqty,0)-ISNULL(o.out_qty,0)>0
ORDER BY s.产品,s.in_date