Skip to Content
0

making report inventory

Dec 29, 2016 at 03:33 AM

67

avatar image

Dear All Master,

any master give me a hand to make a report as follow format :

Itemcode | Binlocation | Cost | Unitprice | Qty |

i hope any master assistant me for tthis.

Thanks.

Best regards,

Harry

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Arun Kumar Jan 30, 2017 at 06:56 AM
0

Hi Harry,

pls try below query and revert back

SELECT distinct T0.ITEMCODE , T1.FrgnName as 'Part No', T1.ItemName, T2.ItmsGrpNam, T0.WhsCode, T0.ONHAND as 'Total Qty', T0.ONHAND*T0.AVGPRICE as 'TotalPrice' ,T1.[ManBtchNum], T1.[ManSerNum],
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 30 THEN T0.ONHAND END '>30 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 30THEN T0.ONHAND*T0.AVGPRICE END '>30 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 60 THEN T0.ONHAND END '>60 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 60THEN T0.ONHAND*T0.AVGPRICE END '>60 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END '>90 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90THEN T0.ONHAND*T0.AVGPRICE END '>90 Days(Value)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND END '>180 Days(Qty)',
CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 180 THEN T0.ONHAND*T0.AVGPRICE END '>180 Days(Value)'
FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE
INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD left join ibt1 t3 on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode
WHERE
T0.ONHAND>0 AND (T0.WhsCode ='[%0]' or '[%0]' = '') and (T2.ItmsGrpNam = '[%1]' or '[%1]' = '')

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Arun thanks for your help, btw i have other request for show follow format :

Item Code | Whs Code | Whs name | Qty | Bin|

could u gimme a hand ?

0