Re: Best way to do a join with effective dates and see all rows

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 16 Nov 2006 08:13:02 -0800
Message-ID: <1163693582.802962.203310@f16g2000cwb.googlegroups.com>

Oops, you are right, something is wrong. In the inner select you must collect all effective dates per product not per subgroup...

One more try:

select Keys.SubGroupId, Products.ProductName, SubGroups.SubGroupName, Keys.EffectiveDate
from
  (
    select Products.ProductID, SubGroups.SubGroupID, Dates.EffectiveDate

      , Max(Products.EffectiveDate) as ProductDate
      , Max(SubGroups.EffectiveDate) as SubGroupDate
    from
      (
        select distinct ProductID, EffectiveDate
        from Products
        union
        select distinct ProductID, SubGroups.EffectiveDate
        from SubGroups
        join Products using(SubGroupID)
      ) Dates

    join Products on (Products.ProductID = Dates.ProductID and Products.EffectiveDate <= Dates.EffectiveDate)

    join SubGroups on (SubGroups.SubGroupID = Products.SubGroupID and SubGroups.EffectiveDate <= Dates.EffectiveDate)

    group by Products.ProductID, SubGroups.SubGroupID, Dates.EffectiveDate
  ) Keys
join Products on (Products.ProductID = Keys.ProductID and Products.SubGroupID = Keys.SubGroupID and Products.EffectiveDate = Keys.ProductDate)
join SubGroups on (SubGroups.SubGroupID = Keys.SubGroupID and SubGroups.EffectiveDate = Keys.SubGroupDate) order by 4,1,2,3

---

Gumbatman schrieb:

> Thorsten,
>
> Thanks for the great answer, I really appreciate it.
>
> Just a quick question, in the first FROM, you have the following:
> select Products.ProductID, Dates.SubGroupID, Dates.EffectiveDate
> , Max(Products.EffectiveDate) as ProductDate
> , Max(SubGroups.EffectiveDate) as SubGroupDate
>
> How is the Products.ProductID referring to the Products table since it is
> based on the FROM that is a Union between the two tables?
>
> I am still working on this, but you have given me a lot to go with.
>
> Thank you!
Received on Thu Nov 16 2006 - 10:13:02 CST