iT邦幫忙

0

SQL語法考勤上下班問題？

程式設計
badboy01 2013-01-25 10:45:10 ‧ 14895 瀏覽

請教有一出勤資料庫，我用以下語法

Select convert(varchar,date,111)+'  '+datename(weekday,date) as date,employeeID,NameID
,case when datepart(hour,date) <12 then convert(varchar,date,108) else N'無' end as N'上班' 
,case when datepart(hour,date) >=12 then convert(varchar,date,108) else N'無' end as N'下班'  
From myabsent

結果如附圖上班與下班
我該如何改變SQL語法
可以顯示出如下格式
日期 員工編號 姓名 上班 下班 事件
2012-01-22 A01 小張 8:30 17:45
2012-01-22 A02 小吳 8:15 18:50
2012-01-23 A03 小天 8:28 無刷卡
2012-01-24 A04 小林 8:35 18:21 遲到
2012-01-25 A05 小玉 無刷卡 18:35
如果有重複刷卡情形，上班取最早時間的一筆資料，下班取最晚時間的一筆資料，如果遲到顯示
謝謝！

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

4 個回答

14
charmmih
iT邦研究生 5 級 ‧ 2013-01-25 17:28:20
最佳解答 
<pre class="c" name="code">
select a.DAY1, a.employeeID,a.NameID 
,case when min(b.date) IS NOT null  then convert(varchar,min(b.date),108) else N'無' end as N'上班'    
,case when  max(c.date) IS NOT null   then convert(varchar,max(c.date),108) else N'無' end as N'下班' 
from (select  distinct convert(varchar,date,112) DAY1 ,employeeID,NameID
		from myabsent 
		where  date >= CONVERT(smalldatetime,'20120101',112) and date <CONVERT(smalldatetime,'20120201',112)) a
left join myabsent b on convert(varchar,b.date,112)=c.DAY1 
	and a.employeeID=b.employeeID and a.NameID=b.NameID 
left join myabsent c on convert(varchar,c.date,112)=c.DAY1 
	and a.employeeID=c.employeeID and a.NameID=c.NameID 
where datepart(hour,b.date) <12 and datepart(hour,c.date) >=12

小心時間轉換, 否則效能會很差,
一般處理習慣是將時間轉成字串, 再新增入表格, 較直覺容易處理.
記得, 還有建相關索引~~

看更多先前的回應...收起先前的回應...
badboy01 iT邦新手 2 級 ‧ 2013-01-28 13:56:06 檢舉

大大您好！
我使用大大方法執行有錯誤(找不到c.DAY1資料行)和少了grpou by，不過我將兩行c.DAY1改為a.DAY1和加入group by後可執行，但是結果是空白一片，不知道是哪裡有誤@@"，謝謝！

badboy01 iT邦新手 2 級 ‧ 2013-01-28 14:33:51 檢舉

大大抱歉，是我疏忽，日期區間輸入錯誤~~已經可以了^^，感謝大大

charmmih iT邦研究生 5 級 ‧ 2013-01-28 14:40:04 檢舉

left join myabsent b on convert(varchar,b.date,112)=c.DAY1
and a.employeeID=b.employeeID and a.NameID=b.NameID
left join myabs...(恕刪)

確實, 應改為 a.DAY1, 我疏忽了...
left join myabsent b on convert(varchar,b.date,112)=a.DAY1
and a.employeeID=b.employeeID and a.NameID=b.NameID
left join myabsent c on convert(varchar,c.date,112)=a.DAY1
and a.employeeID=c.employeeID and a.NameID=c.NameID

badboy01 iT邦新手 2 級 ‧ 2013-01-28 14:48:02 檢舉

大大您好！
發現一個問題我日期區間下20130101~20130201，可是結果只有1/22有資料，1/23沒有顯示出來？(目前用來測試資料是1/22有上下班資料，1/23只有上班資料)，謝謝！

badboy01 iT邦新手 2 級 ‧ 2013-01-28 15:00:37 檢舉

大大您好！
補充一下，應該說沒有刷卡時應該會顯示'無'，可以結果是沒有顯示未刷的資料。謝謝！

badboy01 iT邦新手 2 級 ‧ 2013-01-28 17:21:31 檢舉

大大您好！
我將
where datepart(hour,b.date) <12 and datepart(hour,c.date) >=12 中間 and 改為 or 之後可以看見1/22,1/23資料，但是有人下班沒刷卡"下班"欄位會顯示上班刷卡的時間？

charmmih iT邦研究生 5 級 ‧ 2013-01-28 17:47:22 檢舉

select a.DAY1, a.employeeID,a.NameID
,case when min(b.date) IS NOT null then convert(varchar,min(b.date),108) else N'無' end as N'上班'
,case when max(c.date) IS NOT null then convert(varchar,max(c.date),108) else N'無' end as N'下班'
from (select distinct convert(varchar,date,112) DAY1 ,employeeID,NameID
from myabsent
where date >= CONVERT(smalldatetime,'20120101',112) and date <CONVERT(smalldatetime,'20120201',112)) a
left join myabsent b on convert(varchar,b.date,112)=a.DAY1
and a.employeeID=b.employeeID and a.NameID=b.NameID
and datepart(hour,b.date) <12
left join myabsent c on convert(varchar,c.date,112)=a.DAY1
and a.employeeID=c.employeeID and a.NameID=c.NameID
and datepart(hour,c.date) >=12

這樣跑看看囉...

badboy01 iT邦新手 2 級 ‧ 2013-01-29 08:47:01 檢舉

感恩大大幫忙，可以正確跑出結果，非常感謝！請教大大通常語法 select 欄位,.... from (select 欄位,... from table where) as 別名 這段語法不是很懂，謝謝!

charmmih iT邦研究生 5 級 ‧ 2013-01-29 10:24:35 檢舉

  1. 感謝 badboy01 提供這個案例, 還有 richardsuma, smile 兩位大大先行發想,
    接下來我將兩位大大的想法加以整合寫出.

  2. 說明如下:
    a: 是先做維度 (distinct DAY1,employeeID,NameID)
    b: 填入上班資訊
    c: 填入下班資料

badboy01 iT邦新手 2 級 ‧ 2013-01-29 11:08:40 檢舉

charmmih大大您好！
從view看大概了解a,b,c的table互相關係，只是在什麼時候要用到<select 欄位,.... from (select 欄位,... from table where) as 別名> 語法呢?可以請大大解釋一下嗎?感恩^^

slime iT邦大師 1 級 ‧ 2013-01-29 11:41:28 檢舉

charmmih 網友的多個 select , 跟原本我提的 select 功能是雷同的啦:
先分析好最後產出的結果, 反推中間過程需要的資料, 再反推並用 select 產生這些資料.

作法上: charmmih 是放在同一行指令內, 而後面會多幾個 select , 我則是先 select 放到暫存表格, 結果是雷同的; 只是我覺得要 debug 方便, 撰寫時都會用暫存表格, 來確保資料流與邏輯相符.

badboy01 iT邦新手 2 級 ‧ 2013-01-29 16:11:25 檢舉

slime 大大補充說明，另請教slime大大，您提供的語法有一段是 where 上下午='上午'和 上下午='下午'，網路上查詢好像是說where不能搭配別名方式處理?

slime iT邦大師 1 級 ‧ 2013-01-29 16:35:17 檢舉

因為我寫的只是"大意", 用來想邏輯用的而已, 所以後面也有補充: 作法不只一種.

舉 charmmih 的實作, 是採用 datepart(hour,b.date) <12 來判斷上下午, 其中的 b 就是資料表 myabsent 的別名哦; 我的例子則是建立 #tmp_raw , [上下午] 已經不是別名, 而是欄位名稱嘍.

charmmih iT邦研究生 5 級 ‧ 2013-01-29 17:11:55 檢舉

  1. from 後面的子查詢, 叫做衍生表格 視同一般表格, 通常是:
    a. 外層 select 有用到其欄位 (DAY1),
    b. 此衍生表格往往是資料要先處理, 本例有先做 distinct

  2. badboy01 若有心要好好深入研究SQL, 有一本書要買<SQL 案例解析>,
    繁體版已絕版, 簡體版台灣剩不多, 欲購從速.

  3. 時時試著用1個sql指令查出你要的結果, 隨時查看資料庫是否有合適的索引,
    你才能善用資料庫的特長, 你的SQL才會下得精進.

badboy01 iT邦新手 2 級 ‧ 2013-01-31 08:42:53 檢舉

感謝charmmih大大建議，我已經上網拍訂了一本，再次感謝大大^^

登入發表回應
16
richardsuma
iT邦大師 1 級 ‧ 2013-01-25 11:04:37

使用 min() 與 max() 如下:

Select convert(varchar,date,111)+' '+datename(weekday,date) as date,employeeID,NameID
,case when min(datepart(hour,date)) <12 then convert(varchar,date,108) else N'無' end as N'上班'
,case when max(datepart(hour,date)) >=12 then convert(varchar,date,108) else N'無' end as N'下班'
From myabsent

看更多先前的回應...收起先前的回應...
badboy01 iT邦新手 2 級 ‧ 2013-01-25 11:28:59 檢舉

大大您好！
依照大大改語法之後結果沒變，好奇怪？

slime iT邦大師 1 級 ‧ 2013-01-25 12:31:05 檢舉

badboy01提到：
2012-01-23 A03 小玉 8:28 無
2012-01-25 A05 小玉 無 18:35

看起來沒問題啊....
小玉 1/23 8:28 打卡, 之後 1/25 18:35 又打卡.
所以比較可能: 小玉 1/23 下班沒打卡, 1/25 上班也沒打卡(不過 18:35 還沒到耶)

另外注意一下:

  1. 有沒有公式或資料回傳用的時區設定不一致的狀況. (例如卡機用 GMT , 主機用 GMT+8 )
  2. 打卡鐘最近有沒有斷電或其他原因造成資料 lose ? (少見, 但偶爾有瑕疵品會出現)
richardsuma iT邦大師 1 級 ‧ 2013-01-25 13:01:26 檢舉

為什麼 小玉的員工編號 不一樣?
這樣會造成資料不會在同一列!

richardsuma iT邦大師 1 級 ‧ 2013-01-25 13:02:37 檢舉

而且如 slime 所說的 日期也不一樣?

badboy01 iT邦新手 2 級 ‧ 2013-01-25 13:27:23 檢舉

抱歉小玉員工編號我打錯了，應該是一樣的。

badboy01 iT邦新手 2 級 ‧ 2013-01-25 13:47:43 檢舉

抱歉各位大大，舉例內容有誤,請參考補充內容

badboy01 iT邦新手 2 級 ‧ 2013-01-31 08:55:37 檢舉

謝謝richardsuma大大提供另一種方法`沒想到SQL還有這種指令。

richardsuma iT邦大師 1 級 ‧ 2013-01-31 10:08:23 檢舉

只是沒有辦法解決 三班輪值 的問題？

slime iT邦大師 1 級 ‧ 2013-01-31 10:46:11 檢舉

richardsuma提到：
只是沒有辦法解決 三班輪值 的問題？

以我用過的邏輯大約是這樣:

  1. 員工必須先有排班表, 各班有對應的"起始時間"與"結束時間".

  2. 若打卡時間在"有排班(已經排除休假)"的"起始時間"與"結束時間"內, 就是遲到或早退.
    再把我的回答的第 3 個步驟:

    <pre class="c" name="code">3. 等步驟 2 處理完, 才判斷
    a. 12 點前的打卡時間, 是漏打還是遲到.
    b. 12 點後的打卡時間, 是漏打還是早退.

改成

&lt;pre class="c" name="code">3. 等步驟 2 處理完, 以打卡時間對照當天的出勤時間, 是漏打還是遲到/早退.

原理就是"上下班認定的基準點", 由題目的"中午 12 點"(很奇怪的點), 更細緻的調整成:

&lt;pre class="c" name="code">
1. 系統預設每個人的應上班時間.
2. 員工排休假或調班.
3. 打卡時間記錄, 對應排班或休假, 若無特殊排班或休假, 則對應標準應上班時間.
4. 做成差勤記錄.
5. 月底後 5 日內確認差勤記錄, 若無誤則轉薪資系統處理.
badboy01 iT邦新手 2 級 ‧ 2013-01-31 11:45:35 檢舉

感謝slime大大提供建議，我會慢慢學習消化吸收，目前還只是SQL新手，太多不懂了@@"

richardsuma iT邦大師 1 級 ‧ 2013-01-31 23:55:25 檢舉

還是 slime 的經驗豐富。

登入發表回應
14
slime
iT邦大師 1 級 ‧ 2013-01-25 14:27:33

  1. 看起來原始資料有 'employeeID', 'date' 兩欄, 分別代表'員工號'及'打卡時間', 另外連結 'NameID' 是'員工姓名'.

  2. 公式裏用了 convert(varchar,date,111) , 一般是把 date 當保留字, 不會把變數用 date .

  3. 再來原本的 select 為

    <pre class="c" name="code">convert(varchar,date,111)+' '+datename(weekday,date) as date

既然用 as , 前後的名稱就最好不要一樣, 才知道是原始資料還是轉換的.
4. 最後看了一下公式, 簡化為

&lt;pre class="c" name="code">Select date,employeeID,NameID,N'上班',as N'下班'

就邏輯上感覺有點怪, 如果以比較簡單的表格(employeeID,date)來看, datepart(hour,date)如果是 <12 就會在上班, >=12 就會在下班, 所以出現在上班, 就不會出現在下班, 後來補充的範例並沒有問題.

只是以需求來看, 應該要依這樣的步驟思考:

  1. 抓讀卡機資料.

  2. (目前看起來要補這一項)把讀卡機資料轉成上班打卡記錄, 以標準朝酒晚舞(畫線), 可能像這樣:

    <pre class="c" name="code">員工號,日期,12點前的打卡時間,12點後的打卡時間

這部份應該要搭配 select ... group by 處理
3. 等步驟 2 處理完, 才判斷
a. 12 點前的打卡時間, 是漏打還是遲到.
b. 12 點後的打卡時間, 是漏打還是早退.
c. 當天都沒打, 與出差或請假記錄比對.

看更多先前的回應...收起先前的回應...
badboy01 iT邦新手 2 級 ‧ 2013-01-25 14:33:37 檢舉

對喔~還要考慮當天忘記打的情況.....看樣子真不好處理= ='

slime iT邦大師 1 級 ‧ 2013-01-25 14:41:28 檢舉

補充公式(沒有 MS SQL 可測, 大意寫一下)

&lt;pre class="c" name="code">
select 員工號, convert(varchar,打卡時間,111) as 打卡日, 打卡時間, case (12點前算上午,12點後算下午) as 上下午 into #temp_raw
select 員工號, 打卡日, min(打卡時間) as 上班打卡 from #temp_raw where 上下午='上午' into #temp_morning
select 員工號, 打卡日, max(打卡時間) as 下班打卡 from #temp_raw where 上下午='下午' into #temp_afternoon
select 員工號, 打卡日, 上班打卡, 下班打卡 from #temp_raw join #temp_morning join #temp_afternoon group by 員工號, 打卡日

還是有很多工作上會實作的東西沒有檢測到, 例如: 有分多班制的公司, 有加班等動作的處理, 如果以基本使用大概是這樣吧. (歡迎各位捐 MS Windows Server + SQL 給我, 可折現更好)

badboy01 iT邦新手 2 級 ‧ 2013-01-25 14:52:04 檢舉

謝謝大大指導，請問 case (12點前算上午,12點後算下午)這段內容要怎麼寫呢？看不太懂@@"

slime iT邦大師 1 級 ‧ 2013-01-25 15:00:27 檢舉

沒有一定要用 case 啦, 只是讓後面的程式可以判斷是上午或下午, 也可以用 if , 或把 datetime 的 am/pm 抓出來.

如果要我寫, 就請捐一套合法的 SQL 給我吧....(好窮)

badboy01 iT邦新手 2 級 ‧ 2013-01-25 15:01:04 檢舉

請問slime大大,select有3個,括弧需要嗎?

badboy01 iT邦新手 2 級 ‧ 2013-01-25 15:02:44 檢舉

slime大大，抱歉沒有合法的SQL@@"

slime iT邦大師 1 級 ‧ 2013-01-25 15:03:55 檢舉

我寫的 select 只有 3 個嗎? @_@

slime iT邦大師 1 級 ‧ 2013-01-25 15:12:50 檢舉

沒有合法的 SQL , 那就只好請大家看看上面的邏輯, 自己改寫了....

外獅佬 iT邦大師 1 級 ‧ 2013-01-25 17:12:38 檢舉

slime提到：
合法的 SQL

用Express版就好了咩.....開心

slime iT邦大師 1 級 ‧ 2013-01-25 17:35:04 檢舉

重點是要教人釣魚而不是要釣具啦....釣具我有 SQLite , 而是要跟網友說怎麼找到魚群, 不然拿 Shimano 釣竿卻釣了木魚就麻煩了....

登入發表回應
8
jeffreyhu
iT邦新手 4 級 ‧ 2013-01-27 18:46:13

以Oracle舉例
使用row_number(partition by XX order by 刷卡日期) rn 的函數操作[刷卡資料表]
可以做每日第一筆及每日最後一筆刷卡資料的取出,但因為其中有可能會有,遲到,提早下班,加班, 彈性上下班等等狀況,請再自行做判斷

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙