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

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!

"Thorsten Kettner" <thorsten.kettner_at_web.de> wrote in message news:1163688677.769633.234230_at_m73g2000cwd.googlegroups.com...
> Sorry, I understand it now. I feel so foolish. I was confused by the
> sample tables' names. I think there is really a products table
> containing product ids and there related subgroup ids and another table
> of product names containing the products' names and their effective
> date? However, the solution to your question is: You want to have one
> result record for each distinctive date (because either product name or
> subgroup name changes then). This is your inner select. Then join the
> two tables, and determine their maximum date for the given distinct
> date. At last join the tables again adding all the fields you want to
> see:
>
> select Keys.SubGroupId, Products.ProductName, SubGroups.SubGroupName,
> Keys.EffectiveDate
> from
> (
> select Products.ProductID, Dates.SubGroupID, Dates.EffectiveDate
> , Max(Products.EffectiveDate) as ProductDate
> , Max(SubGroups.EffectiveDate) as SubGroupDate
> from
> (
> select distinct SubGroupID, EffectiveDate from Products
> union
> select distinct SubGroupID, EffectiveDate from SubGroups
> ) Dates
> join Products on (Products.SubGroupID = Dates.SubGroupID and
> Products.EffectiveDate <= Dates.EffectiveDate)
> join SubGroups on (SubGroups.SubGroupID = Dates.SubGroupID and
> SubGroups.EffectiveDate <= Dates.EffectiveDate)
> group by Products.ProductID, Dates.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
>
> Note 1: Maybe that can be solved more elegantly by using aggregate
> functions.
> Note 2: I suppose that your real tables link a distinct subgroup id to
> each product id, so the part and Products.SubGroupID = Keys.SubGroupID
> will actually not be needed, as product ids get compared already.
>
>
> -----------------------------------------------
> Gummy wrote:
> > > I have two tables:
> > >
> > > PRODUCTS
> > > ProductID ProductName SubGroupID EffectiveDate
> > > 1 Vanilla A 1/1/2000
> > > 1 French Vanilla A 1/1/2001
> > > 1 Yummy Vanilla A 1/1/2003
> > >
> > >
> > > SUBGROUPS
> > > SubGroupID SubGroupName EffectiveDate
> > > A Frozen 1/1/2000
> > > A Ice Cream 2/1/2001
> > > A Dairy 1/1/2002
> > >
> > >
> > > I want to join them where I see each row from both tables , using the
> > > effective dates, but without getting duplicates (createing a
normailzed
> > > table). Here are the results I would like:
> > >
> > > RESULTS
> > > ProductID ProductName SubGroupName EffectiveDateAll
> > > 1 Vanilla Frozen 1/1/2000
> > > 1 French Vanilla Frozen 1/1/2001
> > > 1 French Vanilla Ice Cream 2/1/2001
> > > 1 French Vanilla Dairy 1/1/2002
> > > 1 Yummy Vanilla Dairy 1/1/2003
>
Received on Thu Nov 16 2006 - 09:54:28 CST