quote:

---

Originally posted by visakh16

how does you capture change of locations? what's the significance of handedout?

---

quote:

---

Originally posted by MrPeroni

quote:

---

Originally posted by visakh16

how does you capture change of locations? what's the significance of handedout?

---

A items location can change if the item has been sold, at the inventory, if the item is transported from a MainStorage to Storage10 etc. Did I understand your question right?

If a item is labeld as "handed out" it has been handed out without having a connection to a Order. If a item is listed as "missing" it has not been found at the inventory.

---

DECLARE @datFrom datetime, @datTo datetime; 

SET @datFrom = '2008-04-01' 

SET @datTo = '2008-07-05' 

SELECT lo1.vc_location_desc AS Plats, imn.strName AS Produkt, i1.strItemBarCode AS BarCode, i1.dtModified AS Datum, a.strFirstname AS Förnamn, a.strLastName AS Efternamn

FROM tbl_location lo1
INNER JOIN tbl_item i1 ON lo1.i_location_id=i1.intLocationID
INNER JOIN tbl_itemName imn ON i1.intNameID=imn.intID
INNER JOIN tbl_admin a ON i1.intAdminID=a.ingAdmin
INNER JOIN (SELECT i2.intNameID,MAX(i2.dtModified) AS MaxDate
FROM tbl_item i2
INNER JOIN tbl_location lo2
ON lo2.i_location_id=i2.intLocationID
GROUP BY i2.intNameID
HAVING SUM(CASE WHEN lo2.vc_location_desc LIKE '%Missing%' THEN 1 ELSE 0 END)=1) tmp
ON tmp.intNameID=i1.intNameID
AND tmp.MaxDate=i1.dtModified
WHERE i1.dtModified > @datFrom
AND i1.dtModified < @datTo
AND lo1.vc_location_desc NOT LIKE '%Missing%'
ORDER BY i1.dtModified DESC