MSSQL trigger 是否可以做#temp　，while

mssql trigger #temp while

noway 2023-07-14 14:47:57 ‧ 1016 瀏覽

分享至

您好：
請問
MSSQL 內 trigger，是否方便做#temp　，while
會影響效能嗎？

目前刷卡鐘，　上下班會重複刷　，多時段區間刷
所以得用 trigger 來判斷　
上班重複刷　，或已經有刷　下班，　新的一筆就要再寫一筆上班
下班：要判斷　他是第一段下班，第二段下班　，刷多次，要抓最後一筆（未再刷　上班前）

謝謝

天黑 iT邦研究生 5 級 ‧ 2023-07-14 16:57:37 檢舉

也許可以考慮用排程處理，用trigger 打卡每次都掃全部資料... 有點硬

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2023-07-14 15:02:47

最佳解答

建議:
1.先把刷卡鐘的資料格式弄清楚
2.評估定時將資料匯入 DB
3.寫個 SQL 擷取正確的資料寫入另一個 Table
4.統計出缺席資料(工時)符合人資需求
5.產出每週報表(工號,日期,上班未打卡,下班未打卡,曠職....)

回應 9
分享
檢舉

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

noway iT邦研究生 1 級 ‧ 2023-07-14 18:26:23 檢舉

您好：
目前

前段　原廠刷卡格式－－有
～～中間　刷卡資料　轉入　ERP TBALE ~~ 有重複
後段　ERP 計算刷卡－－有

但因為　人臉感應較靈敏，有時會誤刷，或多刷
導致　先刷上班２筆，不小心　誤刷下一筆下班　最後再刷一筆下班

或者　請假，就刷２段卡

目前要先處理的是　上班刷卡多筆，下班刷卡多筆，要做出區間

乃至，後續　門禁，要看進門／出門多少時間

謝謝

rogeryao iT邦超人 7 級 ‧ 2023-07-14 18:38:55 檢舉

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 解答了

一級屠豬士 iT邦大師 1 級 ‧ 2023-07-15 11:46:18 檢舉

贊成 rogeryao 大大的看法, 可以先把資料與想達到的描述一下.這查詢SQL應該可以寫出來的.

noway iT邦研究生 1 級 ‧ 2023-07-15 13:26:31 檢舉

您好：
謝謝
1.row_number()有想過，
但　類似以下情況
上班
上班
上班
下班
下班

上班
下班

這樣　不好區分兩段

2.有看到其他室要在table 中做手腳
上班－－狀態空
下班－－將上班況態補上
來做triger 更新條件

謝謝

rogeryao iT邦超人 7 級 ‧ 2023-07-15 13:48:31 檢舉

建議:
1.將資料建好,才不會雞同鴨講
2.搭配人資的行事曆,排除假日;再搭配請假單
3.這類的問題用排程處理應較適宜(每週五或月底前跑一次,讓員工補假單;隔月出報表給人資)

noway iT邦研究生 1 級 ‧ 2023-07-17 08:48:41 檢舉

您好：
先針對刷卡
如下，１　上班，２　下班
前面刷２次上班，一般以第一次刷上班03:55:04為　上班時間
接著刷２次下班，以最後刷得為主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

rogeryao iT邦超人 7 級 ‧ 2023-07-17 09:05:19 檢舉

1.要定義上班及下班時間
2.請提供預期的結果

rogeryao iT邦超人 7 級 ‧ 2023-07-17 10:38:57 檢舉

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
日期要另外勾稽人資的行事曆
大概就是這樣做吧

```
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](https://dbfiddle.uk/TrXCE1LH)
日期要另外勾稽人資的行事曆
大概就是這樣做吧

修改

noway iT邦研究生 1 級 ‧ 2023-07-19 14:50:39 檢舉

您好：謝謝您!
但前段，沒有設班表，單純拋時間
我大致另外的方式可做了
謝謝

登入發表回應

PPTaiwan

iT邦好手 1 級 ‧ 2023-07-14 15:20:46

使用 #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 瀏覽器的檢查