Oracle Query -Lead/over

with VendorGrandtotal as 
(
SELECT nsn,thisdate,  
vendor1_code, vendor1_name, qtysum_1, DELIVERED_QTY_1, split_1,
vendor2_code, vendor2_name, qtysum_2, DELIVERED_QTY_2, split_2
FROM 
(SELECT nsn_code AS nsn, vendor_code AS vendor1_code, vendor_name AS vendor1_name, thisdate,
qtysum AS qtysum_1, DELIVERED_QTY AS DELIVERED_QTY_1,
split AS split_1, LEAD(vendor_code) OVER (ORDER BY split) AS vendor2_code,
               LEAD(vendor_name) OVER (ORDER BY split) AS vendor2_name,
               LEAD(qtysum) OVER (ORDER BY split)      AS qtysum_2,
              LEAD(DELIVERED_QTY)OVER (ORDER BY split) AS DELIVERED_QTY_2,
               LEAD(split) OVER (ORDER BY split)       AS split_2
               ,ROW_NUMBER() OVER (ORDER BY split)      rn
          FROM (  SELECT p.NSN_CODE,
                        dv.vendor_code,
                         dv.vendor_name,d.thisdate,
                         SUM(po.quantity) qtysum,
                         sum(po.QTYDELIVERED) as DELIVERED_QTY,
                         rfp.vendor_share_percent AS split
                    FROM fact_po_lines po
                         INNER JOIN dim_product p
                             ON po.dim_product_id = p.dim_product_id 
                         INNER JOIN dim_date d ON po.ORDERED_DATE_ID = d.dim_date_id
                         INNER JOIN fact_pa_item pai
                             ON     po.dim_product_id = pai.dim_product_id
                                AND po.dim_vendor_id = pai.dim_vendor_id
                         INNER JOIN dim_vendor dv ON po.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_date deff ON pai.dim_date_effective_id = deff.dim_date_id
                         INNER JOIN dim_date dexp ON pai.dim_date_expiration_id = dexp.dim_date_id
                         INNER JOIN dim_product p ON pai.dim_product_id = p.dim_product_id
                         INNER JOIN fact_rfp_product rfp
                             ON     pai.source_rfp_id = rfp.source_rfp_id
                                AND pai.dim_product_id = rfp.dim_product_id
                                AND pai.dim_date_effective_id = rfp.dim_date_create_id
                   WHERE    d.THISDATE BETWEEN ${prmStartDate} AND ${prmEndDate}  
                  -- d.THISDATE ='12-AUG-14'
                   AND d.thisdate BETWEEN deff.thisdate AND dexp.thisdate
                         and p.NSN_CODE = ${NSN_SUB2}
                GROUP BY p.nsn_code,
                         dv.vendor_code,
                         dv.vendor_name, 
                         d.thisdate,
                         rfp.vendor_share_percent) t)
                        
 WHERE rn = 1
--order by thisdate;
)
--select * from VendorGrandtotal 

select --nsn,thisdate
case when vendor1_code = vendor2_code
  then vendor1_name 
  else vendor1_name 
  end as vendor1_name
,case when vendor1_code = vendor2_code
  then qtysum_1 + qtysum_2
  else qtysum_1
  end as qtysum_1
,case when vendor1_code = vendor2_code
  then DELIVERED_QTY_1 + DELIVERED_QTY_2
  else DELIVERED_QTY_1
  end as DELIVERED_QTY_1
,case when split_1 = 100
 then (qtysum_1)/ (qtysum_1 )
 else (qtysum_1)/ (qtysum_1 + qtysum_2)
 end as percent_to_date1
-- get vendor 2 data  
,case when vendor1_code <> vendor2_code
  then vendor2_name 
  else null
  end as vendor2_name
,case when vendor1_code <> vendor2_code
  then  qtysum_2
  else null
  end as qtysum_2
,case when vendor1_code <> vendor2_code
  then  DELIVERED_QTY_2
  else null
  end as DELIVERED_QTY_2
,case when split_1 <> 100
 then (qtysum_2)/ (qtysum_1 + qtysum_2)
 else null
 end as percent_to_date2
from VendorGrandtotal 
order by thisdate

Select all Open in new window