RE: SQL Question

["Chris Payne" <CPayne@xxxxxxxxxxxxxxx>]

Ok,
        When we group by AdjOn,AdjCan,AdjCaC we are making our selection
less unique, we want the min of (AdjDat * 1000000 + AdjTim) for each
group of AdjAct,AdjPO# what if we use two CTE's one to narrow down the
corrections table, and then one to pull the fields that we want:


With Narowing_down as
(select 
  Cust#
  PO#
  min(Correction_Date_YYMMDD * 1000000 + Correction_Time HHMMSS) as
dateandtime
from
  order_correction
group by
  Cust#,
  PO#
),

With Earliest_Correction as
(select 
  Cust#
  PO#
  Correction_Date_YYMMDD
  Correction_Time HHMMSS
  Old/New Code (O = Original)
  Original Cancel Date YYMMDD
from
  order_correction a join
  narrowing_down b on
   a.cust# = b.cust# and
   a.PO# = b.PO# and
   integer(datenandtime/1000000) = Correction_Date_YYMMDD and
   mod(dateandtime,1000000) =  Correction_Time HHMMSS
)


Select
a.cust,
a.PO,
coalesce(c.correction_date, a.current_cancel_date),
b.div,
b.brand,
b.subbrand
From
Order_Header a join
Order_detail b on
  a.cust = b.cust and
  a.PO = b.PO left outer join
Earliest_Correction c on
  a.cust = c.cust and
  a.po = c.po
Where
b.div = 'SOME DIv'and
b.sub-brand = 'Stuff' and
a.cust in ('cust1','cust2') and
coalesce(c.correction_date, a.current_cancel_date) >= start_date and
coalesce(c.correction_date, a.current_cancel_date) <= end_date


Chris