CREATE VIEW qsocount as select QSL_RCVD as rcvdcount , NULL as missingcount ,bandvalue ,modevalue,band,mode from qsos,band,mode where QSL_RCVD='1' and (band.id=qsos.band and mode.id=qsos.mode) union all select NULL as rcvdcount,QSL_RCVD as missingcount ,bandvalue ,modevalue,band,mode from qsos,band,mode where QSL_RCVD !='1' and (band.id=qsos.band and mode.id=qsos.mode);
CREATE VIEW countrystat as select call,NULL as rcvd, prefix,mainprefix from qsos,prefixlist where call like prefixlist.prefix||'%' and QSL_RCVD !='1' union all select call,QSL_RCVD as rcvd, prefix,mainprefix from qsos,prefixlist where call like prefixlist.prefix||'%' and QSL_RCVD ='1';
CREATE VIEW myqsocount as select QSL_RCVD as rcvdcount , NULL as missingcount , NULL as lotwcount,bandvalue ,modevalue,band,mode from qsos,band,mode where QSL_RCVD='1' and (band.id=qsos.band and mode.id=qsos.mode) union all select NULL as rcvdcount,QSL_RCVD as missingcount ,NULL as lotwcount, bandvalue ,modevalue,band,mode from qsos,band,mode where QSL_RCVD !='1' and (band.id=qsos.band and mode.id=qsos.mode) union all select NULL as rcvdcount, NULL as missingcount , LOTW_QSL_RCVD  as lotwcount, bandvalue ,modevalue,band,mode from qsos,band,mode where (QSL_RCVD ='1' or LOTW_QSL_RCVD='1') and (band.id=qsos.band and mode.id=qsos.mode);
CREATE VIEW wasstat as select NULL as rcvdcount, LOTW_QSL_RCVD as missingcount,state, statevalue from qsos,state where qsos.state != '0' and qsos.state=state.id and (QSL_RCVD !='1' and LOTW_QSL_RCVD != '1') union all select LOTW_QSL_RCVD as rcvdcount, NULL as missingcount, state, statevalue from qsos,state where  qsos.state!='0' and qsos.state=state.id and (QSL_RCVD ='1' or LOTW_QSL_RCVD='1');
CREATE VIEW wassum as select count(distinct state) as worked, NULL as confirmed from wasstat union all select NULL as worked, count(distinct state) as confirmed from wasstat where rcvdcount!='';
CREATE VIEW wasbandstat as select NULL as rcvdcount, LOTW_QSL_RCVD as missingcount,state, statevalue, band, bandvalue from qsos,state,band where qsos.state != '0' and qsos.state=state.id and band.id=qsos.band and (QSL_RCVD !='1' and LOTW_QSL_RCVD != '1') union all select LOTW_QSL_RCVD as rcvdcount, NULL as missingcount, state, statevalue, band, bandvalue from qsos,state,band where  qsos.state!='0' and qsos.state=state.id and band.id=qsos.band and (QSL_RCVD ='1' or LOTW_QSL_RCVD='1');
CREATE VIEW was_needed as 
      select count(rcvdcount) as confirmed,
             count(state) as worked,
             statevalue
        from wasstat 
        group by state;
CREATE VIEW vuccstat as 
   select NULL as rcvdcount, NULL as lotwcount, APP_LinLog_GRID4 as grid,
          state, statevalue, band, bandvalue 
      from qsos,state,band 
      where APP_LinLog_GRID4 != '' and band >= '11' 
            and qsos.state=state.id and band.id=qsos.band
            and (QSL_RCVD !='1' and LOTW_QSL_RCVD != '1') 
   union all select QSL_RCVD as rcvdcount,NULL as lotwcount, APP_LinLog_GRID4 as grid,
          state, statevalue, band, bandvalue 
      from qsos,state,band 
      where APP_LinLog_GRID4 != '' and band >= '11' 
            and qsos.state=state.id and band.id=qsos.band
            and QSL_RCVD ='1' 
   union all select NULL as rcvdcount, LOTW_QSL_RCVD as lotwcount, APP_LinLog_GRID4 as grid,
          state, statevalue, band, bandvalue 
      from qsos,state,band 
      where APP_LinLog_GRID4 != '' and band >= '11' 
            and qsos.state=state.id and band.id=qsos.band
            and (QSL_RCVD !='1' and LOTW_QSL_RCVD = '1');
CREATE VIEW vuccstat6m as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='6m'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='6m' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='6m' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vucc6m_needed as 
      select count(rcvdcount) as confirmed,
             count(lotwcount) as lotw,
             count(grid) as worked,
             grid,
             band, bandvalue 
        from vuccstat 
        where bandvalue='6m' 
        group by grid;
CREATE VIEW vuccstat2m as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='2m'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='2m' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='2m' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vucc2m_needed as 
      select count(rcvdcount) as confirmed,
             count(lotwcount) as lotw,
             count(grid) as worked,
             grid,
             band, bandvalue 
        from vuccstat 
        where bandvalue='2m' 
        group by grid;
CREATE VIEW vuccstat1_25m as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='1.25m'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='1.25m' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='1.25m' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vuccstat70cm as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='70cm'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='70cm' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='70cm' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vuccstat33cm as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='33cm'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='33cm' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='33cm' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vuccstat23cm as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='23cm'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='23cm' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='23cm' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vuccstat13cm as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='13cm'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='13cm' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='13cm' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vuccstat9cm as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='9cm'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='9cm' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='9cm' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vuccstat6cm as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='6cm'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='6cm' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='6cm' and (lotwcount!='' or rcvdcount!='');
CREATE VIEW vuccstat3cm as 
   select count(distinct grid) as worked, NULL as confirmed, NULL as lotw 
      from vuccstat where bandvalue='3cm'
   union all select NULL as worked, count(distinct grid) as confirmed, NULL as lotw 
      from vuccstat where bandvalue='3cm' and rcvdcount!='' 
   union all select NULL as worked, NULL as confirmed, count(distinct grid) as lotw 
      from vuccstat where bandvalue='3cm' and (lotwcount!='' or rcvdcount!='');
