I am working on two different tables: sales
and inventory
. From the table inventory
, there’s Quantity_Received
and from the table sales
, there’s Quantity_Sold
.
Quantity Remaining in Store
represents the difference between Quantity_Received
and Quantity_Sold
.
When the Quantity Remaining in Store
is equal to 0, I want SQL to print ‘Out of stock’.
When the Quantity Remaining in Store
is greater than 0 but less than 10, I want SQL to print Low stock
.
When the Quantity Remaining in Store
is greater than 10, I want SQL to print Still in stock
Here is the query I am using currently:
SELECT inventory.Quantity_Received AS 'Q1', sales.Quanity_Sold as 'Q2', inventory.Quantity_Received - sales.Quanity_Sold as 'Quantiy Remaining in Store' FROM inventory INNER JOIN sales ON inventory.id=sales.id IF (inventory.Quantity_Received - sales.Quanity_Sold =>10), BEGIN, PRINT 'Still enough in Stock', END ELSE IF (inventory.Quantity_Received - sales.Quanity_Sold =<10) BEGIN PRINT 'Low in Stock' END IF (inventory.Quantity_Received - sales.Quanity_Sold =0), BEGIN, PRINT 'Out of stock'
Advertisement
Answer
Something like
SELECT inventory.Quantity_Received AS 'Q1', sales.Quanity_Sold as 'Q2', inventory.Quantity_Received - sales.Quanity_Sold as 'Quantity Remaining in Store', CASE WHEN (inventory.Quantity_Received - sales.Quanity_Sold >10) THEN 'Still enough in Stock' WHEN (inventory.Quantity_Received - sales.Quanity_Sold =<10 AND inventory.Quantity_Received - sales.Quanity_Sold > 0) THEN 'Low in Stock' ELSE 'Out of Stock' END as Quantity_Remaining_in_Store from inventory inner join sales on inventory.id=sales.id