FinancialModelling/Files/AR Scripts/02_Invoices.sql at main · ProdataSQL/FinancialModelling · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
DROP TABLE IF EXISTS Invoice;

CREATE TABLE Invoice
(
    InvoiceID INT IDENTITY(1,1)
  , CustomerID INT
  , InvoiceDate DATE
  , DueDate DATE
  , PaidDate DATE
  , CurrencyCode CHAR(3)
  , AmountLocal DECIMAL(18,2)
  , FXRate DECIMAL(18,6)
);

INSERT INTO Invoice
(
    CustomerID
  , InvoiceDate
  , DueDate
  , CurrencyCode
  , AmountLocal
  , FXRate
)
SELECT
    c.CustomerID

  , inv.InvoiceDate

  , DATEADD(
        DAY,
        30 + (ABS(CHECKSUM(NEWID())) % 60),   -- 30–90 day terms
        inv.InvoiceDate
    )

  , c.CurrencyCode

  , CAST(
        100 + (ABS(CHECKSUM(NEWID())) % 9000)
    AS DECIMAL(18,2))

  , CASE c.CurrencyCode
        WHEN 'EUR' THEN 1
        WHEN 'USD' THEN 0.88 + (ABS(CHECKSUM(NEWID())) % 10) / 100.0
        WHEN 'GBP' THEN 1.15 + (ABS(CHECKSUM(NEWID())) % 10) / 100.0
        WHEN 'JPY' THEN 0.006 + (ABS(CHECKSUM(NEWID())) % 5) / 1000.0
        WHEN 'CAD' THEN 0.70 + (ABS(CHECKSUM(NEWID())) % 10) / 100.0
    END

FROM Customer c

CROSS APPLY
(
    SELECT DATEADD(
        DAY,
        - (ABS(CHECKSUM(NEWID())) % 365),   -- last 12 months
        CAST(GETDATE() AS DATE)
    ) AS InvoiceDate
) inv

CROSS JOIN GENERATE_SERIES(1, 150);   -- 150 invoices per customer