Query the Minimum Value per day within a month's worth of data

I have two sets of pricing data (A and B). Set A consists of all of my pricing data per order over a month. Set B consists of all of my competitor's pricing data over the same month. I want to compare my competitor's lowest price to each of my prices per day.

Graphically, the data appears like this:

Date:-- Set A: -- Set B:

1---------25---------31

1---------54---------47

1---------23---------56

1---------12---------23

1---------76---------40

1---------42

I want pass only the lowest price to a case statement which evaluates which prices are better. I would like to process an entire month's worth of data all at one time, so in my example, Dates 1 thru 30(1) would be included and crunched all at once, and for each day, there would only be one value from set B included: the lowest price in the set.

Important notes: Set B does not have a datapoint for each point in S开发者_开发百科et A

Hopefully this makes sense. Thanks in advance for any help you may be able to render.

That's a strange example you have - do you really have prices ranging from 12 to 76 within a single day?

Anyway, left joining your (grouped) data with their (grouped) data should work (untested):

with 
  my_prices as (
    select price_date, min(price_value) min_price from my_prices group by price_date),
  their_prices as (
    select price_date, min(price_value) min_price from their_prices group by price_date)
select 
  mine.price_date,
  (case 
     when theirs.min_price is null then mine.min_price
     when theirs.min_price >= mine.min_price then mine.min_price
     else theirs.min_price
   end) min_price
from
  my_min_prices mine
left join their_prices theirs on mine.price_date = theirs.price_date

I'm still not sure that I understand your requirements. My best guess is that you want something like

SQL> ed
Wrote file afiedt.buf

  1  with your_data as (
  2     select 1 date_id, 25 price_a,31 price_b from dual
  3     union all
  4     select 1, 54, 47 from dual union all
  5     select 1, 23, 56 from dual union all
  6     select 1, 12, 23 from dual union all
  7     select 1, 76, 40 from dual union all
  8     select 1, 42, null from dual)
  9  select date_id,
 10         sum( case when price_a < min_price_b
 11                   then 1
 12                   else 0
 13                end) better,
 14         sum( case when price_a = min_price_b
 15                   then 1
 16                   else 0
 17                end) tie,
 18         sum( case when price_a > min_price_b
 19                   then 1
 20                   else 0
 21                end) worse
 22    from( select date_id,
 23                 price_a,
 24                 min(price_b) over (partition by date_id) min_price_b
 25            from your_data )
 26*  group by date_id
SQL> /

   DATE_ID     BETTER        TIE      WORSE
---------- ---------- ---------- ----------
         1          1          1          4