Combining Detail Records from different Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining Detail Records from different Tables

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-22 : 17:47:00

Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.

tblShipmentHdr --> tblShipmentDet (Key = ShipmentID)
tblAdjustments --> standalone

Result: combine tblShipmentHdr + attached tblShipmentDet records with
tblAdjustments records.

Would the best approach be to use a UNION SELECT?

@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005'
AS
SELECT
	SHPH.ProductID,
	SHPH.ReceivedDate,
	SHPH.ShipmentNo,
	SHPD.Vendor, 
	SHPD.Quantity, 
	QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
			THEN NULL ELSE SHPH.ReceivedDate
			END,
	QShipQty = CASE WHEN SHPD.TransCd = 'F' 
			THEN NULL
			WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
			THEN NULL 
			ELSE SHPH.ShippingQty
			END,
	PROD.ProductName, 
	QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null
			THEN 0 ELSE MEND.OpeningAccrual
			END
FROM dbo.tblShipmentHdr SHPH 
LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7)
LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID
LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121) 
WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-22 : 17:50:15

UNION ALL
SELECT...

can be used since your schema is fairly close. For columns that don't match up, put '' or 0 or some other data to signify you don't have data for that column in that table.

You could also use an INNER JOIN.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources