USE [Northwind]
GO

/****** Object:  StoredProcedure [dbo].[StockLevelAlarmList]    Script Date: 1/20/2023 9:53:47 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[StockLevelAlarmList] 
AS
SELECT 
 ProductID AS 'ID'
,ProductName AS 'Name'
,UnitsOnOrder AS 'On Order'
,UnitsInStock AS 'In Stock'
,ReorderLevel AS 'ReorderAt'
,Concern = 
CASE 
	WHEN (uNITSINSTOCK < uNITSONORDER) THEN 'YES' 
	WHEN uNITSINSTOCK >= uNITSONORDER THEN 'NO'
END 
--,CONVERT(varchar,(UnitsInStock-ReorderLevel)) 
,UnitsInStock-ReorderLevel AS Shortage
FROM dbo.Products 
WHERE (Discontinued='False' AND UnitsInStock < UnitsOnOrder)
GO