quick help

[tonyvee1973]

Hi All

I have a query i am trying to amend slightly but having difficulties.
The query is below, i have a field of STK_PHYSICAL which is highlighted. I also have a field named FREETYPE8 (Highlighted) which i need to show the words "LOW" if the STK_PHYSICAL is less than 6.
Any help really would be appreciated
Many thanks


SELECT dbo.STK_STOCK.STKCODE,
'' AS Spare,
dbo.STK_STOCK.STKNAME,
'' AS UnitOfMeasure,
'' AS UnitOfSales,
dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1 AS RRP,
'' AS PriceCode,
--StockLocation.STK_PHYSICAL,
(SELECT CASE WHEN SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) IS NULL THEN 0 ELSE SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) END
FROM dbo.STK_LOCATION WHERE dbo.STK_LOCATION.LOC_STOCK_CODE = dbo.STK_STOCK.STKCODE AND dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT','DEESET')) AS Stk_Physical,
PopDetails.POD_QTYRESERVED AS QuantityDue,

--POPDetails AS QuantityDue,

CONVERT(CHAR(8), PopDetails.POD_REQDATE, 112) AS NextDelivery,

-- (SELECT TOP 1 CONVERT(CHAR(8), POD_REQDATE, 112)
-- FROM POP_DETAIL
-- WHERE POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
-- GROUP BY POD_STOCK_CODE, POD_REQDATE
-- ORDER BY POD_REQDATE ASC
-- ) AS NextDelivery,

'' AS ColourCode,
'' AS ProductGroup,
CASE dbo.STK_STOCK3.STK_USRFLAG2 when 1 then 'Y' ELSE 'N' END as Discontinued,
CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END as Dead,
dbo.STK_STOCK.STK_BARCODE AS BarCode,
'' AS MinimumMultiples,
'' AS GenericCode5,
'' AS GenericCode6,
dbo.STK_STOCK.STK_SORT_KEY,
dbo.STK_STOCK.STK_SORT_KEY1,
'' AS FreeType3,
'' AS FreeType4,
'' AS FreeType5,
'' AS FreeType6,
'' AS FreeType7,
'' AS FreeType8,
'' AS BreakCode,
ROUND(dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1, 2) AS STK_COSTPRICE,
'' AS MinimumPrice,
'' AS RangeCode,
'' AS MinimumPromotional,
'' AS TaxCode,
'' AS MinimumOrderQuantity,
'' AS FutureStock
FROM dbo.STK_STOCK
INNER JOIN dbo.STK_STOCK_2
ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2
INNER JOIN dbo.STK_STOCK3
ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3
-- INNER JOIN (
-- Select dbo.STK_LOCATION.LOC_STOCK_CODE,
-- SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
-- From dbo.STK_LOCATION
-- Group By dbo.STK_LOCATION.LOC_STOCK_CODE
-- ) As StockLocation
-- On dbo.STK_STOCK.STKCODE = StockLocation.LOC_STOCK_CODE and dbo.STK_LOCATION.LOC_CODE NOT IN ('CR','RE','REJ')
INNER JOIN (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE
From (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE,
Row_Number() Over (Partition By POD_STOCK_CODE Order By CASE WHEN POD_QTYRESERVED > 0 THEN 0 ELSE 1 END, POD_REQDATE) As RowId
From POP_DETAIL


) As ALL_POP_DETAILS

Where RowId = 1
) As PopDetails
On dbo.STK_STOCK.STKCODE = PopDetails.POD_STOCK_CODE
-- LEFT OUTER JOIN
-- (SELECT TOP 1 POD_STOCK_CODE, SUM(ISNULL(POD_QTYRESERVED, 0)) AS QTY
-- FROM POP_DETAIL
-- WHERE POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
-- GROUP BY POD_STOCK_CODE, POD_REQDATE
-- ORDER BY QTY DESC, POD_REQDATE ASC
-- )AS POPDetails ON POPDetails.POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
WHERE dbo.STK_STOCK3.STK_USRFLAG1 <> 1 and dbo.STK_STOCK.STK_SORT_KEY not in ('BILLY')

 

[djj55]

CASE WHEN STK_PHYSICAL < 6 THEN 'LOW' ELSE '' END AS FreeType8

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[tonyvee1973]

Thanks DJ, much appreciated as always.
Some items are not marking but think its an error my end and i need to subtract a field. I have tried below as i need to subtract loc_reserve_out from stk_physical but doesnt seem to work, have i got the syntax incorrect?
CASE WHEN (STK_PHYSICAL - STK_LOCATION.LOC_RESERVE_OUT) <6 THEN 'LOW' ELSE '' END AS FreeType8

 

[tonyvee1973]

Also, didnt explain myself very well, sorry. Its also showing items that have zero stock so i suppose i need the formula to show "LOW" if the stk_physical is between 1 and 6.
Really appreciate the help

 

[tonyvee1973]

Ok, i amended the query and managed to get it working to how it should. However i need to exclude some stock locations but when i try to amend the line highlighted it returns an error saying " Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "stocklocation.LOC_CODE" could not be bound" - i believe this is doen to a join issue but cant seem to figure out.
I have attached the query below again, any help really would be appreciated.



SELECT dbo.STK_STOCK.STKCODE,
'' AS Spare,
dbo.STK_STOCK.STKNAME,
'' AS UnitOfMeasure,
'' AS UnitOfSales,
dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1 AS RRP,
'' AS PriceCode,
--StockLocation.STK_PHYSICAL,
(SELECT CASE WHEN SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) IS NULL THEN 0 ELSE SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) END
FROM dbo.STK_LOCATION WHERE dbo.STK_LOCATION.LOC_STOCK_CODE = dbo.STK_STOCK.STKCODE AND dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT')) AS Stk_Physical,
PopDetails.POD_QTYRESERVED AS QuantityDue,

--POPDetails AS QuantityDue,

CONVERT(CHAR(8), PopDetails.POD_REQDATE, 112) AS NextDelivery,

-- (SELECT TOP 1 CONVERT(CHAR(8), POD_REQDATE, 112)
-- FROM POP_DETAIL
-- WHERE POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
-- GROUP BY POD_STOCK_CODE, POD_REQDATE
-- ORDER BY POD_REQDATE ASC
-- ) AS NextDelivery,

'' AS ColourCode,
'' AS ProductGroup,
CASE dbo.STK_STOCK3.STK_USRFLAG2 when 1 then 'Y' ELSE 'N' END as Discontinued,
CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END as Dead,
dbo.STK_STOCK.STK_BARCODE AS BarCode,
'' AS MinimumMultiples,
'' AS GenericCode5,
'' AS GenericCode6,
dbo.STK_STOCK.STK_SORT_KEY,
dbo.STK_STOCK.STK_SORT_KEY1,
'' AS FreeType3,
'' AS FreeType4,
'' AS FreeType5,
'' AS FreeType6,
'' AS FreeType7,
[highlight Yellow]CASE WHEN STK_PHYSICAL - STK_RESERVE_OUT between 1 and 12 AND dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT') THEN 'LOW' ELSE '' END AS FreeType8,[/highlight]
ROUND(dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1, 2) AS STK_COSTPRICE,
'' AS MinimumPrice,
'' AS RangeCode,
'' AS MinimumPromotional,
'' AS TaxCode,
'' AS MinimumOrderQuantity,
'' AS FutureStock
FROM dbo.STK_STOCK
INNER JOIN dbo.STK_STOCK_2
ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2
INNER JOIN dbo.STK_STOCK3
ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3
-- INNER JOIN (
-- Select dbo.STK_LOCATION.LOC_STOCK_CODE,
-- SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
-- From dbo.STK_LOCATION
-- Group By dbo.STK_LOCATION.LOC_STOCK_CODE
-- ) As StockLocation
-- On dbo.STK_STOCK.STKCODE = StockLocation.LOC_STOCK_CODE and dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT')
INNER JOIN (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE
From (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE,
Row_Number() Over (Partition By POD_STOCK_CODE Order By CASE WHEN POD_QTYRESERVED > 0 THEN 0 ELSE 1 END, POD_REQDATE) As RowId
From POP_DETAIL


) As ALL_POP_DETAILS

Where RowId = 1
) As PopDetails
On dbo.STK_STOCK.STKCODE = PopDetails.POD_STOCK_CODE
-- LEFT OUTER JOIN
-- (SELECT TOP 1 POD_STOCK_CODE, SUM(ISNULL(POD_QTYRESERVED, 0)) AS QTY
-- FROM POP_DETAIL
-- WHERE POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
-- GROUP BY POD_STOCK_CODE, POD_REQDATE
-- ORDER BY QTY DESC, POD_REQDATE ASC
-- )AS POPDetails ON POPDetails.POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
WHERE dbo.STK_STOCK3.STK_USRFLAG1 <> 1 and dbo.STK_STOCK.STK_SORT_KEY not in ('BILLY')