Hello,
I'm trying to achieve the following for SAP B1: get a stock report in different bin locations for a date in the past. I already found 2 queries for either of these problems:
1. SELECT T1.WhsName, T0.ItemCode, Max(T0.Dscription) 'Item Name', SUM(T0.InQty-T0.OutQty) 'On Hand'
FROM OINM T0
JOIN OWHS T1 ON T1.WhsCode=T0.Warehouse
WHERE T0.DocDate <= [%0]
GROUP BY T1.WhsName,T0.ItemCode
Having SUM(T0.InQty-T0.OutQty) > 0
Which gives me the stock on a date in the past.
2. select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as "On Hand", t2.BinCode, t2.SL1Code, t2.SL2Code, t0.WhsCode
from OITW T0
inner join OITM t1 on t0.ItemCode = t1.ItemCode
inner join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode
inner join OBIN T2 on t2.AbsEntry = t3.BinAbs
order by t2.SL1Code, t2.SL2Code, t0.ItemCode
Which gives me the stock on a particular bin location
Somehow these 2 queries should be combined (i think) to make my report. However, lacking some knowledge of SQL I'm having trouble doing this.
Thanks