Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

can you do that in one query?

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.


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
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
got it: INTERVAL(10) day
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic