|
|
Posted by Frank on 09/29/47 11:51
Sorry everyone there was a typo and I will expand a little as well.
1) The keys are as follows (both tables have primary ID keys too but
they weren't included in the original question - see brackets below)
CAgyHist:
(CAH_ID PK)
ProdID FK
AgyID FK
CInvHist:
(CIH_ID PK)
ProdID FK
InvID FK
2) ProdID = PK from the products table.
AgyID = PK from the Agency table (i.e. Supplier)
InvID = PK from the InventoryType table (categorization for products)
3) Products in our application can move from supplier to supplier and
can also change their categorization. Each of the history tables
tracks these changes as they occur and when they occur. The start date
is obviously when the product begins with the corresponding agency or
categorization, and the end date is when it finishes (a NULL value
means that the product is still with a given agency or being
categorized in a certain manner.
The problem I want/need to solve is I need a complete historical
account for a product as it moves from agency to agency and from
categorization to categorization and I need it to be on a single report
(table) and chronological, so hence the final table which shows how the
product has moved throughout time.
4) Yes, sorry that was a typo. The CInvHist table records should have
read:
Table 2:
CInvHist (ProdID, InvID,StartDate,EndDate)
1 1 Jan 1, 2006 Jan 15, 2006
1 2 Jan 15, 2006 Jan 23, 2006
1 1 Jan 23, 2006 NULL
Sorry about all that confusion. I'm really hoping that this isn't too
tough or time consuming (from an execution point of view).
Again, any help will be appreciated.
Thanks,
Frank
Erland Sommarskog wrote:
> Frank (mrpubnight@hotmail.com) writes:
> > Basically there are tables that track history and each table tends to
> > track only a specific value housed within a date range. I'm trying to
> > combine the tables to get a snap-shot of the complete history. I'm
> > having problems dealing with the Start/End Dates from the two tables
> > and building the dates in the final table to be broken down by 'history
> > type'.
> >
> > Here are a few sample records and the results I'm trying to achieve:
> >
> > Table 1:
> > CAgyHist (ProdID,AgyID,StartDate,EndDate)
> > 1 1 Jan 1, 2006 Jan 5, 2006
> > 1 2 Jan 5, 2006 Jan 25, 2006
> > 1 1 Jan 25, 2006 NULL
> >
> > Table 2:
> > CInvHist (ProdID, InvID,StartDate,EndDate)
> > 1 1 Jan 1, 2006 Jan 23, 2006
> > 1 2 Jan 23, 2006 Jan 15, 2006
> > 1 1 Jan 15, 2006 NULL
> >
> > Desired End Result:
> > CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)
> > 1 1 1 Jan 1, 2006 Jan 5, 2006
> > 1 2 1 Jan 5, 2006 Jan 15, 2006
> > 1 2 2 Jan 15, 2006 Jan 23, 2006
> > 1 2 1 Jan 23, 2006 Jan 25, 2006
> > 1 1 1 Jan 25, 2006 NULL
> >
> > My challenge thus far has been dealing with the dates as they don't
> > necessarily correspond - from one table to the other.
>
> There should be a fair chance to this in a query (or possibly two
> with help of some temp table). But since it's bit complex, the hour
> is late, and your sample data is unclear, I prefer to ask for
> clarification:
>
> 1) What are the keys of these tables?
> 2) What do they signify?
> 3) What is the combined table supposed to describe?
> 4) Is that interval from Jan 23 to Jan 15 intentional or is a typo?
> In the latter case, can you provide an updated sample?
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|