On 8 Apr 2004 18:20:35 -0700, Bill wrote:
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<6n********************************@4ax.com>. .. On 8 Apr 2004 06:20:12 -0700, Bill wrote:
(snip) >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?
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
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
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(coalesce(trm_dt,'99991231')),'99991231' ) 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)
