Re: Aggregate with comparison

[Patricio Llaguno]

let me try to simplify the problem as you suggested

Table A:

| Id  |   1     |     2    |     3     |
|-----|---------|----------|-----------|
|   1 | 00:05:00| 00:10:00 | (null)    |
|   2 | 00:10:00|  (null)  | (null)    |

Table B

| Id  |   col   |Expected  |
|-----|---------|----------|
|   1 |     1   | 00:06:00 |
|   2 |     2   | 00:12:00 |
|   3 |     3   | 00:22:00 |

I am trying to make a sum depending on the actual value on the rows of table A in comparison to the expected on table B

Select 
    Id, (Select ??????? From (Select TiempoStd from B)as Stime) as Time
From
   A

Basically i want to make a comparison between the 2 tables to see which one is greater and add that to the next one.

I cant manage to understand how to call a specific value under my temp table Stime.

i am not that familiar with SQL so thats why i cant get this, the logic is something like this. in where the question marks are on the Query

ADDTIME(IF(A.1>(Stime.Expected where col = 1),A.1,(Stime.Expected where col = 1)),
ADDTIME(IF(A.2>(Stime.Expected where col = 2),A.2,(Stime.Expected where col = 2)),
IF(A.3>(Stime.Expected where col = 3),A.3,(Stime.Expected where col = 3))

Stime.Expected where col = 3 is a bad syntax right? but i hope you get the point of the logic im trying to make here.


so that the output is as follows:

Table A:

| Id  |   Time  |
|-----|---------|
|   1 | 00:40:00|
|   2 | 00:44:00|