ITEM WISE BIN LOCATION & STOCK
Actually I wrote one query for "Item wise Bin location" but while I am executing the query,For some items Bin Location displays NULL values, but some more items it displays duplicate bin locations.
SELECT DISTINCT T0.ItemCode, T7.BinCode , isnull(T3.OnHandQty,0) as 'On Hand', T6.Price as 'LP Rate', (T3.OnHandQty * T6.Price) as 'Total Value', T4.[ItmsGrpNam] as 'Item Category', T1.ItemName, T5.FirmName as 'Net / Dis. Price',T1.U_Brand as 'Brand',T1.U_U_Model as 'Model',T1.U_ItemType as 'ItemType',T1.U_Moving,T1.QryGroup1 as 'Equipments',T1.QryGroup2 as 'Accessories',T1.QryGroup3 as 'Spares',T1.QryGroup4 as 'GardenTools',T1.QryGroup5 as 'Non Trading',T1.QryGroup6 as 'Other Equipments',T1.QryGroup7 as 'R&D Equipments', T1.QryGroup8 as 'Equipment Asper Price List', T1.QryGroup10 as 'Asset Items',
T0.WhsCode as 'Warehouse'
FROM OITW T0
INNER JOIN OITM T1 on T0.ItemCode = T1.ItemCode
LEFT JOIN OBIN T7 ON T7.AbsEntry = T0.DftBinAbs
LEFT OUTER JOIN OIBQ T3 on T0.ItemCode=T3.ItemCode and T0.WhsCode = T3.WhsCode
LEFT JOIN OITB T4 ON T4.ItmsGrpCod = T1.ItmsGrpCod
LEFT JOIN OMRC T5 ON T5.FirmCode = T1.FirmCode
INNER JOIN ITM1 T6 ON T1.ItemCode = T6.ItemCode
WHERE T3.OnHandQty <>'0.00' and T1.ItemType <> 'F' and T1.QryGroup8 ='Y' and T6.PriceList = '7'
GROUP BY T0.ItemCode, T1.ItemName, T1.U_Brand ,T0.WhsCode,T6.Price ,T7.BinCode,T1.U_U_Model,T1.U_ItemType,T1.U_Moving,T1.QryGroup1,T1.QryGroup2,T1.QryGroup3,T1.QryGroup4,T4.[ItmsGrpNam],T5.FirmName,T3.OnHandQty,T1.QryGroup5,T1.QryGroup6,T1.QryGroup7,T1.QryGroup8,T1.QryGroup10
ORDER BY T4.[ItmsGrpNam]
Experts for your reference I am attaching screenshot,In that query is displaying duplicate and null bin locations that i have selected.
Hope all are understood my requirement.
Please help me out to make this Query(Bin Location)
Hope for the best reply ASAP.