业务经理业绩统计表

SELECT   a.fyear ,  
         c.fname as fdpname  ,
         b.fname as fempname ,  
         a.FAMOUNT  ,
         a.FORAMOUNT  , 
         a.fORallamount,
         a.fstockamount,
         a.fstocktaxamount,
         a.FINALLAMOUNT
FROM  (
        (SELECT   a.fyear ,  a.fdeptid ,  a.fid  ,  a.FAMOUNT ,
         b.foramount/10000 AS FORAMOUNT ,b.fORallamount/10000 AS FORallAMOUNT ,
         d.fstockamount ,d.fstocktaxamount,
         CASE WHEN c.finallamount/10000>1 THEN  c.finallamount/10000 ELSE '0' END  AS FINALLAMOUNT
        from (select 
              year(a.F_PXXU_CREATEDATE_DVN) as Fyear , 
              b.F_PXXU_BASE_83G AS FDEPTID ,
              b.F_PXXU_BASE_IMU AS FID  ,
              F_PXXU_INTEGER_3IY   AS  FAMOUNT  
              from  PXXU_t_Cust100015 a
              left join  PXXU_t_Cust_Entry100022 b on b.fid=a.fid) a
              left join  (
                          select  a.FSALEDEPTID ,a.FSALERID  , 
                          sum(b.FBILLAMOUNT_LC)*1 as  fORamount  ,  sum(b.FBILLallAMOUNT_LC)*1 as  fORallamount
                          from T_SAL_ORDER  a
                          left join  T_SAL_ORDERFIN b  on  b.fid=a.FID
                          where  a.FID  in ( select FID   from  T_SAL_ORDER   where  fbillno  like '%co%' )  and  a.FDOCUMENTSTATUS='C' 
                          group  by   a.FSALEDEPTID ,a.FSALERID )  b  on   b.FSALERID=a.FID
              INNER join (select  a.FSALESMANID , 
                        isnull( sum(c.ftaxprice*b.frealqty)/10000,0) as  fstocktaxamount  , 
                        isnull( sum(c.fprice*b.frealqty)/10000 ,0)  as  fstockamount
                        from  T_SAL_OUTSTOCK  a
                        inner join t_sal_outstockentry   b   on a.fid=b.fid  and a.FSALESMANID>0
                        inner join T_SAL_OUTSTOCKENTRY_F c   on b.fentryid=c.fentryid
                          group by   a.FSALESMANID
                        ) d  on  d.FSALESMANID=a.fid
              INNER join  (
                          select     a.FSALEERID ,a.FSALEDEPTID, 
                          SUM(B.FALLAMOUNTFOR+1-1)*1 AS FINALLAMOUNT
                          from  T_IV_SALESIC a
                          left join T_IV_SALESICENTRY  b  on  b.fid=a.fid
                          where a.FDOCUMENTSTATUS='C'  and b.FSALESORDERNO like '%CO%'
                          GROUP BY  a.FSALEERID ,a.FSALEDEPTID)  c  on   c.FSALEERID=a.fid
          )
        ) a
left join  V_BD_SALESMAN_L b  on b.fid=a.fid 
left join  T_BD_DEPARTMENT_L  c on  c.FDEPTID=a.FDEPTID