Database

SELECT UNITS.ProductionNo, 
   COUNT(UNITS.SerialNo) AS SHIPPED,
   COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS 
   JOIN UNIT_Logs
      ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo

SELECT u.ProductionNo,
   u.SHIPPED,
   l.PACKED
FROM 
(
   SELECT ProductionNo, 
      COUNT(SerialNo) AS SHIPPED
   FROM UNITS 
   WHERE ScanStatus = 'SHIPPED'
   GROUP BY ProductionNo
) u
LEFT OUTER JOIN 
(
   SELECT ProductionNo, 
      COUNT(SerialNo) AS PACKED
   FROM UNIT_Logs 
   WHERE Old_Status = 'PACKED'
   GROUP BY ProductionNo  
) l
ON l.ProductionNo = u.ProductionNo

<<tash18>> wrote:

distinct records

-- Table
SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute 
FROM TableA A 
INNER JOIN TableB B 
ON A.ID = B.ID 
INNER JOIN TableC C ........

--or 
-- Concatenate
SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo
FROM TableA A 
INNER JOIN TableB B 
ON A.ID = B.ID 
INNER JOIN TableC C ........

pebrian27 wrote:

but I will limit the result set to 10K max

pebrian27 wrote:

Query can take minutes even hours

Mycroft Holmes wrote:

pebrian27 wrote:
Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.