Combining 2 tables with date ranges

[Frank]

Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want the report to take ages to run, 2) I'm not a big fan of cursors!

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.

I am by no means a database expert of any level and any help would be
greatly appreciated.

Thanks,
Frank.

[Jack Vamvas]

what do you mean by , "the dates don't correspond from 1 table to the
other"?
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Frank" <mr********@hotmail.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...

Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want the report to take ages to run, 2) I'm not a big fan of cursors!

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.

I am by no means a database expert of any level and any help would be
greatly appreciated.

Thanks,
Frank.

[markc600]

From your data, CInvHist has this row

CInvHist (ProdID, InvID,StartDate,EndDate)
1 2 Jan 23, 2006 Jan 15, 2006

which has StartDate *after* the EndDate. Is this what you mean?

[Jason]

It looks like you want to treat the 2 tables as one so you can sort by
the start date? If so, then you can use a union query and use the order
by clause at the end of the second select statement like:
select * from table1
union
select * from table2
order by start date

Jason

[Erland Sommarskog]

Frank (mr********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Frank]

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 (mr********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

Your sample data is a mess, but the usual way is to build a calendar
and join these improperly designed tables together with BETWEEN
predicates, something like:

SELECT C.cal_date, T1.a, T2.b, ..
FROM Calendar AS C, T1, T2
WHERE C.cal_date BETWEEN T1.start_date AND T1.end_date
AND C.cal_date BETWEEN T2.start_date AND T2.end_date
AND .. ;

MIssing or reversed data will not be shown in this query.

[Erland Sommarskog]

Frank (mr********@hotmail.com) writes:

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

That's a bit problematic. It s not clear whether I can trust whether
ProdID, StartDate can be unique, or whether there can be more entries for
the same day and product. In my solution below, I have assumed they are
unique. Then again, if they were there is no reason for that CAH_ID.

Here is a query that works with your sample data. I will have to admit
that I'm not fully certain on how it works, and I would recommend you
to test further. I would also suggest that you check out
http://groups.google.com/group/comp....8dda4c48fb808b
for a similar problem.

CREATE TABLE CAgyHist (ProdID int NOT NULL,
AgyID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))

CREATE TABLE CInvHist (ProdID int NOT NULL,
InvID int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
PRIMARY KEY(ProdID, StartDate))
INSERT CAgyHist(ProdID,AgyID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 5, 2006'
UNION
SELECT 1, 2, 'Jan 5, 2006', 'Jan 25, 2006'
UNION
SELECT 1, 1, 'Jan 25, 2006', NULL

INSERT CInvHist (ProdID, InvID,StartDate,EndDate)
SELECT 1, 1, 'Jan 1, 2006', 'Jan 15, 2006'
UNION
SELECT 1, 2, 'Jan 15, 2006', 'Jan 23, 2006'
UNION
SELECT 1, 1, 'Jan 23, 2006', NULL

SELECT ProdID, AgyID, InvID, StartDate, EndDate
FROM (SELECT a.ProdID, a.AgyID, i.InvID,
CASE WHEN a.StartDate > i.StartDate
THEN a.StartDate
ELSE i.StartDate
END AS StartDate,
CASE WHEN coalesce(a.EndDate, '99991231') <
coalesce(i.EndDate ,'99991231')
THEN a.EndDate
ELSE i.EndDate
END AS EndDate
FROM CAgyHist a
JOIN CInvHist i ON a.ProdID = i.ProdID) AS x
WHERE StartDate < coalesce(EndDate, '99991231')
ORDER BY StartDate, EndDate
go
DROP TABLE CAgyHist
DROP TABLE CInvHist

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx