Sql-Server-2005
如何計算員工遲到分鐘的總和並使用儲存過程插入另一個表
員工每天遲到分鐘儲存在一個表中（Attndate），我想計算個別員工遲到分鐘的總和，然後使用 sql server 儲存過程插入另一個表（LateMuster）
重要的是，我想在“2017-09-01”和“2017-09-08”之類的兩個日期之間獲得遲到的時間
Attndate - 表
預期輸出 - LateMuster-Table
最後我會用這張表來水晶報告（使用vb.net）
注意：- 我正在使用 sql server 2005 和 Visual Studio 2005
假設：
- 根據 MSSQL 文件，該問題被標記
sql-server-2005為不支持 CTE 和 PIVOT 表，直到sql-server-2008- 這個問題也被標記了，
stored-procedures所以我猜一些程序編碼是可以接受的（即，這不必是單個查詢）對於這個答案，我們將著眼於填充 #temp 表，然後動態建構查詢以將數據“透視”到 31 列/天…
我冒昧地為 8 月添加了一些數據，這樣我們就可以對這個月進行一個總結，加上當月第 1 天（8 月 1 日，9 月 1 日）的幾個雙重條目：
drop table if exists Attndate; create table Attndate (EMPCODE int ,EMPNAME varchar(30) ,[DATE] date ,InTime time ,LateMins int); insert into AttnDate (EMPCODE, EMPNAME,[DATE],InTime,LateMins) values (1,'John' ,'08/01/2017','9:17', 7), (2,'David','08/01/2017','9:18', 8), (3,'Kumar','08/01/2017','9:12', 2), (4,'Jack' ,'08/01/2017','9:18', 8), (1,'John' ,'08/26/2017','9:27',17), (2,'David','08/26/2017','9:20',10), (3,'Kumar','08/26/2017','9:17', 7), (4,'Jack' ,'08/26/2017','9:13', 3), (5,'Rose' ,'08/28/2017','9:31',21), (2,'David','08/29/2017','9:28',18), (3,'Kumar','08/30/2017','9:23',23), (4,'Jack' ,'08/31/2017','9:59',49), (1,'John' ,'09/01/2017','9:30',20), (2,'David','09/01/2017','9:25',15), (3,'Kumar','09/01/2017','9:12', 2), (4,'Jack' ,'09/01/2017','9:15', 5), (5,'Rose' ,'09/02/2017','9:36',26), (2,'David','09/02/2017','9:18', 8), (3,'Kumar','09/02/2017','9:13', 3), (4,'Jack' ,'09/02/2017','9:51',41), (1,'John' ,'09/03/2017','9:30', 4), (3,'Kumar','09/03/2017','9:30',13);我們將用轉換為月份的日期加上按月份的日期匯總來填充我們的
#rollup表格：[DATE]``LateMinsdrop table if exists #rollup; select EMPCODE, EMPNAME, day([DATE]) as dom, sum(LateMins) as lm into #rollup from Attndate where [Date] between '2017-08-01' and '2017-09-30' group by EMPCODE, EMPNAME, day([DATE])現在我們使用循環構造來建構我們的“樞軸”查詢：
declare @query varchar(max), @ctr int select @ctr=1, @query = 'select distinct r1.EMPCODE, r1.EMPNAME' while @ctr < 32 begin select @query = @query + char(10) + ', (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=' + convert(varchar(2),@ctr) + ') as [' + convert(varchar(2),@ctr) + ']' select @ctr = @ctr + 1 end set @query = @query + char(10) + ', (select sum(lm) from #rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins]' + char(10) +' from #rollup r1 order by 1,2' print @query execute (@query)將
print @query我們的“樞軸”查詢顯示為：select distinct r1.EMPCODE, r1.EMPNAME , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=1) as [1] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=2) as [2] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=3) as [3] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=4) as [4] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=5) as [5] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=6) as [6] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=7) as [7] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=8) as [8] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=9) as [9] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=10) as [10] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=11) as [11] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=12) as [12] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=13) as [13] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=14) as [14] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=15) as [15] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=16) as [16] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=17) as [17] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=18) as [18] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=19) as [19] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=20) as [20] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=21) as [21] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=22) as [22] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=23) as [23] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=24) as [24] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=25) as [25] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=26) as [26] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=27) as [27] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=28) as [28] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=29) as [29] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=30) as [30] , (select lm from #rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=31) as [31] , (select sum(lm) from #rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins] from #rollup r1 order by 1,2該
execute (@query)語句生成我們的“數據透視表”：EMPCODE | EMPNAME | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | Total Late Mins ------- | ------- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | --------------- 1 | John | 27 | null | 4 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 17 | null | null | null | null | null | 48 2 | David | 23 | 8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 10 | null | null | 18 | null | null | 59 3 | Kumar | 4 | 3 | 13 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 7 | null | null | null | 23 | null | 50 4 | Jack | 13 | 41 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 3 | null | null | null | null | 49 | 106 5 | Rose | null | 26 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 21 | null | null | null | 47這是這個答案的小提琴。
撇開 的潛在限制不談
sql-server-2005，這是一個動態建構由 CTE 錨定的“樞軸”查詢的解決方案。我冒昧地為 8 月添加了一些數據，這樣我們就可以對這個月進行一個總結，加上當月第 1 天（8 月 1 日，9 月 1 日）的幾個雙重條目：
drop table if exists Attndate; create table Attndate (EMPCODE int ,EMPNAME varchar(30) ,[DATE] date ,InTime time ,LateMins int); insert into AttnDate (EMPCODE, EMPNAME,[DATE],InTime,LateMins) values (1,'John' ,'08/01/2017','9:17', 7), (2,'David','08/01/2017','9:18', 8), (3,'Kumar','08/01/2017','9:12', 2), (4,'Jack' ,'08/01/2017','9:18', 8), (1,'John' ,'08/26/2017','9:27',17), (2,'David','08/26/2017','9:20',10), (3,'Kumar','08/26/2017','9:17', 7), (4,'Jack' ,'08/26/2017','9:13', 3), (5,'Rose' ,'08/28/2017','9:31',21), (2,'David','08/29/2017','9:28',18), (3,'Kumar','08/30/2017','9:23',23), (4,'Jack' ,'08/31/2017','9:59',49), (1,'John' ,'09/01/2017','9:30',20), (2,'David','09/01/2017','9:25',15), (3,'Kumar','09/01/2017','9:12', 2), (4,'Jack' ,'09/01/2017','9:15', 5), (5,'Rose' ,'09/02/2017','9:36',26), (2,'David','09/02/2017','9:18', 8), (3,'Kumar','09/02/2017','9:13', 3), (4,'Jack' ,'09/02/2017','9:51',41), (1,'John' ,'09/03/2017','9:30', 4), (3,'Kumar','09/03/2017','9:30',13);在 CTE 的幫助下動態建構我們的“樞軸”查詢：
declare @query varchar(max), @ctr int select @ctr=1, @query = 'with rollup as (select EMPCODE, EMPNAME, day([DATE]) as dom, sum(LateMins) as lm from Attndate where [Date] between ''2017-08-01'' and ''2017-09-30'' group by EMPCODE, EMPNAME, day([DATE])) select distinct r1.EMPCODE, r1.EMPNAME' while @ctr < 32 begin select @query = @query + char(10) + ', (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=' + convert(varchar(2),@ctr) + ') as [' + convert(varchar(2),@ctr) + ']' select @ctr = @ctr + 1 end select @query = @query + char(10) + ', (select sum(lm) from rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins]' + char(10) +' from rollup r1 order by 1,2' print @query execute (@query)
- CTE（匯總）將
[DATE]’s 轉換為月份中的天數（dom），以及匯總LateMins（lm）- 子查詢
(rollup r2)為我們提供了結果集的最後一列[Total Late Mins]將
print @query我們的 ‘pivot’ 查詢顯示為：with rollup as (select EMPCODE, EMPNAME, day([DATE]) as dom, sum(LateMins) as lm from Attndate where [Date] between '2017-08-01' and '2017-09-30' group by EMPCODE, EMPNAME, day([DATE])) select distinct r1.EMPCODE, r1.EMPNAME , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=1) as [1] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=2) as [2] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=3) as [3] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=4) as [4] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=5) as [5] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=6) as [6] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=7) as [7] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=8) as [8] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=9) as [9] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=10) as [10] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=11) as [11] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=12) as [12] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=13) as [13] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=14) as [14] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=15) as [15] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=16) as [16] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=17) as [17] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=18) as [18] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=19) as [19] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=20) as [20] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=21) as [21] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=22) as [22] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=23) as [23] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=24) as [24] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=25) as [25] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=26) as [26] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=27) as [27] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=28) as [28] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=29) as [29] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=30) as [30] , (select lm from rollup r2 where r2.EMPCODE=r1.EMPCODE and r2.dom=31) as [31] , (select sum(lm) from rollup r2 where r2.EMPCODE=r1.EMPCODE) as [Total Late Mins] from rollup r1 order by 1,2該
execute (@query)語句生成我們的“數據透視表”：EMPCODE | EMPNAME | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | Total Late Mins ------- | ------- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | --------------- 1 | John | 27 | null | 4 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 17 | null | null | null | null | null | 48 2 | David | 23 | 8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 10 | null | null | 18 | null | null | 59 3 | Kumar | 4 | 3 | 13 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 7 | null | null | null | 23 | null | 50 4 | Jack | 13 | 41 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 3 | null | null | null | null | 49 | 106 5 | Rose | null | 26 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 21 | null | null | null | 47這是這個答案的小提琴。