Subqueries

DaveStt · June 28, 2012, 12:13pm

I have two subqueries:
One returns ID, Days, Value where value is a running total:

1 | 1 | 1
1 | 2 | 4
2 | 5 | 6
2 | 8 | 16

The second returns ID (same as above), Product, Value where value is a runningtotal

1 | Widget1 | 1
1 | Widget1 | 3
2 | Widget2 | 8
2 | Widget2 | 16

What I need to know is at what day is the value in query 1 greater than query2.

1 | Widget1 | 1 | 2
1 | Widget1 | 3 | 2
2 | Widget2 | 8 | 8
2 | Widget2 | 16 | 8

Message was edited by: Dave - Stt

jacquesrk · July 2, 2012, 1:42pm

I am going to restate what I think you are trying to do, and add prefixes to some of the column data to make it less confusing for me.

“One returns ID, Days, Value where value is a running total:”
dataset1

ID | Day | Value
ID1 | day1 | 1
ID1 | day2 | 4
ID2 | day5 | 6
ID2 | day8 | 16

“The second returns ID (same as above), Product, Value where value is a runningtotal”
dataset2

ID | Product | Value
ID1 | Widget1 | 1
ID1 | Widget1 | 3
ID2 | Widget2 | 8
ID2 | Widget2 | 16

“What I need to know is at what day is the value in query 1 greater than query2.”

desired result set

It looks to me like the query would be

select d2.id, d2.product, d2.value, d1.day
from dataset2 d2, dataset1 d1
where d2.id = d1.id and d2.value <= d1.value ;

Replace (dataset2) and (dataset1) with your subqueries

I have “d2.value <= d1.value”, but in your desired result set, one time you include the day when the results are equal, and one time you don’t, so I’m not sure if you want “less than or equal to” or “less than”

Message was edited by: jacquesrk

DaveStt · July 3, 2012, 7:56am

Unfortunately, I don’t think that will work. I should have provided a better example, however, there will almost always be instances where there are multiple values in dataset1 that are greater than the value in dataset2. In these cases I would only want to return the first instance where dataset1.value >= dataset2.value.

For example:

Where dataset2.value = 3 return only dataset1.day = day2 since this is the first instance where dataset1.value >= dataset2.value.

dataset1
ID | Day | Value
ID1 | day1 | 1
ID1 | day2 | 4
ID1 | day5 | 6
ID1 | day8 | 16