select
t1.FItemID 物料内码,
T1.FQty 库存数量,
t2.FCheckDate 审核日期 ,
t2.FAuxQty 出入库数量
from ( select FItemID,FQty from ICInventory where FQty !=0) t1
left join (select t1.FItemID,t2.FCheckDate,Fauxqty from ICStockBillEntry t1
left join ICStockBill t2 on t1.FInterID = t2.FInterID) t2 on t1.FItemID=t2.FItemID
求根据库存找出迄今为止,180天无出入库记录的相关数据
上述代码查询结果如下:
物料内码 库存数量 审核日期 出入库数量
31340 1.0000000000 2014-04-23 00:00:00.000 5.0000000000
24434 70.0000000000 2014-06-05 00:00:00.000 30.0000000000
24434 224.0000000000 2014-06-05 00:00:00.000 30.0000000000
24434 300.0000000000 2014-06-05 00:00:00.000 30.0000000000
24434 -50.0000000000 2014-06-05 00:00:00.000 30.0000000000
24434 -2.0000000000 2014-06-05 00:00:00.000 30.0000000000
24434 70.0000000000 2014-06-05 00:00:00.000 30.0000000000
24434 224.0000000000 2014-06-05 00:00:00.000 30.0000000000
21780 756.0000000000 2014-06-06 00:00:00.000 16.8100000000
21780 1693.5660000000 2014-06-06 00:00:00.000 16.8100000000
31340 1.0000000000 2014-04-09 00:00:00.000 4.0000000000
24234 35.0000000000 2014-04-19 00:00:00.000 24.0000000000
24234 -20.0000000000 2014-04-19 00:00:00.000 24.0000000000
30561 83.0000000000 2014-06-05 00:00:00.000 4.0000000000
21869 89.8910000000 2014-06-12 00:00:00.000 5.9740000000
------解决思路----------------------
DECLARE @dt datetime -- 分界点日期
SET @dt = Convert(datetime,
Convert(varchar(10),
DateAdd(day,-180,GetDate()),
120),
120)
;WITH table1 AS (
--你的查询语句放这里
)
,t1 AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY 物料内码 ORDER BY 审核日期 DESC) rn
FROM table1
WHERE 审核日期 < @dt
)
,t2 AS (
SELECT DISTINCT 物料内码
FROM table1
WHERE 审核日期 >= @dt
)
SELECT 物料内码,
库存数量,
审核日期,
出入库数量
FROM t1
WHERE rn = 1
AND NOT EXISTS (SELECT *
FROM t2
WHERE t2.物料内码 = t1.物料内码)
------解决思路----------------------
DECLARE @Dt DATETIME = CONVERT(VARCHAR(10),GETDATE()-180)
--使用提供的结果集
;WITH CTE AS(
select
t1.FItemID 物料内码,
T1.FQty 库存数量,
t2.FCheckDate 审核日期 ,
t2.FAuxQty 出入库数量
from ( select FItemID,FQty from ICInventory where FQty !=0) t1
left join (select t1.FItemID,t2.FCheckDate,Fauxqty from ICStockBillEntry t1
left join ICStockBill t2 on t1.FInterID = t2.FInterID) t2 on t1.FItemID=t2.FItemID)
SELECT DISTINCT 物料内码
FROM CTE a
WHERE NOT EXISTS(SELECT * FROM CTE WHERE 物料内码=a.物料内码 AND FCheckDate>@Dt)