Re: Can pure SQL provide AR/AP reports?

CREATE TABLE AccountHistory
(PRIMARY KEY (customer, trans_date, trans_type),  customer CHAR(10) NOT NULL,
 trans_date DATE NOT NULL,
 trans_type CHAR(2) NOT NULL,
 amount DECIMAL(12, 4) NOT NULL);

INSERT INTO AccountHistory VALUES ('a', '2003-01-01', 'd', 100);
INSERT INTO AccountHistory VALUES ('a', '2003-02-01', 'd', 200);
INSERT INTO AccountHistory VALUES ('a', '2003-03-01', 'd', 300);
INSERT INTO AccountHistory VALUES ('a', '2003-04-01', 'd', 400);
INSERT INTO AccountHistory VALUES ('a', '2003-05-01', 'd', 500);
INSERT INTO AccountHistory VALUES ('a', '2003-06-01', 'd', 600);
INSERT INTO AccountHistory VALUES ('a', '2003-01-01', 'p', -90);
INSERT INTO AccountHistory VALUES ('a', '2003-02-05', 'p', -160);
INSERT INTO AccountHistory VALUES ('a', '2003-02-27', 'p', -250);
INSERT INTO AccountHistory VALUES ('a', '2003-02-28', 'p', -100);
INSERT INTO AccountHistory VALUES ('a', '2003-04-02', 'p', -450);

CREATE TABLE Calendar (cal_date DATE NOT NULL PRIMARY KEY);

INSERT INTO Calendar VALUES ('2003-01-31');
INSERT INTO Calendar VALUES ('2003-02-28');
INSERT INTO Calendar VALUES ('2003-03-31');
INSERT INTO Calendar VALUES ('2003-04-30');
INSERT INTO Calendar VALUES ('2003-05-30');
INSERT INTO Calendar VALUES ('2003-06-30');

SELECT A1.customer, C1.cal_date, SUM(amount)   FROM Calendar AS C1, AccountHistory AS A1  WHERE trans_date <= C1.caL_date
GROUP BY A1.customer, C1.cal_date; Received on Fri Jun 20 2003 - 23:02:33 CEST