「休假直轉橫」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
已建立頁面,內容為 "==程式碼== <SQL> SELECT UD_ID, UD_NAME, [1], [2], [3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17], [18],[19],[20],[21],[22],[23],[24],[25],[2…"
 
 
第1行: 第1行:
==程式碼==
==程式碼==
<SQL>
<SQL>
SELECT UD_ID, UD_NAME, [1], [2], [3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],
SELECT UD_ID, UD_NAME,(SELECT
[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] FROM UserData  
CASE WHEN UD_BRANCHS ='RD' THEN '品技'
WHEN UD_BRANCHS ='TSK' THEN '廠務'
WHEN UD_BRANCHS ='MI' THEN '廠務'
WHEN UD_BRANCHS ='AC' THEN '廠務'
WHEN UD_BRANCHS ='SA' THEN '業務'
WHEN UD_BRANCHS ='WM' THEN '製造'
WHEN UD_BRANCHS ='HM' THEN '加工'
ELSE '' END) BRANCHS,
 
[1], [2], [3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],
[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],
(SELECT
CASE WHEN UD_BRANCHS ='RD' THEN 3
WHEN UD_BRANCHS ='TSK' THEN 2
WHEN UD_BRANCHS ='MI' THEN 2
WHEN UD_BRANCHS ='AC' THEN 2
WHEN UD_BRANCHS ='SA' THEN 1
WHEN UD_BRANCHS ='WM' THEN 5
WHEN UD_BRANCHS ='HM' THEN 4
ELSE '' END) BRANCHSORDER
FROM UserData  
LEFT JOIN
LEFT JOIN
(
(
第18行: 第39行:
) p
) p
) PP
) PP
ON UD_ID=LW01 WHERE UD_OUTDATE IS NULL AND UD_BRANCHS IS NOT NULL
ON UD_ID=LW01 WHERE UD_OUTDATE IS NULL AND UD_BRANCHS IS NOT NULL AND UD_ID <>'admin'
ORDER BY UD_ID
ORDER BY BRANCHSORDER,UD_ID
</SQL>
</SQL>

於 2022年5月9日 (一) 08:35 的最新修訂

程式碼

<SQL> SELECT UD_ID, UD_NAME,(SELECT CASE WHEN UD_BRANCHS ='RD' THEN '品技' WHEN UD_BRANCHS ='TSK' THEN '廠務' WHEN UD_BRANCHS ='MI' THEN '廠務' WHEN UD_BRANCHS ='AC' THEN '廠務' WHEN UD_BRANCHS ='SA' THEN '業務' WHEN UD_BRANCHS ='WM' THEN '製造' WHEN UD_BRANCHS ='HM' THEN '加工' ELSE END) BRANCHS,

[1], [2], [3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],

[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31], (SELECT CASE WHEN UD_BRANCHS ='RD' THEN 3 WHEN UD_BRANCHS ='TSK' THEN 2 WHEN UD_BRANCHS ='MI' THEN 2 WHEN UD_BRANCHS ='AC' THEN 2 WHEN UD_BRANCHS ='SA' THEN 1 WHEN UD_BRANCHS ='WM' THEN 5 WHEN UD_BRANCHS ='HM' THEN 4 ELSE END) BRANCHSORDER

FROM UserData LEFT JOIN ( SELECT * FROM ( SELECT LW01,LEFT(LW02,1) LW02,DATEPART(d,LW03 )LW03 FROM APRdayoff WHERE LW03 BETWEEN '2022-05-01' AND '2022-05-31' ) t PIVOT ( -- 設定彙總欄位及方式 MAX(LW02) -- 設定轉置欄位,並指定轉置欄位中需彙總的條件值作為新欄位 FOR LW03 IN ([1], [2], [3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17], [18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) p ) PP ON UD_ID=LW01 WHERE UD_OUTDATE IS NULL AND UD_BRANCHS IS NOT NULL AND UD_ID <>'admin' ORDER BY BRANCHSORDER,UD_ID </SQL>