On 2 dez, 14:16, Serge Rielau <srie...@ca.ibm.com> wrote:
> Michel Esber wrote:
> > I understand what´s happening here, and the result set is not
> > incorrect (according to the query).
>
> I do not understand what's happening here.
>
> > Hope that it makes sense now.
>
> No it doesn't, you are assuming we have the same local knowledge as you.
> 1. We are missing the sample data for the asset table
> 2. You keep flip flopping between 30 days and 90 days which confuses the
> matter
> 3. Shouldn't matter, but: Why are you adding the literal into the NOT
> EXISTS with the HAVING?
>
> I propose we restart with a clean slate:
> Define both tables, define contents for both. Define the output.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab



My apologies. Let me try to make this simple:

TBL_ASSET_SW (MACHINE_ID, SW_ID) values are:
A,1
B,2
C,3

T_METERING (MACHINE_ID, SW_ID, EXECUTION_DATE):

A,1,11/30/2007
A,1,10/15/2007
B,2,10/18/2007
B,2,10/17/2007


In this example, I need to retrieve the maximum execution for all
(MACHINE_ID, SW_ID) that have NOT EXECUTED between 11/25/2007 and
12/02/2007.

The expected output is:

B,2,10/18/2007
C,3,NULL

Even though (C,3) did not have any execution, I need it in the result
set. I was hoping to find a solution that did not scan T_METERING more
than once. I do not know if that is possible.

Thanks, Michel.

Michel Esber wrote:
> On 2 dez, 14:16, Serge Rielau <srie...@ca.ibm.com> wrote:
>> Michel Esber wrote:
>>> I understand what´s happening here, and the result set is not
>>> incorrect (according to the query).
>> I do not understand what's happening here.
>>
>>> Hope that it makes sense now.
>> No it doesn't, you are assuming we have the same local knowledge as you.
>> 1. We are missing the sample data for the asset table
>> 2. You keep flip flopping between 30 days and 90 days which confuses the
>> matter
>> 3. Shouldn't matter, but: Why are you adding the literal into the NOT
>> EXISTS with the HAVING?
>>
>> I propose we restart with a clean slate:
>> Define both tables, define contents for both. Define the output.
>>
>> Cheers
>> Serge
>> --
>> Serge Rielau
>> DB2 Solutions Development
>> IBM Toronto Lab
>
>
>
> My apologies. Let me try to make this simple:
>
> TBL_ASSET_SW (MACHINE_ID, SW_ID) values are:
> A,1
> B,2
> C,3
>
> T_METERING (MACHINE_ID, SW_ID, EXECUTION_DATE):
>
> A,1,11/30/2007
> A,1,10/15/2007
> B,2,10/18/2007
> B,2,10/17/2007
>
>
> In this example, I need to retrieve the maximum execution for all
> (MACHINE_ID, SW_ID) that have NOT EXECUTED between 11/25/2007 and
> 12/02/2007.
>
> The expected output is:
>
> B,2,10/18/2007
> C,3,NULL
>
> Even though (C,3) did not have any execution, I need it in the result
> set. I was hoping to find a solution that did not scan T_METERING more
> than once. I do not know if that is possible.

Yes, this is possible. Since your original posts were looking for
data older than a certain age (and the example above asks for data
older than 7 days (since today is 2007-12-02):

select
a.machine_id,
a.sw_id,
max(b.execution_date)
from
tbl_asset_sw a
left outer join t_metering b
on (a.machine_id = b.machine_id and a.sw_id = b.sw_id)
group by
a.machine_id,
a.sw_id
having
max(b.execution_date) is null
or
max(b.execution_date) < current date - 7 days


Good luck,

On 3 dez, 02:05, Ian <ianb...@mobileaudio.com> wrote:
> Michel Esber wrote:
> > On 2 dez, 14:16, Serge Rielau <srie...@ca.ibm.com> wrote:
> >> Michel Esber wrote:
> >>> I understand what´s happening here, and the result set is not
> >>> incorrect (according to the query).
> >> I do not understand what's happening here.
>
> >>> Hope that it makes sense now.
> >> No it doesn't, you are assuming we have the same local knowledge as you..
> >> 1. We are missing the sample data for the asset table
> >> 2. You keep flip flopping between 30 days and 90 days which confuses the
> >> matter
> >> 3. Shouldn't matter, but: Why are you adding the literal into the NOT
> >> EXISTS with the HAVING?
>
> >> I propose we restart with a clean slate:
> >> Define both tables, define contents for both. Define the output.
>
> >> Cheers
> >> Serge
> >> --
> >> Serge Rielau
> >> DB2 Solutions Development
> >> IBM Toronto Lab
>
> > My apologies. Let me try to make this simple:
>
> > TBL_ASSET_SW (MACHINE_ID, SW_ID) values are:
> > A,1
> > B,2
> > C,3
>
> > T_METERING (MACHINE_ID, SW_ID, EXECUTION_DATE):
>
> > A,1,11/30/2007
> > A,1,10/15/2007
> > B,2,10/18/2007
> > B,2,10/17/2007
>
> > In this example, I need to retrieve the maximum execution for all
> > (MACHINE_ID, SW_ID) that have NOT EXECUTED between 11/25/2007 and
> > 12/02/2007.
>
> > The expected output is:
>
> > B,2,10/18/2007
> > C,3,NULL
>
> > Even though (C,3) did not have any execution, I need it in the result
> > set. I was hoping to find a solution that did not scan T_METERING more
> > than once. I do not know if that is possible.
>
> Yes, this is possible. Since your original posts were looking for
> data older than a certain age (and the example above asks for data
> older than 7 days (since today is 2007-12-02):
>
> select
> a.machine_id,
> a.sw_id,
> max(b.execution_date)
> from
> tbl_asset_sw a
> left outer join t_metering b
> on (a.machine_id = b.machine_id and a.sw_id = b.sw_id)
> group by
> a.machine_id,
> a.sw_id
> having
> max(b.execution_date) is null
> or
> max(b.execution_date) < current date - 7 days
>
> Good luck,

Thanks Ian and all that have helped. I now realize my first query was
awful. Your solution was simple and efficient.