小弟要寫一隻報表,已經將內容 SUM成
工號 假別 時數
101 事假 3
101 病假 5
101 公假 3
102 事假 2
103 病假 1
但小弟想把這樣的表寫成像底下這樣
工號 事假 病假 公假
101 3 5 3
102 2 0 0
103 0 1 0
已經開始在設計報表內容,但所需要的欄位都放在 detail 那層裡面
然後一開始的那些資料小弟不顯示出來,只放空欄位顯示
打算用程式去將第一項的資料丟到空格,再顯示成下面那個想要的
可是就算不顯示,他資料的空格還是會跳動,造成我寫的時候
資料不出來,整頁都是空格,請問我該怎麼處理
或者是還有什麼更好的寫法,感謝
SELECT
[EMPLOYEE].[CNNAME],
[EMPLOYEE].[CODE],
CASE WHEN sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]) IS NULL THEN 0 ELSE sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]) END ,
[ATTENDANCETYPE].[SHORTNAME]
FROM [ATTENDANCELEAVE]
LEFT JOIN [EMPLOYEE] ON [ATTENDANCELEAVE].[EMPLOYEEID] = [EMPLOYEE].[EMPLOYEEID] LEFT JOIN ATTENDANCETYPE ATTENDANCETYPE ON [ATTENDANCETYPE].[ATTENDANCETYPEID] = [ATTENDANCELEAVE].[ATTENDANCETYPEID]
GROUP BY [EMPLOYEE].[CNNAME],[EMPLOYEE].[CODE],[ATTENDANCETYPE].[SHORTNAME]
改成這樣試試.
如果可以,再解釋為什麼要這樣改.
我是怕:WHEN sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]) IS NULL
這一段會出錯.
CASE WHEN 條件 (= NULL)
THEN 條件為真時的執行段(填0).
ELSE 條件為偽的執行段(加總數字)
END
另外:WHERE 條件中應該少了日期區間的判斷
select EMPLOYEEID] ,[EMPLOYEE].[CNNAME],[EMPLOYEE].[CODE],
'事假'= (select CASE WHEN sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]) IS NULL THEN 0 ELSE sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]) END from [ATTENDANCELEAVE] where [ATTENDANCELEAVE].[EMPLOYEEID] = [EMPLOYEE].[EMPLOYEEID] AND [ATTENDANCELEAVE].[ATTENDANCETYPEID]='事假' AND 日期條件) ,
'病假'= (select CASE WHEN sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]) IS NULL THEN 0 ELSE sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]) END from [ATTENDANCELEAVE] where [ATTENDANCELEAVE].[EMPLOYEEID] = [EMPLOYEE].[EMPLOYEEID] AND [ATTENDANCELEAVE].[ATTENDANCETYPEID]='病假' AND 日期條件)
FROM [EMPLOYEE]