Day difference between to items that belong to a pack

Question

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

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

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

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

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

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)

---

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.

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.

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

Answer 1

Here is one method using an exists clause:

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:

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