select
d.DocumentNumber as [Delivery number],
d.ReceiptDate as [Receipt Date],
tmp1.[Total Cartons],
tmp2.[Total Units],
tmp1.[Cartons Received],
tmp2.[Units Received],
tmp1.[Cartons Open]
From
Document d
inner join 
(SELECT Document_ID,
        COUNT(DISTINCT c.CartonStatus_ID) as [Total Units],
        SUM(CASE 
            WHEN cl.Codevalue='Carton Closed' 
            THEN 1 
            ELSE 0 
            END) AS  [Cartons Received],
        SUM(CASE 
            WHEN cl.Codevalue='Carton Open' 
            THEN 1 
            ELSE 0 
            END) AS  [Cartons Open]
FROM Carton c 
INNER JOIN codelist cl
ON cl.Codelist_id = c.CartonStatus_ID
GROUP BY Document_ID)tmp1
on tmp1.Document_Id = d.Document_Id
INNER JOIN (
SELECT Document_ID,
       Sum(QtyShipped) as [Total Units],
       Sum(CD.QtyReceived) as [Units Received],
FROM Carton c 
INNER JOIN CartonDetail cd 
on cd.Carton_Id = c.Carton_Id
GROUP BY Document_ID
)tmp2
ON tmp2.Document_ID=d.Document_Idinner join Stores s on s.Store_Id = d.SourceId
Inner join Codelist cl on cl.Codelist_id = c.CartonStatus_Id
inner join dbo.Codelist cl1 on cl1.Codelist_Id = d.DocType
left outer join CartonDetail cd on cd.Carton_Id = c.Carton_Id
Left outer join Codelist cl2 on cl2.Codelist_Id = cd.CartonDetailType_Id
Where
cl.Codevalue='Carton Open'
and s.StoreType =5
and not d.DocumentNumber is null
and d.destinationId= 8637
and cl1.CodeValue = 'JaxReceipts'
and cl2.Codevalue='Carton Details'
Group by DocumentNumber, d.Receiptdate,d.Document_ID
Order by DocumentNumber

quote:

---

Originally posted by renu.khot@gmail.com

Hi,

---