iT邦幫忙

0

鼎新 HRM 自定義報表

小弟要寫一隻報表,已經將內容 SUM成
工號 假別 時數
101 事假 3
101 病假 5
101 公假 3
102 事假 2
103 病假 1

但小弟想把這樣的表寫成像底下這樣
工號         事假          病假           公假 
 101            3                  5                   3
 102            2                  0                    0
 103            0                  1                     0
 
 
已經開始在設計報表內容,但所需要的欄位都放在 detail 那層裡面
然後一開始的那些資料小弟不顯示出來,只放空欄位顯示
打算用程式去將第一項的資料丟到空格,再顯示成下面那個想要的

可是就算不顯示,他資料的空格還是會跳動,造成我寫的時候
資料不出來,整頁都是空格,請問我該怎麼處理

或者是還有什麼更好的寫法,感謝
有沒有目前的語法?
tonyrao iT邦新手 5 級 ‧ 2016-07-25 13:05:20 檢舉
SELECT
[EMPLOYEE].[CNNAME],[EMPLOYEE].[CODE],sum([ATTENDANCELEAVE].[EFFECTIVEHOURS]),[ATTENDANCETYPE].[SHORTNAME]

FROM [EMPLOYEE]

LEFT JOIN ATTENDANCELEAVE ATTENDANCELEAVE ON [ATTENDANCELEAVE].[EMPLOYEEID] = [EMPLOYEE].[EMPLOYEEID] LEFT JOIN ATTENDANCETYPE ATTENDANCETYPE ON [ATTENDANCETYPE].[ATTENDANCETYPEID] = [ATTENDANCELEAVE].[ATTENDANCETYPEID]

GROUP BY [EMPLOYEE].[CNNAME],[EMPLOYEE].[CODE],[ATTENDANCETYPE].[SHORTNAME]
tonyrao iT邦新手 5 級 ‧ 2016-07-25 13:06:27 檢舉
不過一直到 報表設計那邊 ,就會出現我的問題了~~ 這個SQL語法只是作加總而已

1 個回答

0
做工仔人!
iT邦高手 1 級 ‧ 2016-07-25 13:43:53

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]

我要發表回答

立即登入回答