Skip to content
Advertisement

SQL Conditional Print Statement

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
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement