Return one row

**Hugo Kornelis** · Jul 20 '05, 03:23 AM

Re: Return one row

On 7 Apr 2004 20:56:48 -0700, Bill wrote:
[color=blue]
>Hello
>
>I was wondering if someone could help me out with something.
>
>With the following rows:
>
>agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl
>1 1 1 10-10-2000 01 10-15-2000 02
>1 1 2 10-09-2000 06 05
>1 2 1 10-08-2000 05 10-20-2000 01
>1 2 1 10-05-2000 01 10-15-2000 03
>
>What I need is SQL to get one row per agnt/supv with the following:
> strt_dt, strt_lvl of earliest strt_dt
> trm_dt, trm_lvl of the latest trm_dt but only if all trm_dts for
>the supv are
> filled in, otherwise null for both
>
>agnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl
>1 1 10-09-2000 06 null null
>1 2 10-05-2000 01 10-20-2000 01
>
>If anyone has any ideas on the most efficient way to accomplish this,
>I'd appreciate a reply.
>
>Thanks![/color]

Maybe this:

select D.agnt_num, D.supv_num,
D.strt_dt, J1.strt_lvl,
D.trm_dt, J2.trm_lvl
from (select agnt_num, supv_num, min(strt_dt) as strt_dt,
nullif(max(coal esce(trm_dt,'99 991231')),'9999 1231') as trm_dt
from MyTable
group by agnt_num, supv_num) D
inner join MyTable as J1
on J1.agnt_num = D.agnt_num
and J1.supv_num = D.supv_num
and J1.strt_dt = D.strt_dt
left join MyTable as J2
on J2.agnt_num = D.agnt_num
and J2.supv_num = D.supv_num
and J2.trm_dt = D.trm_dt

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**Bill** · Jul 20 '05, 03:23 AM

Re: Return one row

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<om8a70lub rt4fpa79ht4cpq8 0t2rdejrak@4ax. com>...[color=blue]
> On 7 Apr 2004 20:56:48 -0700, Bill wrote:
>[color=green]
> >Hello
> >
> >I was wondering if someone could help me out with something.
> >
> >With the following rows:
> >
> >agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl
> >1 1 1 10-10-2000 01 10-15-2000 02
> >1 1 2 10-09-2000 06 05
> >1 2 1 10-08-2000 05 10-20-2000 01
> >1 2 1 10-05-2000 01 10-15-2000 03
> >
> >What I need is SQL to get one row per agnt/supv with the following:
> > strt_dt, strt_lvl of earliest strt_dt
> > trm_dt, trm_lvl of the latest trm_dt but only if all trm_dts for
> >the supv are
> > filled in, otherwise null for both
> >
> >agnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl
> >1 1 10-09-2000 06 null null
> >1 2 10-05-2000 01 10-20-2000 01
> >
> >If anyone has any ideas on the most efficient way to accomplish this,
> >I'd appreciate a reply.
> >
> >Thanks![/color]
>
> Maybe this:
>
> select D.agnt_num, D.supv_num,
> D.strt_dt, J1.strt_lvl,
> D.trm_dt, J2.trm_lvl
> from (select agnt_num, supv_num, min(strt_dt) as strt_dt,
> nullif(max(coal esce(trm_dt,'99 991231')),'9999 1231') as trm_dt
> from MyTable
> group by agnt_num, supv_num) D
> inner join MyTable as J1
> on J1.agnt_num = D.agnt_num
> and J1.supv_num = D.supv_num
> and J1.strt_dt = D.strt_dt
> left join MyTable as J2
> on J2.agnt_num = D.agnt_num
> and J2.supv_num = D.supv_num
> and J2.trm_dt = D.trm_dt
>
> (untested)
>
> Best, Hugo[/color]

Hello

Thank you very much for the reply. I guess one thing I forgot to
mention is that the start and trm dates could be the same for two or
more meet_nums. So somehow I think I'd need to get the meet_num for
the high and low of the dates to avoid getting more than one row.

Any ideas?

Thanks again
Bill

**Hugo Kornelis** · Jul 20 '05, 03:23 AM

Re: Return one row

On 8 Apr 2004 06:20:12 -0700, Bill wrote:

(snip)[color=blue]
>Thank you very much for the reply. I guess one thing I forgot to
>mention is that the start and trm dates could be the same for two or
>more meet_nums. So somehow I think I'd need to get the meet_num for
>the high and low of the dates to avoid getting more than one row.
>
>Any ideas?[/color]

It looks as if you're problem is not "how to code", but "what to
code".

Let's change your original sample data a bit:

agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl
1 1 1 10-10-2000 01 10-15-2000 02
1 1 2 10-10-2000 06 05
1 2 1 10-08-2000 05 10-15-2000 01
1 2 2 10-05-2000 01 10-15-2000 03

Now agnt/supv 1/1 have two meets with the same strt_dt and 1/2 have
two meetings with the same trm_dt. If you still want one row per
supv/agnt, then what sould go at the place of the question marks?

gnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl
1 1 10-10-2000 ?? null null
1 2 10-05-2000 01 10-15-2000 ??

In other words - which strt_lvl and trm_lvl to select when multiple
rows share the minimum strt_dt resp. rmp_dt?

You'll have to get that clear first, before you waste any time coding
something your customer probably doesn't want at all.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

**Bill** · Jul 20 '05, 03:24 AM

Re: Return one row

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<6nbb70pft uk6vh5ea0503ak2 ues0pvrnlu@4ax. com>...[color=blue]
> On 8 Apr 2004 06:20:12 -0700, Bill wrote:
>
> (snip)[color=green]
> >Thank you very much for the reply. I guess one thing I forgot to
> >mention is that the start and trm dates could be the same for two or
> >more meet_nums. So somehow I think I'd need to get the meet_num for
> >the high and low of the dates to avoid getting more than one row.
> >
> >Any ideas?[/color]
>
> It looks as if you're problem is not "how to code", but "what to
> code".
>
> Let's change your original sample data a bit:
>
> agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl
> 1 1 1 10-10-2000 01 10-15-2000 02
> 1 1 2 10-10-2000 06 05
> 1 2 1 10-08-2000 05 10-15-2000 01
> 1 2 2 10-05-2000 01 10-15-2000 03
>
>
> Now agnt/supv 1/1 have two meets with the same strt_dt and 1/2 have
> two meetings with the same trm_dt. If you still want one row per
> supv/agnt, then what sould go at the place of the question marks?
>
> gnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl
> 1 1 10-10-2000 ?? null null
> 1 2 10-05-2000 01 10-15-2000 ??
>
> In other words - which strt_lvl and trm_lvl to select when multiple
> rows share the minimum strt_dt resp. rmp_dt?
>
> You'll have to get that clear first, before you waste any time coding
> something your customer probably doesn't want at all.
>
> Best, Hugo[/color]

Hello

You are correct sir. I'm sorry for the crummy data and bad specs.
When the dates are the same I need the lowest meet_num. I was told by
the customer that the dates shouldn't be the same, but I've always
been a bit on the paranoid side. Thanks for patience!

Bill

**Hugo Kornelis** · Jul 20 '05, 03:24 AM

Re: Return one row

On 8 Apr 2004 18:20:35 -0700, Bill wrote:
[color=blue]
>Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<6nbb70pft uk6vh5ea0503ak2 ues0pvrnlu@4ax. com>...[color=green]
>> On 8 Apr 2004 06:20:12 -0700, Bill wrote:
>>
>> (snip)[color=darkred]
>> >Thank you very much for the reply. I guess one thing I forgot to
>> >mention is that the start and trm dates could be the same for two or
>> >more meet_nums. So somehow I think I'd need to get the meet_num for
>> >the high and low of the dates to avoid getting more than one row.
>> >
>> >Any ideas?[/color]
>>
>> It looks as if you're problem is not "how to code", but "what to
>> code".
>>
>> Let's change your original sample data a bit:
>>
>> agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl
>> 1 1 1 10-10-2000 01 10-15-2000 02
>> 1 1 2 10-10-2000 06 05
>> 1 2 1 10-08-2000 05 10-15-2000 01
>> 1 2 2 10-05-2000 01 10-15-2000 03
>>
>>
>> Now agnt/supv 1/1 have two meets with the same strt_dt and 1/2 have
>> two meetings with the same trm_dt. If you still want one row per
>> supv/agnt, then what sould go at the place of the question marks?
>>
>> gnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl
>> 1 1 10-10-2000 ?? null null
>> 1 2 10-05-2000 01 10-15-2000 ??
>>
>> In other words - which strt_lvl and trm_lvl to select when multiple
>> rows share the minimum strt_dt resp. rmp_dt?
>>
>> You'll have to get that clear first, before you waste any time coding
>> something your customer probably doesn't want at all.
>>
>> Best, Hugo[/color]
>
>Hello
>
>You are correct sir. I'm sorry for the crummy data and bad specs.
>When the dates are the same I need the lowest meet_num. I was told by
>the customer that the dates shouldn't be the same, but I've always
>been a bit on the paranoid side. Thanks for patience!
>
>Bill[/color]

I'd use a view in this case. It can proably all be done in one big and
complicated query with derived tables, but if you wwant to understand
your code later, that might not be a good idea.

CREATE VIEW ParanoidStrt AS
SELECT agnt_num, supv_num, strt_dt, strt_lvl
FROM MyTable AS O
WHERE NOT EXISTS
(SELECT *
FROM MyTable AS I
WHERE I.agnt_num = O.agnt_num
AND I.supv_num = O.supv_num
AND I.strt_dt = O.strt_dt
AND I.meet_num < O.meet_num)
go
CREATE VIEW ParanoidTrm AS
SELECT agnt_num, supv_num, trm_dt, trm_lvl
FROM MyTable AS O
WHERE NOT EXISTS
(SELECT *
FROM MyTable AS I
WHERE I.agnt_num = O.agnt_num
AND I.supv_num = O.supv_num
AND I.trm_dt = O.trm_dt
AND I.meet_num < O.meet_num)
go

-- And now comes the query:
select D.agnt_num, D.supv_num,
D.strt_dt, J1.strt_lvl,
D.trm_dt, J2.trm_lvl
from (select agnt_num, supv_num, min(strt_dt) as strt_dt,
nullif(max(coal esce(trm_dt,'99 991231')),'9999 1231') as trm_dt
from MyTable
group by agnt_num, supv_num) D
inner join ParanoidStrt as J1
on J1.agnt_num = D.agnt_num
and J1.supv_num = D.supv_num
and J1.strt_dt = D.strt_dt
left join ParanoidTrm as J2
on J2.agnt_num = D.agnt_num
and J2.supv_num = D.supv_num
and J2.trm_dt = D.trm_dt

Note 1: This is still untested. I can only test soluition I present in
this newsgroup if I have some DDL plus INSERT statements that I can
copy and paste in Query Analyser to recreate your table sturcture in
my test DB.

Note 2: If you don't want to use the views, simply replace the name of
each view in the query by the select statement of that view, enclosed
in (parantheses).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Return one row

Return one row

Comment

Comment

Comment

Comment

Comment