Select with left outer join - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select with left outer join

Author

Topic

Smitty200
Starting Member

11 Posts

Posted - 2006-06-21 : 15:09:39

I have the following sql query. My problem is that my inclusion of a date range is so the isNull(SUM(od.Sent),0) AS Consumed gives me the amount consumed for the date range. But when I include it, it also limits the number of items returned from tItems. There is 1600 items in this table and I want all of them to be returned with the consumption for the date range. But its only giving me the rows that had somthing consumed in that time perioe. Is there anyway possible to get all items to show and still include a date range? Any help would be appreciated

Kirk

SELECT i.ItemName, i.Description, i.ItemSKU, c.CurrentCount, c.MaximumOnHand, c.ReorderCount, c.LeadTime, isNull(SUM(od.Sent),0) AS Consumed, v.Company,
i.LastPO, i.LastPrice / i.ConversionQTY AS UnitCost, c.CurrentCount * (i.LastPrice / i.ConversionQTY) AS Total
FROM tItems i LEFT OUTER JOIN
tCounts c ON i.ItemSKU = c.ItemSKU LEFT OUTER JOIN
tPurchaseOrderMaster p ON i.LastPO = p.OrderID LEFT OUTER JOIN
tVendors v ON p.VendorID = v.VendorID LEFT OUTER JOIN
tOpenOrdersDetail od ON i.ItemSKU = od.ItemSKU
WHERE i.LastPO != 0 AND (od.D >='6/20/2006' AND od.D <= '6/21/2006')
GROUP BY i.ItemSKU, i.ItemName, i.Description, c.CurrentCount, c.MaximumOnHand, c.ReorderCount, c.LeadTime, v.Company, i.LastPO, i.LastPrice,
i.ConversionQTY
ORDER BY i.ItemSKU

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-21 : 15:27:26

Try this:

SELECT i.ItemName,
       i.Description,
       i.ItemSKU,
       c.CurrentCount,
       c.MaximumOnHand,
       c.ReorderCount,
       c.LeadTime,
       SUM(CASE WHEN od.Sent IS NOT NULL OR (od.D >= '20060620' AND od.D <= '20060621')
                THEN od.Sent
                ELSE 0
           END) AS Consumed,
       v.Company, 
       i.LastPO,
       i.LastPrice / i.ConversionQTY AS UnitCost,
       c.CurrentCount * (i.LastPrice / i.ConversionQTY) AS Total
FROM tItems i
LEFT OUTER JOIN tCounts c
   ON i.ItemSKU = c.ItemSKU
LEFT OUTER JOIN tPurchaseOrderMaster p
   ON i.LastPO = p.OrderID
LEFT OUTER JOIN tVendors v
   ON p.VendorID = v.VendorID
LEFT OUTER JOIN tOpenOrdersDetail od
   ON i.ItemSKU = od.ItemSKU
WHERE i.LastPO != 0 
GROUP BY i.ItemSKU,
         i.ItemName,
         i.Description,
         c.CurrentCount,
         c.MaximumOnHand,
         c.ReorderCount,
         c.LeadTime,
         v.Company,
         i.LastPO,
         i.LastPrice, 
         i.ConversionQTY
ORDER BY i.ItemSKU

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-21 : 15:34:57

[code]SELECT i.ItemName,
i.Description,
i.ItemSKU,
c.CurrentCount,
c.MaximumOnHand,
c.ReorderCount,
c.LeadTime,
SUM(case when od.D BETWEEN '6/20/2006' AND '6/21/2006' THEN od.Sent ELSE 0 END) AS Consumed, v.Company,
i.LastPO,
i.LastPrice / i.ConversionQTY AS UnitCost,
c.CurrentCount * (i.LastPrice / i.ConversionQTY) AS Total
FROM tItems i
LEFT JOIN tCounts c ON i.ItemSKU = c.ItemSKU
LEFT JOIN tPurchaseOrderMaster p ON i.LastPO = p.OrderID
LEFT JOIN tVendors v ON p.VendorID = v.VendorID
LEFT JOIN tOpenOrdersDetail od ON i.ItemSKU = od.ItemSKU
WHERE i.LastPO <> 0
-- AND (od.D >='6/20/2006' AND od.D <= '6/21/2006')
GROUP BY i.ItemSKU,
i.ItemName,
i.Description,
c.CurrentCount,
c.MaximumOnHand,
c.ReorderCount,
c.LeadTime,
v.Company,
i.LastPO,
i.LastPrice,
i.ConversionQTY
ORDER BY i.ItemSKU[/code]

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-21 : 15:44:54

This may perform a little better, but assumes 0-1 hits on tCounts, tPurchaseOrderMaster and tVendors per tItems (GROUP BY removed):

SELECT di.ItemName,
       di.Description,
       di.ItemSKU,
       c.CurrentCount,
       c.MaximumOnHand,
       c.ReorderCount,
       c.LeadTime,
       (SELECT SUM(CASE WHEN od.D >= '20060620' AND od.D <= '20060621'
                   THEN od.Sent
                   ELSE 0
                   END)
        FROM tOpenOrdersDetail od
        WHERE od.ItemSKU = i.ItemSKU
       ) AS Consumed,
       v.Company, 
       i.LastPO,
       i.LastPrice / i.ConversionQTY AS UnitCost,
       c.CurrentCount * i.LastPrice / i.ConversionQTY AS Total
FROM tItems i
LEFT OUTER JOIN tCounts c
   ON i.ItemSKU = c.ItemSKU
LEFT OUTER JOIN tPurchaseOrderMaster p
   ON i.LastPO = p.OrderID
LEFT OUTER JOIN tVendors v
   ON p.VendorID = v.VendorID
WHERE i.LastPO != 0 
ORDER BY i.ItemSKU

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Smitty200
Starting Member

11 Posts

Posted - 2006-06-22 : 18:43:07

Thanks Peter this worked perfectly.

quote:

Originally posted by Peso

SELECT		i.ItemName,
		i.Description,
		i.ItemSKU,
		c.CurrentCount,
		c.MaximumOnHand,
		c.ReorderCount,
		c.LeadTime,
		SUM(case when od.D BETWEEN '6/20/2006' AND '6/21/2006' THEN od.Sent ELSE 0 END) AS Consumed,		v.Company, 
		i.LastPO,
		i.LastPrice / i.ConversionQTY AS UnitCost,
		c.CurrentCount * (i.LastPrice / i.ConversionQTY) AS Total
FROM		tItems i
LEFT JOIN	tCounts c ON i.ItemSKU = c.ItemSKU
LEFT JOIN	tPurchaseOrderMaster p ON i.LastPO = p.OrderID
LEFT JOIN	tVendors v ON p.VendorID = v.VendorID
LEFT JOIN	tOpenOrdersDetail od ON i.ItemSKU = od.ItemSKU
WHERE		i.LastPO <> 0
--		AND (od.D >='6/20/2006' AND od.D <= '6/21/2006')
GROUP BY	i.ItemSKU,
		i.ItemName,
		i.Description,
		c.CurrentCount,
		c.MaximumOnHand,
		c.ReorderCount,
		c.LeadTime,
		v.Company,
		i.LastPO,
		i.LastPrice, 
		i.ConversionQTY
ORDER BY	i.ItemSKU

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources