ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ingmar Lötzsch <iloetz...@asci-systemhaus.de>
Subject Re: howto do iteration with UNION
Date Thu, 04 Dec 2008 09:15:38 GMT


-------- Original-Nachricht --------
Betreff: Re: howto do iteration with UNION
Datum: Thu, 04 Dec 2008 09:54:38 +0100
Von: Ingmar Lötzsch <iloetzsch@asci-systemhaus.de>
Antwort an: user-java@ibatis.apache.org
An: user-java@ibatis.apache.org
Referenzen: <20820524.post@talk.nabble.com>

> 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

Mime
View raw message