Re: Help ! Very challenging SQL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 7 Jun 2004 07:48:56 +0000 (UTC)
Message-ID: <ca16l8$pl$1@sparta.btinternet.com>

I've already demonstrated that ANSI SQL
is not my forte, so I won't try to give you a complete answer in ANSI - perhaps VC
will oblige.

My approach to the problem would be as
follows:

if there are no DAT rows in the range you want zero.

if there is an exact match on a DAT row you want one

if there isn't an exact match, you want the count of future dates in the range

Assumption - doing the join just once is probably the most efficient option.

STEP 1:
Generate all three columns as defined above - using VCs ANSI SQL, and
adding a couple of columns, I think this works:

select

    mst.*,
    count(dt) total_dates,
    sum(

        case
            when mst.dt1 = dat.dt
            then 1
            else 0
        end
    )                    match_date,
    sum(
        case
            when dat.dt > mst.dt1
            then 1
            else 0
        end
    )                    future_dates

from mst left join dat

on dt between dt1 and dt2 and ind=1
group by dt1,dt2;

You then need to put a wrapper around
the three counts (using a CASE perhaps,
and an in-line view if Teradata allows it) that says something like:

if total_dates = 0 then

return 0
elsif match_date = 1 then

return 1
else

return future_dates
end

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


<dba2adm_at_yahoo.com> wrote in message
news:903bd22e.0406040701.36ae540e_at_posting.google.com...


> Thanks Jonathan,

>

> Yesterday after few tries I came up with the following sql, I am not

> sure this is correct.

> select mst.*, sum(case when dt1 < dt2 and dt between mst.dt1 and

> mst.dt2 and ind = 1 then 1 else 0 end) as dt from mst, dat group by

> mst.dt1, mst.dt2.

>

> I also tried the left outer join, but my logic was wrong.

>

> Now, how do I extend your logic to include another "when" inside the

> case. something similar to the following.

>

> select mst.*,

>   case

>     WHEN mst.dt1 = dat.dt and dat.ind = 1 THEN 1 -> if dt1 is there

> and ind = 1 return 1

>     when dt1 < dt2

>     then (select count(*) from dat where ind = 1 and dt between

> mst.dt1 and

>          mst.dt2)

>   else 0

>   end as ct from mst

>

Received on Mon Jun 07 2004 - 02:48:56 CDT

Re: Help ! Very challenging SQL - Oracle/Teradata