u948P1KF

1CREATE TABLE IF NOT EXISTS `currency_lkp` (
2  `currency_id` int(11) NOT NULL AUTO_INCREMENT,
3  `currency` varchar(10) NOT NULL,
4  `currency_label` varchar(100) NOT NULL,
5  `currency_description` varchar(100) NOT NULL,
6  `exchange_rate_USD` float NOT NULL,
7  PRIMARY KEY (`currency_id`)
8) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
9
10INSERT INTO `currency_lkp` (`currency_id`, `currency`, 
11`currency_label`, `currency_description`, `exchange_rate_USD`)
12VALUES
13(1, 'GBP', '£', 'Great British Pound (Sterling)', 0.75),
14(2, 'Euro', '€', 'Euro currency for the Euro Zone', 0.85),
15(3, 'USD', '$', 'United States Dollars', 1),
16(4, 'CLP', '$', 'Chilean Pesos', 639.85),
17(5, 'INR', '&#8377', 'Indian Rupees', 64.8);
18
19CREATE TABLE IF NOT EXISTS `associate_data` (`associate_data_id`
20int(11) NOT NULL AUTO_INCREMENT,
21`currency` varchar(10) NOT NULL, 
22`status` varchar(20) NOT NULL, 
23`invoice_total` float,
24 PRIMARY KEY (`associate_data_id`)
25) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
26
27INSERT INTO `associate_data` (`associate_data_id`, `currency`, 
28`status`, `invoice_total`)
29 VALUES
30(1, 'GBP', 'Paid', 200),
31(2, 'GBP', 'Unpaid', 300),
32(3, 'Euro', 'Paid', 1000),
33(4, 'Euro', 'Unpaid', 2000),
34(5, 'USD', 'Paid', 1200),
35(6, 'USD', 'Unpaid', 1400),
36(7, 'GBP', 'WaitingInvoice', 400),
37(8, 'Euro', 'WaitingInvoice', 1300),
38(9, 'USD', 'WaitingInvoice', 2300),
39(10, 'CLP', 'Paid', 100200),
40(11, 'CLP', 'Unpaid', 110400),
41(12, 'CLP', 'WaitingInvoice', 11200);
42
43MySQL Query in full:
44`
45(
46SELECT cl.currency,
47(SELECT SUM(invoice_total) FROM associate_data ad2
48WHERE ad2.currency = cl.currency  AND ad2.status = 'Paid') AS Paid,
49(SELECT SUM(invoice_total) FROM associate_data ad3
50WHERE ad3.currency = cl.currency  AND ad3.status = 'Unpaid') AS Unpaid,
51(SELECT SUM(invoice_total) FROM associate_data ad4
52WHERE ad4.currency = cl.currency  AND ad4.status = 'WaitingInvoice') AS WaitingInvoice,
53(SELECT IFNULL(Unpaid,0) + IFNULL(WaitingInvoice, 0)) AS TotalToPay
54FROM currency_lkp cl
55)
56
57UNION ALL
58
59(
60SELECT 'Total in USD',
61
62FORMAT(
63SUM(IFNULL(
64(SELECT SUM(invoice_total) FROM associate_data ad5
65WHERE ad5.currency = clu.currency AND (ad5.status = 'Paid'))
66,0.1)/clu.exchange_rate_USD)
67,0) AS TotalUSDPaid,
68
69FORMAT(
70SUM(IFNULL(
71(SELECT SUM(invoice_total) FROM associate_data ad6
72WHERE ad6.currency = clu.currency AND (ad6.status = 'Unpaid'))
73,0.1)/clu.exchange_rate_USD)
74,0) AS TotalUSDUnpaid,
75
76FORMAT(
77SUM(IFNULL(
78(SELECT SUM(invoice_total) FROM associate_data ad6
79WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice'))
80,0.1)/clu.exchange_rate_USD)
81,0) AS TotalUSDWaitingInvoice,
82
83FORMAT(
84SUM(IFNULL(
85(SELECT SUM(invoice_total) FROM associate_data ad6
86WHERE ad6.currency = clu.currency AND (ad6.status = 'WaitingInvoice' OR ad6.status = 'Unpaid'))
87,0.1)/clu.exchange_rate_USD)
88,0) AS TotalUSDToPay
89FROM currency_lkp clu
90)`