97 lines
7.6 KiB
MySQL
97 lines
7.6 KiB
MySQL
|
Drop Table #TABLETMEP1
|
|||
|
|
Drop Table #TABLETMEP2
|
|||
|
|
go--------------------
|
|||
|
|
Select
|
|||
|
|
CASE WHEN ISNULL(X2.FNAME,'') = '' THEN S.<EFBFBD><EFBFBD><EFBFBD><EFBFBD> ELSE S2.<EFBFBD><EFBFBD><EFBFBD><EFBFBD> END as 'SBU',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>SBU
|
|||
|
|
CASE WHEN ISNULL(X2.FNAME,'') = '' THEN S.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> ELSE S2.<EFBFBD><EFBFBD>ϸ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> END as 'DetailArea',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ϸ
|
|||
|
|
G.GFNAME as 'GName', --ְλ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN ISNULL(X2.FNAME,'') = '' THEN X.FNAME ELSE X2.FNAME END as 'Salesperson', --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա
|
|||
|
|
ISNULL(G2.FSTARTDATE,'') as 'RDate',--<EFBFBD><EFBFBD>˾ʱ<EFBFBD><EFBFBD>
|
|||
|
|
ISNULL(G.FSTARTDATE,'') as 'ZWDate', --<EFBFBD><EFBFBD><EFBFBD>α<EFBFBD>ְλ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN ISNULL(G3.FSTARTDATE,'')='' THen null ELSE ISNULL(G3.FSTARTDATE,'') END as 'XSDate',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
ROUND((DATEDIFF(DAY, ISNULL(G2.FSTARTDATE,''), GETDATE()) / 365.0), 1) as 'RZYear',--<EFBFBD><EFBFBD>ְ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
--ISNULL(Y2.FSHARERATE,100) as 'FSHARERATE', --<EFBFBD><EFBFBD>̯<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0) as 'CliqueBG',--<EFBFBD><EFBFBD>ŵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>-<EFBFBD>ѽ<EFBFBD><EFBFBD>м<EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN A.FSaleOrgId = 100302 THEN ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0) ELSE null END as 'PNBG',--<EFBFBD><EFBFBD>ŵҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>)
|
|||
|
|
CASE WHEN A.FSaleOrgId = 100303 THEN (ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0)) ELSE null END as 'XNBG',--<EFBFBD><EFBFBD>ŵҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>) *(ISNULL(Y2.FSHARERATE,100)/100)
|
|||
|
|
CASE WHEN A.FSaleOrgId = 100305 THEN (ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0)) ELSE null END as 'WHPNBG',--<EFBFBD>人<EFBFBD><EFBFBD>ŵҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>) *(ISNULL(Y2.FSHARERATE,100)/100)
|
|||
|
|
CASE WHEN A.FSaleOrgId = 438223 THEN (ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0)) ELSE null END as 'TSBG',--̼<EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>) *(ISNULL(Y2.FSHARERATE,100)/100)
|
|||
|
|
CASE WHEN A.FSaleOrgId = 438224 THEN (ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0)) ELSE null END as 'XGPNBG',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ŵҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>) *(ISNULL(Y2.FSHARERATE,100)/100)
|
|||
|
|
CASE WHEN A.FSaleOrgId = 533896 THEN (ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0)) ELSE null END as 'HXYLBG',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ҵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>) *(ISNULL(Y2.FSHARERATE,100)/100)
|
|||
|
|
CASE WHEN YEAR(A.FDATE) = 2025 THEN ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100 ELSE 0 END as 'DNLJYJ',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ۼ<EFBFBD>ҵ<EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN YEAR(A.FDATE)<2025 THEN ISNULL(A.F_AMOUNTSTAR,0)-ISNULL(B.FBILLALLAMOUNT_LC,0) ELSE null END as 'WNBGHTE',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ<EFBFBD><EFBFBD><EFBFBD>ŵ+<EFBFBD><EFBFBD>ŵ+<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ŵ<EFBFBD><EFBFBD>
|
|||
|
|
ISNULL(H.FALLAMOUNT,0)-ISNULL(C.FREALREFUNDAMOUNT,0) as 'JTHKHJ',--<EFBFBD><EFBFBD><EFBFBD>Żؿ<EFBFBD><EFBFBD>ϼ<EFBFBD>
|
|||
|
|
CASE WHEN A.FSaleOrgId = 100302 THEN ISNULL(H.FALLAMOUNT,0)-ISNULL(C.FREALREFUNDAMOUNT,0) ELSE null END as 'PNLJHKE',--<EFBFBD><EFBFBD>ŵ<EFBFBD>ۼƻؿ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN A.FSaleOrgId = 100303 THEN ISNULL(H.FALLAMOUNT,0)-ISNULL(C.FREALREFUNDAMOUNT,0) ELSE null END as 'XNLJHKE',--<EFBFBD><EFBFBD>ŵ<EFBFBD>ۼƻؿ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN A.FSaleOrgId = 100305 THEN ISNULL(H.FALLAMOUNT,0)-ISNULL(C.FREALREFUNDAMOUNT,0) ELSE null END as 'WHPNLJHKE',--<EFBFBD>人<EFBFBD><EFBFBD>ŵ<EFBFBD>ۼƻؿ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN A.FSaleOrgId = 438223 THEN ISNULL(H.FALLAMOUNT,0)-ISNULL(C.FREALREFUNDAMOUNT,0) ELSE null END as 'GDTSLJHKE',--<EFBFBD>㶫̼<EFBFBD><EFBFBD><EFBFBD>ۼƻؿ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN A.FSaleOrgId = 438224 THEN ISNULL(H.FALLAMOUNT,0)-ISNULL(C.FREALREFUNDAMOUNT,0) ELSE null END as 'XGPNLJHKE',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ŵ<EFBFBD>ۼƻؿ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN A.FSaleOrgId = 533896 THEN ISNULL(H.FALLAMOUNT,0)-ISNULL(C.FREALREFUNDAMOUNT,0) ELSE null END as 'HXYLLJHKE',--<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ۼƻؿ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
ISNULL(G4.FPERFORMANCETARGETS,0) as 'XXSMB',--2024<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD>-<EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN ISNULL(G4.FPERFORMANCETARGETS,0) = 0 THEN 0 ELSE ROUND((ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100)/ISNULL(G4.FPERFORMANCETARGETS,0),2) END as 'DBL1',--2024<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
ISNULL(G4.FPERFORMANCETARGETS,0) as 'YXSMB',--2024<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD>-ԭ
|
|||
|
|
CASE WHEN ISNULL(G4.FPERFORMANCETARGETS,0) = 0 THEN 0 ELSE ROUND((ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100)/ISNULL(G4.FPERFORMANCETARGETS,0),2) END as 'DBL2',--2024<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʣ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>궩<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>)
|
|||
|
|
ISNULL(G4.FPERFORMANCETARGETS,0)-ISNULL(G4.FPERFORMANCETARGETS,0) as 'MBTZJE',--Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
CASE WHEN YEAR(A.FDATE) = 2024 THEN ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100 ELSE 0 END as 'NDXSE1',--2024<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD>
|
|||
|
|
CASE WHEN YEAR(A.FDATE) = 2023 THEN ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100 ELSE 0 END as 'NDXSE2',--2023<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD>
|
|||
|
|
CASE WHEN YEAR(A.FDATE) = 2022 THEN ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100 ELSE 0 END as 'NDXSE3',--2022<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD>
|
|||
|
|
CASE WHEN YEAR(A.FDATE) = 2021 THEN ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100 ELSE 0 END as 'NDXSE4',--2021<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD>
|
|||
|
|
CASE WHEN YEAR(A.FDATE) = 2020 THEN ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100 ELSE 0 END as 'NDXSE5',--2020<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD>
|
|||
|
|
CASE WHEN YEAR(A.FDATE) = 2019 THEN ISNULL(B.FBILLALLAMOUNT_LC,0)*ISNULL(Y2.FSHARERATE,100)/100 ELSE 0 END as 'NDXSE6'--2019<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD>
|
|||
|
|
INTO #TABLETMEP1
|
|||
|
|
From
|
|||
|
|
T_SAL_ORDER A --<EFBFBD><EFBFBD><EFBFBD>۶<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
LEFT JOIN
|
|||
|
|
T_SAL_ORDERFIN B on A.FID = B.FID
|
|||
|
|
LEFT JOIN
|
|||
|
|
V_BD_SALESMAN_L X on X.fid = A.FSALERID
|
|||
|
|
LEFT JOIN --<EFBFBD>տ<EFBFBD><EFBFBD>˿
|
|||
|
|
T_AR_REFUNDBILL C on C.F_VRYF_TEXT_83G = A.F_CONTRACTNUMBER
|
|||
|
|
LEFT JOIN
|
|||
|
|
T_PerformanceSharing Y on Y.FSOURCEBILLNO = A.FBILLNO
|
|||
|
|
LEFT JOIN
|
|||
|
|
T_PerformanceSharingEntry Y2 on Y2.FID = Y.FID
|
|||
|
|
LEFT JOIN
|
|||
|
|
V_BD_SALESMAN_L X2 on Y2.FSALEID = X2.fid
|
|||
|
|
LEFT JOIN
|
|||
|
|
ERPTOHR.HYHRV3.dbo.v_erp_empinfo S on X.FNAME = S.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
LEFT JOIN
|
|||
|
|
ERPTOHR.HYHRV3.dbo.v_erp_empinfo S2 on X2.FNAME = S2.<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
LEFT JOIN
|
|||
|
|
(Select a1.FNAME,a2.*,a3.FNAME as 'GFNAME' From T_HR_EMPINFO_L a1 JOIN T_BD_STAFFTEMP a2 on a1.FID = a2.FID
|
|||
|
|
LEFT JOIN T_ORG_POST_L a3 on a2.FPOSTID = a3.FPOSTID
|
|||
|
|
Where a2.FENTRYID in (Select * From V_User_FID_GW)) G on G.FNAME = (CASE WHEN ISNULL(X2.FNAME,'') = '' THEN X.FNAME ELSE X2.FNAME END)
|
|||
|
|
LEFT JOIN --<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD>˾ʱ<EFBFBD><EFBFBD>
|
|||
|
|
V_User_FID_GW2 G2 on G2.FNAME = (CASE WHEN ISNULL(X2.FNAME,'') = '' THEN X.FNAME ELSE X2.FNAME END)
|
|||
|
|
LEFT JOIN --<EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>θ<EFBFBD>ʱ<EFBFBD><EFBFBD>
|
|||
|
|
V_User_FID_GW3 G3 on G3.FNAME = (CASE WHEN ISNULL(X2.FNAME,'') = '' THEN X.FNAME ELSE X2.FNAME END)
|
|||
|
|
LEFT JOIN--ҵ<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD>ؿ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
(Select a2.FPERFORMANCETARGETS,a2.FPAYMENTSUM,a3.FNAME From MBBA_t_Cust100002 a1 JOIn MBBA_t_Cust_Entry100003 a2 on a1.FID = a2.FID
|
|||
|
|
JOIN V_BD_SALESMAN_L a3 on a2.FNAME = a3.fid Where a1.FDOCUMENTSTATUS = 'C' AND F_MBBA_Combo_qtr = '2025')
|
|||
|
|
G4 on G4.FNAME = (CASE WHEN ISNULL(X2.FNAME,'') = '' THEN X.FNAME ELSE X2.FNAME END)
|
|||
|
|
LEFT JOIN
|
|||
|
|
(Select a2.FBILLNO2,CASE WHEN ISNULL(a2.FALLAMOUNT2,0)>ISNULL(b1.FALLAMOUNT3,0) THEN ISNULL(b1.FALLAMOUNT3,0)
|
|||
|
|
ELSE ISNULL(a2.FALLAMOUNT2,0) END as 'FALLAMOUNT' From T_AutoWrireRecord a1
|
|||
|
|
JOIN T_AutoWrireRecordEntry a2 on a1.FID = a2.FID
|
|||
|
|
LEFT JOIN
|
|||
|
|
(Select a1.FID,SUM(CAST(a2.FALLAMOUNT3 as DECIMAL(10,2))) as 'FALLAMOUNT3' From T_AutoWrireRecord a1
|
|||
|
|
JOIN T_AutoWrireRecordEntry2 a2 on a1.FID = a2.FID
|
|||
|
|
Where a1.FDOCUMENTSTATUS = 'C' AND Year(a2.FDATE3) = '2025'
|
|||
|
|
GROUP BY a1.FID) b1 on b1.FID = a1.FID
|
|||
|
|
Where a1.FDOCUMENTSTATUS = 'C') H on H.FBILLNO2 = A.FBILLNO
|
|||
|
|
Where A.FDOCUMENTSTATUS = 'C' AND A.FDATE >= '2025-01-01' AND A.FDATE <= '2025-01-31'
|
|||
|
|
AND ISNULL(X.FNAME,'') != ''
|
|||
|
|
|
|||
|
|
go--------------------------<EFBFBD><EFBFBD>ͼid
|
|||
|
|
|
|||
|
|
Select
|
|||
|
|
SBU,DetailArea,GName,Salesperson,RDate,ZWDate,XSDate,RZYear,SUM(CliqueBG)/10000'CliqueBG',SUM(PNBG)/10000'PNBG',SUM(XNBG)/10000'XNBG',
|
|||
|
|
SUM(WHPNBG)/10000'WHPNBG',SUM(TSBG)/10000'TSBG',SUM(XGPNBG)/10000'XGPNBG',SUM(HXYLBG)/10000'HXYLBG',SUM(DNLJYJ)/10000'DNLJYJ',SUM(WNBGHTE)/10000'WNBGHTE',SUM(JTHKHJ)/10000'JTHKHJ',SUM(PNLJHKE)/10000'PNLJHKE',
|
|||
|
|
SUM(XNLJHKE)/10000'XNLJHKE',SUM(WHPNLJHKE)/10000'WHPNLJHKE',SUM(GDTSLJHKE)/10000'GDTSLJHKE',SUM(XGPNLJHKE)/10000'XGPNLJHKE',SUM(HXYLLJHKE)/10000'HXYLLJHKE',SUM(XXSMB)/10000'XXSMB',SUM(DBL1)/10000'DBL1',
|
|||
|
|
SUM(YXSMB)/10000'YXSMB',SUM(DBL2)/10000'DBL2',SUM(MBTZJE)/10000'MBTZJE',SUM(NDXSE1)/10000'NDXSE1',SUM(NDXSE2)/10000'NDXSE2',SUM(NDXSE3)/10000'NDXSE3',SUM(NDXSE4)/10000'NDXSE4',SUM(NDXSE5)/10000'NDXSE5',SUM(NDXSE6)/10000'NDXSE6'
|
|||
|
|
INTO #TABLETMEP2
|
|||
|
|
From #TABLETMEP1 GROUP BY SBU,DetailArea,GName,Salesperson,RDate,ZWDate,XSDate,RZYear;
|
|||
|
|
|
|||
|
|
Select * From #TABLETMEP2
|
|||
|
|
|
|||
|
|
--SELECT distinct F_Papercontract FROM T_SAL_DELIVERYNOTICE WHERE F_SFYFH ='<EFBFBD><EFBFBD>'
|