-------- Original-Nachricht --------
Betreff: Re: howto do iteration with UNION
Datum: Thu, 04 Dec 2008 09:54:38 +0100
Von: Ingmar Lötzsch <[EMAIL PROTECTED]>
Antwort an: [email protected]
An: [email protected]
Referenzen: <[EMAIL PROTECTED]>
problem: I wish to get the production of a client through different period
of times.
the simple most sql will be:
*********** SQL ******************
SELECT client_ID, SUM(production) , #date1# AS start_date, #date2# AS
end_date
FROM client_production
WHERE client_ID=33293
AND production_date between #date1# and #date2#
GROUP BY client_ID
You can't use columns in the SELECT clause which don't appear in the
GROUP BY clause. Maybe this helps:
CREATE TABLE production
(
id serial NOT NULL,
client_id int NOT NULL,
production_date date NOT NULL,
production decimal(9,2) NOT NULL,
CONSTRAINT pk_production PRIMARY KEY (id)
);
INSERT INTO production (id, client_id, production_date, production)
VALUES (1, 1, '2008-02-15', 100);
INSERT INTO production (id, client_id, production_date, production)
VALUES (2, 1, '2008-02-16', 200);
INSERT INTO production (id, client_id, production_date, production)
VALUES (3, 1, '2008-04-01', 300);
INSERT INTO production (id, client_id, production_date, production)
VALUES (4, 1, '2008-05-15', 400);
INSERT INTO production (id, client_id, production_date, production)
VALUES (5, 2, '2008-05-02', 500);
INSERT INTO production (id, client_id, production_date, production)
VALUES (6, 2, '2008-06-15', 600);
INSERT INTO production (id, client_id, production_date, production)
VALUES (7, 2, '2008-06-25', 700);
INSERT INTO production (id, client_id, production_date, production)
VALUES (8, 2, '2008-07-01', 800);
INSERT INTO production (id, client_id, production_date, production)
VALUES (9, 2, '2007-07-01', 900); -- note the year
SELECT pr.client_id, qu.quarter, sum(pr.production) AS quarterproduction
FROM production AS pr
INNER JOIN
(
SELECT ARRAY['2008-01-01', '2008-03-31'] :: date[] AS quarter
UNION
SELECT ARRAY['2008-04-01', '2008-06-30'] :: date[] AS quarter
UNION
SELECT ARRAY['2008-07-01', '2008-09-30'] :: date[] AS quarter
UNION
SELECT ARRAY['2008-10-01', '2008-12-30'] :: date[] AS quarter
) AS qu ON pr.production_date BETWEEN quarter[1] AND quarter[2]
GROUP BY pr.client_id, qu.quarter
ORDER BY qu.quarter, pr.client_id
This works on PostgreSQL. How do you have to provide a list of date
arrays and iterate over them, I don't know. I've never used the
<iterate> tag. Maybe you can use a custom DateArrayTypeHandler and/or an
Array implementation to convert Date[] to data[].
Maybe it's easier to calculate the sum outside the database.
But! the date changes from period to period (eg., quarter basis through the
past 3 years).
For this, I assume I should use a UNION and change the date1/2 (if you have
better SQL solution please let me know)
* can anyone advise how to do that with <iterator>?
Thank you!
addendum:
You can avoid the date[] colums in the result:
SELECT pr.client_id, qu.quarter[1] AS date1, qu.quarter[2] AS date2,
sum(pr.production) AS quarterproduction
FROM production AS pr
INNER JOIN
(
SELECT ARRAY['2008-01-01', '2008-03-31'] :: date[] AS quarter
UNION
SELECT ARRAY['2008-04-01', '2008-06-30'] :: date[] AS quarter
UNION
SELECT ARRAY['2008-07-01', '2008-09-30'] :: date[] AS quarter
UNION
SELECT ARRAY['2008-10-01', '2008-12-30'] :: date[] AS quarter
) AS qu ON pr.production_date BETWEEN quarter[1] AND quarter[2]
GROUP BY pr.client_id, date1, date2
ORDER BY date1, pr.client_id
1;"2008-01-01";"2008-03-31";300.00
1;"2008-04-01";"2008-06-30";700.00
2;"2008-04-01";"2008-06-30";1800.00
2;"2008-07-01";"2008-09-30";800.00
