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