Language: Highlight Mode: PLSQL Last Modified: March 02nd, 2009
col dos_monyy format a6 heading "Mon-yy"SET pages 100SET lines 400SET 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): 'SELECTTO_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 LIKENVL('&product','%')AND
clms_usrx2 LIKENVL('&clinic','%')AND
primary_provider_id LIKENVL('&pcp_name','%')AND
sixth_date BETWEENTO_DATE('&from_dos','mmddyy')ANDTO_DATE('&to_dos','mmddyy')AND
fourth_date >TO_DATE('&from_dos','mmddyy')AND
type_code LIKENVL('&claim_type','%')GROUPBYTO_CHAR(sixth_date,'Mon-YY');
spool off;
prompt "Report Spooled to lag_rep.lst ................."
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.