建議:
1.先把刷卡鐘的資料格式弄清楚
2.評估定時將資料匯入 DB
3.寫個 SQL 擷取正確的資料寫入另一個 Table
4.統計出缺席資料(工時)符合人資需求
5.產出每週報表(工號,日期,上班未打卡,下班未打卡,曠職....)
您好:
目前
前段 原廠刷卡格式--有
~~中間 刷卡資料 轉入 ERP TBALE ~~ 有重複
後段 ERP 計算刷卡--有
但因為 人臉感應較靈敏,有時會誤刷,或多刷
導致 先刷上班2筆,不小心 誤刷下一筆下班 最後再刷一筆下班
或者 請假,就刷2段卡
目前要先處理的是 上班刷卡多筆,下班刷卡多筆,要做出區間
乃至,後續 門禁,要看進門/出門多少時間
謝謝
1.先定義好上下班的時段
2.使用類似
Select *
From (
Select *,
row_number() over (partition by ... order by ... TimeX) AS No <==遲到 ; order by ... TimeX Desc 早退
....
From X) AS M
Where TimeX > Start and TimeX <= End
and No = 1
站內有很多類似的問題,都有 SQL 解答了
贊成 rogeryao 大大的看法, 可以先把資料與想達到的描述一下.這查詢SQL應該可以寫出來的.
您好:
謝謝
1.row_number()有想過,
但 類似以下情況
上班
上班
上班
下班
下班
上班
下班
這樣 不好區分兩段
2.有看到其他室要在table 中做手腳
上班--狀態空
下班--將上班況態補上
來做triger 更新條件
謝謝
建議:
1.將資料建好,才不會雞同鴨講
2.搭配人資的行事曆,排除假日;再搭配請假單
3.這類的問題用排程處理應較適宜(每週五或月底前跑一次,讓員工補假單;隔月出報表給人資)
您好:
先針對刷卡
如下,1 上班,2 下班
前面刷2次上班,一般以第一次刷上班03:55:04為 上班時間
接著刷2次下班,以最後刷得為主05:05:04,為下班
接著刷一次上班,06:55:04, 這是第二段上班
謝謝
SELECT * from (
SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 03:55:04.000' as recorddatetime
UNION all
SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 03:55:06.000' as recorddatetime
UNION all
SELECT '000130' as empno, '2' as attendanttype , '2023-07-12 05:01:04.000' as recorddatetime
UNION all
SELECT '000130' as empno, '2' as attendanttype , '2023-07-12 05:05:04.000' as recorddatetime
UNION all
SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 06:55:04.000' as recorddatetime
UNION all
SELECT '000131' as empno, '2' as attendanttype , '2023-07-12 03:55:07.000' as recorddatetime
UNION all
SELECT '000131' as empno, '2' as attendanttype , '2023-07-12 03:55:05.000' as recorddatetime
) m
1.要定義上班及下班時間
2.請提供預期的結果
Create View View_X AS (
SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 03:55:04.000' as recorddatetime
UNION all
SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 03:55:06.000' as recorddatetime
UNION all
SELECT '000130' as empno, '2' as attendanttype , '2023-07-12 05:01:04.000' as recorddatetime
UNION all
SELECT '000130' as empno, '2' as attendanttype , '2023-07-12 05:05:04.000' as recorddatetime
UNION all
SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 06:55:04.000' as recorddatetime
UNION all
SELECT '000131' as empno, '2' as attendanttype , '2023-07-12 03:55:07.000' as recorddatetime
UNION all
SELECT '000131' as empno, '2' as attendanttype , '2023-07-12 03:55:05.000' as recorddatetime
-- 額外加入
UNION ALL
SELECT '000132' as empno, '1' as attendanttype , '2023-07-12 04:55:04.000' as recorddatetime
UNION ALL
SELECT '000132' as empno, '1' as attendanttype , '2023-07-12 04:55:08.000' as recorddatetime
-- 額外加入
UNION ALL
SELECT '000133' as empno, '2' as attendanttype , '2023-07-12 04:55:04.000' as recorddatetime
UNION ALL
SELECT '000133' as empno, '2' as attendanttype , '2023-07-12 07:55:08.000' as recorddatetime
)
-- 定義上班時段
Create View View_Z AS (
Select 1 AS No_P,'00:00:00' AS Start_P,'04:00:00' AS End_P
Union All
-- 第一段上班時段 : 04 - 05
Select 2 AS No_P,'04:00:00' AS Start_P,'05:00:00' AS TEnd_P
Union All
Select 3 AS No_P,'05:00:00' AS Start_P,'07:00:00' AS TEnd_P
Union All
-- 第二段上班時段 : 07 - 08
Select 4 AS No_P,'07:00:00' AS Start_P,'08:00:00' AS TEnd_P
Union All
Select 5 AS No_P,'08:00:00' AS Start_P,'12:00:00' AS TEnd_P
)
-- 上班
Select *,Case When No_P = 1 Or No_P = 3 Then N'準時上班' Else N'遲到' End As Status
From (
Select View_X.*,No_P,
row_number() over (partition by empno,attendanttype,No_P order by recorddatetime) AS No,
CONVERT(varchar(10),cast(recorddatetime AS DateTime),111) AS Date_X,
CONVERT(varchar(12),cast(recorddatetime AS DateTime), 108) AS Time_X
From View_X
Left Join View_Z On CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) > View_Z.Start_P
And CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) < View_Z.End_P
Where attendanttype = '1'
) AS QQ
Where No = 1
Order By empno,attendanttype,recorddatetime
-- 下班
Select *,Case When No_P = 1 Or No_P = 3 Then N'準時下班' Else N'早退' End As Status
From (
Select View_X.*,No_P,
row_number() over (partition by empno,attendanttype,No_P order by recorddatetime) AS No,
CONVERT(varchar(10),cast(recorddatetime AS DateTime),111) AS Date_X,
CONVERT(varchar(12),cast(recorddatetime AS DateTime), 108) AS Time_X
From View_X
Left Join View_Z On CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) > View_Z.Start_P
And CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) < View_Z.End_P
Where attendanttype = '2'
) AS QQ
Where No = 1
Order By empno,attendanttype,recorddatetime
Demo
日期要另外勾稽人資的行事曆
大概就是這樣做吧
您好:謝謝您!
但 前段,沒有設班表,單純拋時間
我大致另外的方式可做了
謝謝
使用 #temp 對 IO 資源產生壓力,特別是當大量的資料寫入。
通常我會用 DECLARE @tABLE TABLE 來做處理,對 WHILE 或是用 DECLARE CURSOR FOR 影響會比較小。 就算你抓一堆資料到 #temp 也是影響了 IO 再做任何 CRUD 也是差不了多少的,你可以試試用 DECLARE @tABLE TABLE 會不會比較好一點。
今天才處理客戶拿 VIEW 來做
(SELECT ROW_NUMBER() OVER ( Order By [sic_code] ))
BETWEEN 1 AND 50
當資料一多時其實效率很差,因為 VIEW 算是實體 TABLE 多做任何事情只會影響到最後查詢結果,我以頁面載入的畫面做比較,以下圖是用 Google 瀏覽器的檢查
這還是 32 筆資料,當資料是 100 筆以上時舊版 3 分鐘,新版還是 1.8~2.0 秒以內,丟給頁面很簡單 dataset + Repeater.DataBind(); 就這樣子而己
但你的問題,我覺得應該只是
上班打卡時間 (09:00 正常 , 9:30 晚到 , 09:31 就是扣一小時)
下班打卡時間 (18:00 正常 , 18:30 正常 , 18:29 早退)
加班打卡時間 (19:00 開始)
加班下班打卡時間 (23:00 為最終結束時間,這段時間超過 23:01 都不算加班)
只要規定好時間,任同仁如何刷上下班都沒有差,反正看記錄來決定。這是我很久寫過的用捷運悠遊卡做的打卡系統所定的..
用 SELECT 下去就可以知道同仁上下班情形了