can you do that in one query?

[Peter Primrose]

Say I gave 2 tables A and B

A�s fields: Date_A and Amount_A
B�s files: Date_B and Amount_B

The end-user asks this question:
select amount (A+B) between 2006-01-01 to 2006-12-31

the return result should be as follow:
Total_A is 1500 (until 2006-05-01)
And Total_B is 1000 (from 2006-06-01)

(table A has info until 2006-05-01 � this is the max date) once you get to the max � continue from table B and get the result from 2006-06-01 to 2006-12-31)

* Table A has priority on table B.
* When the range exceeds Table A continue with table B

Is there anyway to do that in one query?

Thanks for any idea or any combination.

[stu derby]

Originally posted by Peter Primrose:
Say I gave 2 tables A and B

A�s fields: Date_A and Amount_A
B�s files: Date_B and Amount_B

The end-user asks this question:
select amount (A+B) between 2006-01-01 to 2006-12-31

the return result should be as follow:
Total_A is 1500 (until 2006-05-01)
And Total_B is 1000 (from 2006-06-01)

(table A has info until 2006-05-01 � this is the max date) once you get to the max � continue from table B and get the result from 2006-06-01 to 2006-12-31)

* Table A has priority on table B.
* When the range exceeds Table A continue with table B

Is there anyway to do that in one query?

Thanks for any idea or any combination.

I don't really understand what you mean, but I'll take a few guesses anyway:

SELECT the_date, the_amount
FROM
(SELECT date_a AS the_date, amount_a AS the_AMOUNT
 FROM table_a
 WHERE date_a BETWEEN ? AND ?
UNION ALL
 SELECT date_b, amount_b
 FROM table_b
 WHERE date_b BETWEEN ? AND ?)
ORDER BY the_date

SELECT sum(the_amount)
FROM
(SELECT sum(amount_a) AS the_AMOUNT
 FROM table_a
 WHERE date_a BETWEEN ? AND ?
UNION ALL
 SELECT sum(amount_b)
 FROM table_b
 WHERE date_b BETWEEN ? AND ?)
 

[Peter Primrose]

Stu, thank you for your prompt reply.
I will try to put some light into my question.
here's an example that might help. Thanks again.

  
 
date_a    amount_a
2006-01-01    1
2006-02-012
2006-03-013
2006-04-014
 
table_b     amount_b
2006-01-01    9
2006-02-0110
2006-03-0111
2006-04-0112
2006-05-0113
2006-06-0114
2006-07-0115
2006-08-0116
2006-09-0117
2006-10-0118
2006-11-0119
2006-12-0120
 

if the user requests for the amount between 2006-01-01 to 2006-01-08
the return result shoule be:

1+2+3+4+13+15+16+17+18+19+20

because table_a has all value up to 2006-04-01 and the rest comes from table_b from 2006-05-01

I'm looking for somthing like this:
2006-01-01 to 2006-04-01: 10
2006-01-01 to 2006-05-01: 118

[Peter Primrose]

type:

if the user requests for the amount between 2006-01-01 to 2006-12-01
the return result shoule be:

1+2+3+4+13+15+16+17+18+19+20

because table_a has all value up to 2006-04-01 and the rest comes from table_b from 2006-05-01

I'm looking for somthing like this:
2006-01-01 to 2006-04-01: 10
2006-05-01 to 2006-12-01: 118

[Jeanne Boyarsky]

Peter,
Take a look at minus/except. You can use this operator in a way similar to union. It will give you all the rows in table b that are not in table a.

Then you can union that result with table a to get what you are looking for and do a query off of the union'd table.

The only catch is that the tables must have the same column names. You can use a view to do this to simplify things. You could also do it by defining the anonymous tables in your query, but this query is going to be quite involved as is. The views will make things more readable.

[stu derby]

SELECT *
FROM
(SELECT min(date_a) AS START_DATE, max(date_a) as END_DATE, sum(amount_a) AS table_total
 FROM table_a
 WHERE date_a BETWEEN ? AND ?
UNION ALL
 SELECT min(date_b), max(date_b), sum(amount_b)
 FROM table_b
 WHERE date_b BETWEEN ? AND ?) 
ORDER BY start_date

[Peter Primrose]

stu - you are the best!
thanks

by the way, do you know how to add a month in a query?

as in:

select max(date_a)+ONE_MONTH from....

[Peter Primrose]

got it: INTERVAL(10) day