SQL 求大于180天无出库记录的物料

[bingjue1234]

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

[Tiger_Zhao]

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.物料内码)

[zbdzjx]

不考虑效果的情况下，后面增加：

where t2.FItemID not in (
select FItemID from (select t1.FItemID,t2.FCheckDate,Fauxqty from ICStockBillEntry t1
left join  ICStockBill t2 on t1.FInterID = t2.FInterID where t2.FCheckDate>=getdate()-180) t3
)

考虑效率，要改成not in 为not exists

[Ginnnnnnnn]

应该理解成180天以前到今天没出过库呢？ 还是从开始到今天曾经有180天没出库呢？

[bingjue1234]

引用 2 楼 zbdzjx 的回复:

不考虑效果的情况下，后面增加：

where t2.FItemID not in (
select FItemID from (select t1.FItemID,t2.FCheckDate,Fauxqty from ICStockBillEntry t1
left join  ICStockBill t2 on t1.FInterID = t2.FInterID where t2.FCheckDate>=getdate()-180) t3
)

考虑效率，要改成not in 为not exists

t提示 where有语法错误

[hepe00]

只要在你的查询的基础上，找出当前日期getdate()，减去180天后，还小的日期即可。

where t2.FCheckDate<dateadd(day,-180,getdate())

如：

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
where t2.FCheckDate<dateadd(day,-180,getdate())

[bingjue1234]

引用 4 楼 hepe00 的回复:

只要在你的查询的基础上，找出当前日期getdate()，减去180天后，还小的日期即可。

where t2.FCheckDate<dateadd(day,-180,getdate())

如：

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
where t2.FCheckDate<dateadd(day,-180,getdate())

这样只能筛选出180天之前的出入库记录，要查180天内无变动的部分

[bingjue1234]

引用 1 楼 Tiger_Zhao 的回复:

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.物料内码)

可以做成视图吗？

[Tiger_Zhao]

可以啊。把 @dt 用前面的公式替换，从 WITH 关键字开始就是一条语句。

[紫枫昵]

按物料内码分类，找出最近的出库时间，然后看看最近的出库时间跟现在比是不是大于180

[bingjue1234]

引用 9 楼 KanzakiOrange 的回复:

应该理解成180天以前到今天没出过库呢？ 还是从开始到今天曾经有180天没出库呢？

最后一次出入库时间到今天大于180天；

[xilaianzxsc]

其实就是二个时间在做减法

[Ginnnnnnnn]

引用 10 楼 bingjue1234 的回复:

[quote=引用 9 楼 KanzakiOrange 的回复:] 应该理解成180天以前到今天没出过库呢？ 还是从开始到今天曾经有180天没出库呢？

最后一次出入库时间到今天大于180天；[/quote]

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)