属于包的项目之间的日差异

I'm currently working on a database that has 3 tables:

我目前正在开发一个有3个表的数据库:

Bottles(**BottleCode**, Dsc, Capacity, BottleDate)

The BottleDate column is the date when the bottle was filled.

BottleDate列是瓶子装满的日期。

Pack(**PackCode**, PackType, PackDate)

The PackDate column is the date when the pack was created.

PackDate列是创建包的日期。

Pack-Bottle(**BottleCode**, **PackCode**)

What I want to do is get the PackCode from packs that have a difference of 30 days or more between any of their bottles(BottleDate)

我想要做的是从任何瓶子之间相隔30天或更长时间的包装中获取PackCode(BottleDate)

I was trying to solve this using DATEDIFF() in SQL Server, but I don't know how to get the oldest and more recent date from the pack, so that I could check if the difference between them is over 30.

我试图在SQL Server中使用DATEDIFF()来解决这个问题,但我不知道如何从包中获取最旧和最近的日期,以便我可以检查它们之间的差异是否超过30。

I'm really lost, and I would really appreciate if someone could at least point me in the right direction.

我真的迷路了,如果有人至少能指出我正确的方向,我真的很感激。

Thanks in advance :)

提前致谢 :)

Scenario:

Let's say I have a pack with the code "abc123" that contains two bottles one of them was filled on "2014/05/23" and the other one was filled on "2014/09/17", the result should be " abc123" since it contains two bottles that were filled 30 or more days apart from each other, note that a pack has more than just two bottles.

假设我有一个代码为“abc123”的包装,其中包含两个瓶子,其中一个装满了“2014/05/23”,另一个装满了“2014/09/17”,结果应该是“abc123” “因为它包含两个彼此相隔30天或更长时间的瓶子,请注意一个包装的瓶子不止两瓶。

Now lets say I have another pack with the code "efg456" that has three bottles that were filled on "2012/05/04", " 2012/05/15" and "2012/05/28/ respectively, the pack code " efg456" shouldn't appear as a result since all of it's bottles were filled within 30 days apart from each other

现在让我说我有另一个包含代码为“efg456”的包装,其中有三个瓶子,分别填写在“2012/05/04”,“2012/05/15”和“2012/05/28 /,包装代码” efg456“不应该出现,因为它的所有瓶子都在30天之内相互填充

1 个解决方案

#1

Here is one method using an exists clause:

以下是使用exists子句的一种方法:

select p.*
from packs p
where exists (select 1
              from packbottle pb join
                   bottle b
                   on pb.bottlecode = b.bottlecode
              where pb.packcode = p.packcode and
                    b.bottledate < dateadd(day, -30, p.packdate)
             );

Because you want packs only and not bottles, an exists seems like the appropriate solution.

因为你只想要包装而不是瓶子,所以存在似乎是合适的解决方案。

EDIT:

If you want this on any pair of bottles, then you don't need the packs table at all. In fact, the query is then just a simple aggregation query with a having clause:

如果你想在任何一对瓶子上使用它,那么根本不需要包装表。实际上,查询只是一个带有having子句的简单聚合查询:

select pb.packcode, min(bottledate), max(bottledate)
from packbottle pb join
     bottle b
     on pb.bottlecode = p.bottlecode
group by pb.packcode
having datedif(day, min(bottledate), max(bottledate)) > 30

#1