SQL Server - Variables Example, Sql Server

Language: Layout:

/*
** Create Tables
*/

CREATE TABLE customers
(
  customer_id           INT NOT NULL,
  customer_name         VARCHAR(100),
  customer_invoice_rate DECIMAL(3,2),
  customer_vat_rate     DECIMAL(3,2),
  CONSTRAINT PK_customers PRIMARY KEY (customer_id)
);

CREATE TABLE customer_invoices
(
  invoice_id          INT NOT NULL,
  invoice_customer_id INT NOT NULL,
  invoice_amount      DECIMAL(19,4),
  CONSTRAINT PK_customer_invoices PRIMARY KEY (invoice_id)
);

/*
** Sample Data
*/

INSERT
  INTO customers
      (customer_id, customer_name, customer_invoice_rate, customer_vat_rate)
VALUES(          1,  'Customer 1',                     1,              0.20),
      (          2,  'Customer 2',                  0.90,              0.20),
      (          3,  'Customer 3',                  0.80,              0.00), -- Not charged VAT
      (          4,  'Customer 4',                     1,              0.20),
      (          5,  'Customer 5',                     1,              0.00)  -- Not charged VAT
;

INSERT
  INTO customer_invoices
      (invoice_id, invoice_customer_id, invoice_amount)
VALUES(         1,                   1,            100),
      (         2,                   2,            200),
      (         3,                   3,            100),
      (         4,                   4,            100),
      (         5,                   5,            100),
      (         6,                   2,            100),
      (         7,                   3,            100),
      (         8,                   4,            100)
;

DECLARE @CustomerID INT = 1;

/*
** Apply discount to invoice in code
*/

SELECT c.customer_id,
       c.customer_name,
       i.invoice_id,
       i.invoice_amount,
       c.customer_invoice_rate * i.invoice_amount AS billed_amount
  FROM customers c
    INNER JOIN customer_invoices i
       ON i.invoice_customer_id = c.customer_id
 WHERE c.customer_id = @CustomerID
;

/*
** Customers not charged VAT (UK Tax)
*/

SELECT c.customer_id,
       c.customer_name,
       i.invoice_id,
       i.invoice_amount,
       c.customer_vat_rate * i.invoice_amount AS VAT_amount
  FROM customers c
    INNER JOIN customer_invoices i
       ON i.invoice_customer_id = c.customer_id
 WHERE c.customer_id = @CustomerID
;

View schema
Execution time: 0,02 sec, rows selected: 3, rows affected: 13, absolute service time: 0,16 sec