Posted by Erland Sommarskog on 10/02/19 11:51

Frank (mrpubnight@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.databases.ms-sqlserver/browse_thread/thread/6017ebd3dc467439/448dda4c48fb808b?lnk=st&q=Hummel++group%3Acomp.databases.ms-sqlserver+author%3AErland+author%3ASommarskog&rnum=12&hl=sv#448dda4c48fb808b
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, 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

[Back to original message]