Skip to content

Instantly share code, notes, and snippets.

@OrenBochman

OrenBochman/_ledger.md

Last active July 18, 2017 07:23
Show Gist options
  • Save OrenBochman/df1af65fe7196987f54114412f2e28ac to your computer and use it in GitHub Desktop.
Save OrenBochman/df1af65fe7196987f54114412f2e28ac to your computer and use it in GitHub Desktop.
Download ZIP
sql_ledger
Raw
_ledger.md

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Clients
    (client_id int NOT NULL AUTO_INCREMENT,
     client_name VARCHAR(25),
     PRIMARY KEY(client_id)
    )
;

INSERT INTO Clients
    (client_id,client_name)
VALUES
    (1,  'c1'),
    (2,  'c2')
;

CREATE TABLE Budgets
    (budget_id int NOT NULL AUTO_INCREMENT,
     client_id int NOT NULL,
     budget_name VARCHAR(25),
     PRIMARY KEY(budget_id),
     FOREIGN KEY(client_id) REFERENCES Clients(client_id) 
    )

;

INSERT INTO Budgets
    (budget_id, client_id, budget_name)
VALUES
    (1, 1, 'b1'),
    (2, 1, 'b2'),
    (3, 2, 'b3'),
    (4, 2, 'b4')
    
;

CREATE TABLE Ledger
    (ledger_id int NOT NULL AUTO_INCREMENT, 
     budget_id int NOT NULL, 
     deposit numeric, 
     transfer_target int, 
     ts datetime,
     note VARCHAR(250),
     PRIMARY KEY(ledger_id),
     FOREIGN KEY(budget_id) REFERENCES Budgets(budget_id),   
     FOREIGN KEY(transfer_target) REFERENCES Budgets(budget_id)
    )
;
    
INSERT INTO Ledger
    (ledger_id, budget_id, deposit, transfer_target,ts,note)
VALUES
    (1, 1,  1000.25, NULL, '2016-01-01 00:00:00','initial deposit to recency'),
    (2, 1,  -250.00, NULL, '2016-02-02 00:00:00',''),
    (3, 1,  -250.00, NULL, '2016-02-03 00:00:00',''),
    (4, 1,  -250.00, NULL, '2016-02-04 00:00:00',''),
    (5, 2,  1000.25, NULL, '2016-03-01 00:00:00','campaign launch'),
    (6, 2,  -250.00, NULL, '2016-03-02 00:00:00',''),
    (7, 2,  -250.00, NULL, '2016-03-03 00:00:00',''),
    (8, 2,  -250.00, NULL, '2016-03-04 00:00:00',''),
    (9, 1,  1500.00, NULL, '2016-02-04 00:00:00',''),
    (10, 3,  1500.00, NULL, '2016-02-04 00:00:00','recency renewed'),
    (11, 3,  1000.25, NULL, '2016-01-01 00:00:00','initial deposit to always on'),
    (12, 3,  -250.00, NULL, '2016-02-02 00:00:00',''),
    (13, 3,  -250.00, NULL, '2016-02-03 00:00:00',''),
    (14, 3,  -250.00, NULL, '2016-02-04 00:00:00',''),
    (15, 4,  1000.25, NULL, '2016-03-01 00:00:00','campaign launch'),
    (16, 4,  -250.00, NULL, '2016-03-02 00:00:00',''),
    (17, 4,  -250.00, NULL, '2016-03-03 00:00:00',''),
    (18, 4,  -250.00, NULL, '2016-03-04 00:00:00',''),
    (19, 4,  1500.00, NULL, '2016-02-04 00:00:00',''),
    (20, 4,  1500.00, NULL, '2016-02-04 00:00:00','Always on renewed')
    
;

Query 1:

-- raw data for ledger

select * from ledger

Results:

| ledger_id | budget_id | deposit | transfer_target |                   ts |                         note |
|-----------|-----------|---------|-----------------|----------------------|------------------------------|
|         1 |         1 |    1000 |          (null) | 2016-01-01T00:00:00Z |   initial deposit to recency |
|         2 |         1 |    -250 |          (null) | 2016-02-02T00:00:00Z |                              |
|         3 |         1 |    -250 |          (null) | 2016-02-03T00:00:00Z |                              |
|         4 |         1 |    -250 |          (null) | 2016-02-04T00:00:00Z |                              |
|         5 |         2 |    1000 |          (null) | 2016-03-01T00:00:00Z |              campaign launch |
|         6 |         2 |    -250 |          (null) | 2016-03-02T00:00:00Z |                              |
|         7 |         2 |    -250 |          (null) | 2016-03-03T00:00:00Z |                              |
|         8 |         2 |    -250 |          (null) | 2016-03-04T00:00:00Z |                              |
|         9 |         1 |    1500 |          (null) | 2016-02-04T00:00:00Z |                              |
|        10 |         3 |    1500 |          (null) | 2016-02-04T00:00:00Z |              recency renewed |
|        11 |         3 |    1000 |          (null) | 2016-01-01T00:00:00Z | initial deposit to always on |
|        12 |         3 |    -250 |          (null) | 2016-02-02T00:00:00Z |                              |
|        13 |         3 |    -250 |          (null) | 2016-02-03T00:00:00Z |                              |
|        14 |         3 |    -250 |          (null) | 2016-02-04T00:00:00Z |                              |
|        15 |         4 |    1000 |          (null) | 2016-03-01T00:00:00Z |              campaign launch |
|        16 |         4 |    -250 |          (null) | 2016-03-02T00:00:00Z |                              |
|        17 |         4 |    -250 |          (null) | 2016-03-03T00:00:00Z |                              |
|        18 |         4 |    -250 |          (null) | 2016-03-04T00:00:00Z |                              |
|        19 |         4 |    1500 |          (null) | 2016-02-04T00:00:00Z |                              |
|        20 |         4 |    1500 |          (null) | 2016-02-04T00:00:00Z |            Always on renewed |

Query 2:

-- selected data for ledger

select c.client_name, 
       b.budget_name, 
       l.ledger_id, 
       l.budget_id, 
       l.deposit
from Ledger as l
     inner join Budgets as b on l.budget_id = b.budget_id 
     inner join Clients as c on b.client_id = c.client_id

Results:

| client_name | budget_name | ledger_id | budget_id | deposit |
|-------------|-------------|-----------|-----------|---------|
|          c1 |          b1 |         1 |         1 |    1000 |
|          c1 |          b1 |         2 |         1 |    -250 |
|          c1 |          b1 |         3 |         1 |    -250 |
|          c1 |          b1 |         4 |         1 |    -250 |
|          c1 |          b1 |         9 |         1 |    1500 |
|          c1 |          b2 |         5 |         2 |    1000 |
|          c1 |          b2 |         6 |         2 |    -250 |
|          c1 |          b2 |         7 |         2 |    -250 |
|          c1 |          b2 |         8 |         2 |    -250 |
|          c2 |          b3 |        10 |         3 |    1500 |
|          c2 |          b3 |        11 |         3 |    1000 |
|          c2 |          b3 |        12 |         3 |    -250 |
|          c2 |          b3 |        13 |         3 |    -250 |
|          c2 |          b3 |        14 |         3 |    -250 |
|          c2 |          b4 |        15 |         4 |    1000 |
|          c2 |          b4 |        16 |         4 |    -250 |
|          c2 |          b4 |        17 |         4 |    -250 |
|          c2 |          b4 |        18 |         4 |    -250 |
|          c2 |          b4 |        19 |         4 |    1500 |
|          c2 |          b4 |        20 |         4 |    1500 |

Query 3:

-- sums for budget items

select c.client_name, 
       b.budget_name, 
       l.ledger_id, 
       l.budget_id, 
       sum(l.deposit)
from Ledger as l
     inner join Budgets as b on l.budget_id = b.budget_id 
     inner join Clients as c on b.client_id = c.client_id 
     group by b.budget_id

Results:

| client_name | budget_name | ledger_id | budget_id | sum(l.deposit) |
|-------------|-------------|-----------|-----------|----------------|
|          c1 |          b1 |         1 |         1 |           1750 |
|          c1 |          b2 |         5 |         2 |            250 |
|          c2 |          b3 |        10 |         3 |           1750 |
|          c2 |          b4 |        15 |         4 |           3250 |

Query 4:

-- sums for budget items

select c.client_name, 
       b.budget_name, 
    --   l.ledger_id, 
       l.budget_id, 
       sum(case when l.deposit < 0 then l.deposit   else 0 end) as deopsit,
       sum(case when l.deposit >= 0 then l.deposit else 0 end) as spend,
       sum(l.deposit) as balance
from Ledger as l
     inner join Budgets as b on l.budget_id = b.budget_id 
     inner join Clients as c on b.client_id = c.client_id 
     group by b.budget_id

Results:

| client_name | budget_name | budget_id | deopsit | spend | balance |
|-------------|-------------|-----------|---------|-------|---------|
|          c1 |          b1 |         1 |    -750 |  2500 |    1750 |
|          c1 |          b2 |         2 |    -750 |  1000 |     250 |
|          c2 |          b3 |         3 |    -750 |  2500 |    1750 |
|          c2 |          b4 |         4 |    -750 |  4000 |    3250 |
Raw
ledger.ddl.sql
CREATE TABLE Clients
(client_id int NOT NULL AUTO_INCREMENT,
client_name VARCHAR(25),
PRIMARY KEY(client_id)
)
;
INSERT INTO Clients
(client_id,client_name)
VALUES
(1, 'c1'),
(2, 'c2')
;
CREATE TABLE Budgets
(budget_id int NOT NULL AUTO_INCREMENT,
client_id int NOT NULL,
budget_name VARCHAR(25),
PRIMARY KEY(budget_id),
FOREIGN KEY(client_id) REFERENCES Clients(client_id)
)
;
INSERT INTO Budgets
(budget_id, client_id, budget_name)
VALUES
(1, 1, 'b1'),
(2, 1, 'b2'),
(3, 2, 'b3'),
(4, 2, 'b4')
;
CREATE TABLE Ledger
(ledger_id int NOT NULL AUTO_INCREMENT,
budget_id int NOT NULL,
deposit numeric,
transfer_target int,
ts datetime,
note VARCHAR(250),
PRIMARY KEY(ledger_id),
FOREIGN KEY(budget_id) REFERENCES Budgets(budget_id),
FOREIGN KEY(transfer_target) REFERENCES Budgets(budget_id)
)
;
INSERT INTO Ledger
(ledger_id, budget_id, deposit, transfer_target,ts,note)
VALUES
(1, 1, 1000.25, NULL, '2016-01-01 00:00:00','initial deposit to recency'),
(2, 1, -250.00, NULL, '2016-02-02 00:00:00',''),
(3, 1, -250.00, NULL, '2016-02-03 00:00:00',''),
(4, 1, -250.00, NULL, '2016-02-04 00:00:00',''),
(5, 2, 1000.25, NULL, '2016-03-01 00:00:00','campaign launch'),
(6, 2, -250.00, NULL, '2016-03-02 00:00:00',''),
(7, 2, -250.00, NULL, '2016-03-03 00:00:00',''),
(8, 2, -250.00, NULL, '2016-03-04 00:00:00',''),
(9, 1, 1500.00, NULL, '2016-02-04 00:00:00',''),
(10, 3, 1500.00, NULL, '2016-02-04 00:00:00','recency renewed'),
(11, 3, 1000.25, NULL, '2016-01-01 00:00:00','initial deposit to always on'),
(12, 3, -250.00, NULL, '2016-02-02 00:00:00',''),
(13, 3, -250.00, NULL, '2016-02-03 00:00:00',''),
(14, 3, -250.00, NULL, '2016-02-04 00:00:00',''),
(15, 4, 1000.25, NULL, '2016-03-01 00:00:00','campaign launch'),
(16, 4, -250.00, NULL, '2016-03-02 00:00:00',''),
(17, 4, -250.00, NULL, '2016-03-03 00:00:00',''),
(18, 4, -250.00, NULL, '2016-03-04 00:00:00',''),
(19, 4, 1500.00, NULL, '2016-02-04 00:00:00',''),
(20, 4, 1500.00, NULL, '2016-02-04 00:00:00','Always on renewed')
;
Raw
ledger.qry.sql
-- raw data for ledger
select * from ledger;
-- selected data for ledger
select c.client_name,
b.budget_name,
l.ledger_id,
l.budget_id,
l.deposit
from Ledger as l
inner join Budgets as b on l.budget_id = b.budget_id
inner join Clients as c on b.client_id = c.client_id
;
-- sums for budget items
select c.client_name,
b.budget_name,
l.ledger_id,
l.budget_id,
sum(l.deposit)
from Ledger as l
inner join Budgets as b on l.budget_id = b.budget_id
inner join Clients as c on b.client_id = c.client_id
group by b.budget_id
;
-- sums for budget items
select c.client_name,
b.budget_name,
-- l.ledger_id,
l.budget_id,
sum(case when l.deposit < 0 then l.deposit else 0 end) as deopsit,
sum(case when l.deposit >= 0 then l.deposit else 0 end) as spend,
sum(l.deposit) as balance
from Ledger as l
inner join Budgets as b on l.budget_id = b.budget_id
inner join Clients as c on b.client_id = c.client_id
group by b.budget_id
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment