Basic Select - 2

use murach;

-- Calculations

select 
    invoice_number
    ,(invoice_total - payment_total) as balance
from
    invoices;
    
-- IF Conditions    
select 
    invoice_number
    ,IF ( (invoice_total - payment_total) = 0, 'All paid up', 'Balance left') as Status 
from 
    invoices;
    
-- IF Null

SELECT
    payment_date
    ,IFNULL(payment_date, 'No Payment') AS 'Payment_Date'
FROM
    invoices;
    
-- DateDiff

SELECT
    invoice_number
    ,invoice_total
    ,invoice_date
    ,invoice_due_date
    ,DATEDIFF(NOW(), invoice_due_date) as 'Days Passed'
    ,DATEDIFF(invoice_due_date, invoice_date) as 'Diff Days'
FROM 
    invoices;
    
-- CASE Statement

SELECT 
    invoice_number
    ,invoice_total
    ,invoice_date
    ,invoice_due_date
    ,CASE 
        WHEN DATEDIFF(invoice_due_date, invoice_date) > 30 THEN 'Over 30 days past due'
        WHEN DATEDIFF(invoice_due_date, invoice_date) > 0  THEN '1 to 30 days past due'
        ELSE 'Current'
    END AS invoice_status
FROM 
    invoices
ORDER BY 
    invoice_total;
    
    
-- Another CASE implementation

SELECT invoice_number
    ,terms_id
    ,CASE terms_id
        WHEN 1 THEN 'Net due 10 days'
        WHEN 2 THEN 'Net due 20 days'
        WHEN 3 THEN 'Net due 30 days'
        WHEN 4 THEN 'Net due 60 days'
        WHEN 5 THEN 'Net due 90 days'
    END AS terms
FROM 
    invoices;