col dos_monyy format a6 heading "Mon-yy"
 
SET pages 100
SET lines 400
SET pause off
rem SET verify off
rem SET feedback off
rem SET echo off
 
spool lag_rep.lst
 
col m0 format 9999999.99 heading "0"
col m1 format 9999999.99 heading "1"
col m2 format 9999999.99 heading "2"
col m3 format 9999999.99 heading "3"
col m4 format 9999999.99 heading "4"
col m5 format 9999999.99 heading "5"
col m6 format 9999999.99 heading "6"
col m7 format 9999999.99 heading "7"
col m8 format 9999999.99 heading "8"
col m9 format 9999999.99 heading "9"
col m10 format 9999999.99 heading "10"
col m11 format 9999999.99 heading "11"
col m12 format 9999999.99 heading "12"
col m13 format 9999999.99 heading "13"
col m14 format 9999999.99 heading "14"
col m15 format 9999999.99 heading "15"
col m16 format 9999999.99 heading "16"
col m17 format 9999999.99 heading "17"
col m18 format 9999999.99 heading "18"
col m19 format 9999999.99 heading "19"
col m20 format 9999999.99 heading "20"
col m21 format 9999999.99 heading "21"
col m22 format 9999999.99 heading "22"
col m23 format 9999999.99 heading "23"
col m24 format 9999999.99 heading "24"
col m25 format 9999999.99 heading "25"
col m26 format 9999999.99 heading "26"
col m27 format 9999999.99 heading "27"
col m28 format 9999999.99 heading "28"
col m29 format 9999999.99 heading "29"
col m30 format 9999999.99 heading "30"
 
col DOS_monyy format a6 heading "Mon-YY"
 
accept claim_type CHAR prompt 'Enter Claim Type ( P/I/O/ Blanks For all): '
accept plan CHAR       prompt 'Enter Plan ID: '
accept product CHAR    prompt 'Enter Product ID ( Return for all): '
accept Clinic CHAR     prompt 'Enter Clinic ID ( Return for all): '
accept pcp_name CHAR   prompt 'Enter Pcp ID ( Return for all): '
accept from_dos CHAR   prompt 'Enter From Date Of Service ( MMDDYY): '
accept to_dos   CHAR   prompt 'Enter To   Date Of Service ( MMDDYY): '
 
SELECT TO_CHAR(sixth_date,'Mon-YY') DOS_monyy,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),0),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m0,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-1),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m1,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-2),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m2,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-3),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m3,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-4),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m4,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-5),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m5,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-6),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m6,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-7),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m7,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-8),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m8,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-9),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m9,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-10),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m10,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-11),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m11,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-12),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m12,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-13),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m13,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-14),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m14,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-15),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m15,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-16),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m16,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-17),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m17,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-18),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m18,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-19),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m19,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-20),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m20,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-21),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m21,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-22),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m22,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-23),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m23,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-24),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m24,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-25),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m25,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-26),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m26,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-27),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m27,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-28),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m28,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-29),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m29,
  SUM(DECODE(TO_CHAR(sixth_date,'YYMM'), 
          TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(fourth_date,'YYMM'),'YYMM'),-30),'YYMM'),
             NVL(fourth_amount_nmbr,0), 0)) m30
FROM claims
WHERE plan_identifier = '&plan' AND 
      cpt_code LIKE '%' AND 
      group_identifier LIKE NVL('&product','%') AND 
      clms_usrx2 LIKE NVL('&clinic','%') AND 
      primary_provider_id LIKE NVL('&pcp_name','%') AND        
      sixth_date BETWEEN TO_DATE('&from_dos','mmddyy') AND TO_DATE('&to_dos','mmddyy') AND
      fourth_date > TO_DATE('&from_dos','mmddyy') AND
      type_code LIKE NVL('&claim_type','%') 
GROUP BY TO_CHAR(sixth_date,'Mon-YY');
spool off;
prompt "Report Spooled to lag_rep.lst ................."