iT邦幫忙

2

考勤統計刷進刷退的問題

考勤機的紀錄表

卡號, 時間
A0001, 2018/3/16 22:03:05
A0001, 2018/3/16 14:20:10
A0001, 2018/3/16 03:05:33
A0001, 2018/3/15 14:07:30
A0001, 2018/3/15 14:05:15
A0001, 2018/3/14 21:09:30
A0001, 2018/3/14 13:50:15

SQL要如何下才會出現我要的結果?
卡號, 刷進 ,刷退
A0001, 2018/3/16 14:20:10 ,2018/3/16 22:03:05
A0001, 2018/3/15 14:05:15 ,2018/3/16 03:05:33
A0001, 2018/3/14 13:50:15 ,2018/3/14 21:09:30

ps.上班時間為14:00~22:00為正常班,有效打卡以13:00為間隔一日判斷


解答:
https://ithelp.ithome.com.tw/upload/images/20180320/200613694F8oyBqY49.png

哈~回程路上~靈光一閃~自己解答完了= =a
歡迎貼高手張貼自己的答案~
明天再貼我的解答@@...

1 個回答

5
暐翰
iT邦大師 1 級 ‧ 2018-03-20 21:03:04
最佳解答

感覺好玩的題目
我這邊先用本土煉鋼方式(沒考慮效能版本),一步一步分解來組合出你的SQL

這是之前未優化版本(本土煉鋼方式):

【未優化版本】https://goo.gl/3uJKsN

更新優化版本的範例:

【優化版本】https://goo.gl/oWgztn

--【以下建立測試資料數據】
create table #Tem_Table ([USERID] nvarchar(10),[Datetime] datetime );
insert into #Tem_Table ([USERID],[Datetime]) values 
	('A0001','2018/3/16 22:03:05')
    ,('A0001','2018/3/16 03:05:33')
    ,('A0001','2018/3/15 14:07:30')
    ,('A0001','2018/3/15 14:05:15')
    ,('A0001','2018/3/14 21:09:30')
    ,('A0001','2018/3/14 13:50:15')
    ,('A0002','2018/3/14 21:09:30')
    ,('A0002','2018/3/14 13:50:15')    
;

--【開始拼接你要的打卡紀錄】
select 
    [USERID] 卡號
    ,min([datetime]) 刷進 --取最小排名得 "刷進"
    ,max([datetime]) 刷出  --取最大排名得 "刷出"
from #Tem_Table 
group BY CONVERT(CHAR(8), dateadd (hour,-13,[datetime]), 112) --減十三小時
    ,[USERID] --這邊要分組人要不然會資料錯亂 

結果:

主要邏輯:

1:區分時間區段

主要要能夠區分時間區段,而公司比較特別不是以半夜00:00來區分
假如是00:00可以用天來分組很方便。
但這邊就反向思考一下,只要把所有時間都減上13小時
其實就可以照著00:00的方式來分組

2:排名、分組

以時間(不包含時分秒)分組,
並依實際日期取其min(日期)得到刷進,並取其max(日期)得到刷退

看更多先前的回應...收起先前的回應...

嗯~~~既然你貼了~我也貼出來吧XD

明天再標記為最佳解答~

暐翰 iT邦大師 1 級 ‧ 2018-03-20 21:23:38 檢舉

/images/emoticon/emoticon12.gif
真厲害
你這樣簡潔多了,我這個就一個"本土煉鋼"方式 XD

暐翰 iT邦大師 1 級 ‧ 2018-03-21 05:04:22 檢舉

我這邊有更新優化版本 :)

好唷~~

我要發表回答

立即登入回答