超30万订单统计

select *
from (   select 
             c.fname as fbmname ,
            d.fname as fempname ,
             a.FBILLNO AS FORBILLNO,
             e.fname as fcustname ,
           e.fnumber as fcustnumber ,
            sum(b.FBILLALLAMOUNT_LC)*1/10000 AS Foramount ,
             case when LEFT(RIGHT(E.FNUMBER,8),4)>='2024' then '新客户' else '老客户' end as MK
           from T_SAL_ORDER a
           left join T_SAL_ORDERFIN b on b.fid=a.fid
           left join T_BD_DEPARTMENT_L c on c.fdeptid=a.fsaledeptid
           left join V_BD_SALESMAN_L d on d.fid=a.fsalerid
           left join (    select b.fname ,
                                        a.fnumber ,
                                        a.FCUSTID ,
                                        a.FCreateDate
                            from T_BD_CUSTOMER a
                          left join T_BD_CUSTOMER_L B ON B.Fcustid=A.Fcustid ) e on e.fcustid=a.fcustid
          where a.FBILLNO like '%CO%' and e.FNUMBER like '%A%' group by c.fname , d.fname , a.FBILLNO , e.fname , e.FNUMBER ) AS G
WHERE Foramount > '30'
 
 
----主要是CLOUD中不允许使用DROP函数:
----所以对原来的报表的进行化化的结果
 
SELECT c.fname AS fbmname, d.fname AS fempname, a.FBILLNO AS FORBILLNO, e.fname AS fcustname, e.fnumber AS fcustnumber, SUM(b.FBILLALLAMOUNT_LC) / 10000 AS Foramount, CASE WHEN SUBSTRING(e.fnumber, 9, 4) >= '2024' THEN '新客户' ELSE '老客户' END AS MK FROM T_SAL_ORDER a LEFT JOIN T_SAL_ORDERFIN b ON b.fid = a.fid LEFT JOIN T_BD_DEPARTMENT_L c ON c.fdeptid = a.fsaledeptid LEFT JOIN V_BD_SALESMAN_L d ON d.fid = a.fsalerid LEFT JOIN (SELECT b.fname, a.fnumber, a.FCUSTID FROM T_BD_CUSTOMER a LEFT JOIN T_BD_CUSTOMER_L b ON b.Fcustid = A.Fcustid) e ON e.fcustid = a.fcustid WHERE a.FBILLNO LIKE '%CO%' AND e.FNUMBER LIKE '%A%' GROUP BY c.fname, d.fname, a.FBILLNO, e.fname, e.fnumber HAVING SUM(b.FBILLALLAMOUNT_LC) / 10000 > 30
 
 
 
 
posted @ 2024-08-20 16:49  凡人1980  阅读(12)  评论(0)    收藏  举报