Share
ReportCREATE VIEW vw_IVStockAgeing
AS SELECT IV00101.ITEMNMBR AS ITEMNUMBER,
IV00101.ITEMDESC AS ITEMNAME,
IV10200.DATERECD AS DATERECEIVED,
IV10200.QTYRECVD AS QTYRECEIVED,
IV10200.QTYSOLD AS QTYSOLD,
IV00101.ITMCLSCD AS ITEMCLASS,
IV10200.UNITCOST AS UNITCOST,
IV10200.PCHSRCTY AS RECEIPTTYPE,
IV10200.RCPTNMBR AS RECEIPTNO,
IV10200.TRXLOCTN AS LOCATION,
( IV10200.QTYRECVD-IV10200.QTYSOLD ) AS QTYAVAILABLE,
( ( IV10200.QTYRECVD-IV10200.QTYSOLD ) * IV10200.UNITCOST ) AS CURRENTVALUE,
DATEDIFF(day, IV10200.DATERECD, GETDATE()) AS AGEDDAYS,
CASE WHEN DATEDIFF(day, IV10200.DATERECD, GETDATE()) < 0
THEN 'Current'
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 0
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 30
) THEN '0-30 Days' -- 1 month
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 30
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 60
) THEN '31-60 Days' -- 2 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 60
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 90
) THEN '61-90 Days' -- 3 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 90
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 180
) THEN '91-180 Days' -- 6 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 180
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 360
) THEN '181-360 Days' -- 1 year
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 360
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 720
) THEN '> 1 year’ -- 2 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 720
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 1440
) THEN '> 2 years’ -- 3 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 1440
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 2880
) THEN '> 3 years’-- 4 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 2880
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 5760
) THEN '> 4 years’-- 5 years
ELSE ‘> 5 years’ -- Above 5 years
END AS BUCKET
FROM ( IV00101 IV00101
INNER JOIN IV10200 IV10200 ON IV00101.ITEMNMBR = IV10200.ITEMNMBR
)
INNER JOIN IV00102 IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
WHERE IV00102.RCRDTYPE = 1
AND IV10200.PCHSRCTY <> 0
Getting error below:
Msg 102, Level 15, State 1, Procedure vw_IVStockAgeing, Line 37 [Batch Start Line 0]
Incorrect syntax near '>'.
*This post is locked for comments
All responses (
Answers (
291,280
