「休假直轉橫」:修訂間差異
已建立頁面,內容為 "==程式碼== <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>