iT邦幫忙

7

純玩SQL~計算當月每週工時

sql
純真的人 2020-11-25 20:59:37 ‧ 3272 瀏覽

  • 分享至 

  • xImage

今天想到這東西~看大家會如何解出@@~
晚點貼答案...

需求：
1.計算A先生10月上班工時
2.每週區間以星期一~星期天為一筆
那麼A先生當月每週工時會是多少@@..

我給基本資料：

declare @StartDate date = '2020/10/1'
declare @EndDate date = '2020/10/31'
declare @Emp table(
	EmpName nvarchar(20)
)
insert into @Emp
values('A')
declare @Data table(
	DataName nvarchar(20)
	,WorkDate date
	,WorkHr decimal(18, 2)
)
insert into @Data
values('A','2020/10/1',8)
,('A','2020/10/2',9)
,('A','2020/10/5',8)
,('A','2020/10/6',9.5)
,('A','2020/10/7',8)
,('A','2020/10/13',9)
,('A','2020/10/14',9.5)
,('A','2020/10/15',8)
,('A','2020/10/16',8)
,('A','2020/10/27',8.5)
,('A','2020/10/28',9)
,('A','2020/10/29',8)
,('A','2020/10/30',9)

這是查詢後的結果：
https://ithelp.ithome.com.tw/upload/images/20201125/20061369p6Aq8ay2Ic.png

歡迎大家試看看功力~

--

我一開始先把10月區間內的每週先算出來~

select WeekStartDate
,dateadd(d,6,WeekStartDate) WeekEndDate
from (
	select dateadd(d,n-1,@StartDate) SetDate
	,dateadd(day
	,(
		case datepart(weekday,dateadd(d,n-1,@StartDate))
		when 1 then -6
		when 2 then 0
		when 3 then -1
		when 4 then -2
		when 5 then -3
		when 6 then -4
		when 7 then -5
		else 0
		end
	)
	,dateadd(d,n-1,@StartDate)
	) WeekStartDate
	from (
		select Row_Number()Over(order by number) n
		from master..spt_values
	) k
	where n <= datediff(d,@StartDate,@EndDate) + 1
) k
group by WeekStartDate
,dateadd(d,6,WeekStartDate)

https://ithelp.ithome.com.tw/upload/images/20201125/20061369oFK43Mz78D.png

然後再導入員工每週時數去計算得出@@..以下就是我土法練鋼的方式@@...

select EmpName
,WeekStartDate
,dateadd(d,6,WeekStartDate) WeekEndDate
,isNull((
	select Sum(WorkHr)
	from @Data
	where DataName = EmpName
	and WorkDate between WeekStartDate and dateadd(d,6,WeekStartDate)
),0) WorkHr
from (
	select dateadd(d,n-1,@StartDate) SetDate
	,dateadd(day
	,(
		case datepart(weekday,dateadd(d,n-1,@StartDate))
		when 1 then -6
		when 2 then 0
		when 3 then -1
		when 4 then -2
		when 5 then -3
		when 6 then -4
		when 7 then -5
		else 0
		end
	)
	,dateadd(d,n-1,@StartDate)
	) WeekStartDate
	from (
		select Row_Number()Over(order by number) n
		from master..spt_values
	) k
	where n <= datediff(d,@StartDate,@EndDate) + 1
) k
,@Emp
group by EmpName
,WeekStartDate
,dateadd(d,6,WeekStartDate)
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

9
石頭
iT邦高手 1 級 ‧ 2020-11-25 21:39:56
最佳解答

我會使用CTE遞迴+Outer JOIN完成他

;WITH CTE AS (
	SELECT  DATEADD(wk, DATEDIFF(wk,0,MIN(WorkDate)), 0) StartDate, 
		DATEADD(wk, DATEDIFF(wk,0,MAX(WorkDate)), 6) EndDate,
		DataName
	FROM @Data
	GROUP BY DataName
	UNION ALL
	SELECT DATEADD(DAY,7,StartDate) StartDate,
		  EndDate,
		  DataName
	FROM CTE 
	WHERE  DATEADD(DAY,7,StartDate) < EndDate
),CTE2 AS (
	SELECT StartDate,
		   DATEADD(DAY,6,StartDate) EndDate,
		   DataName
	FROM CTE c
)
SELECT c.DataName,
	   SUM(ISNULL(WorkHr,0)) 'WorkHr',
	   c.StartDate,
	   c.EndDate
FROM CTE2 c
LEFT JOIN @Data d
ON d.WorkDate BETWEEN c.StartDate AND c.EndDate
GROUP BY 
	DATEPART(WEEK,WorkDate),
	c.DataName,
	c.StartDate,
	c.EndDate
ORDER BY DataName,StartDate

dbfiddle

純真的人 iT邦大師 1 級 ‧ 2020-11-25 21:48:57 檢舉

喔喔~這招CTE遞迴方式也滿厲害的~

登入發表回應
3
japhenchen
iT邦超人 1 級 ‧ 2020-11-25 21:57:40

SQL Fiddle

MS SQL Server 2017 Schema Setup:

declare @StartDate date = '2020/10/1'
declare @EndDate date = '2020/10/31'
create  table Emp( 
	EmpName nvarchar(20)
)
insert into Emp values ('A')

create table Data(
	DataName nvarchar(20)
	,WorkDate date
	,WorkHr decimal(18, 2)
)
insert into Data
values('A','2020/10/1',8)
,('A','2020/10/2',9)
,('A','2020/10/5',8)
,('A','2020/10/6',9.5)
,('A','2020/10/7',8)
,('A','2020/10/13',9)
,('A','2020/10/14',9.5)
,('A','2020/10/15',8)
,('A','2020/10/16',8)
,('A','2020/10/27',8.5)
,('A','2020/10/28',9)
,('A','2020/10/29',8)
,('A','2020/10/30',9)

Query 1:

SELECT 
  DATEPART(WEEK,WorkDate) AS '周期',
  MIN(WorkDate) AS '從',
  MAX(WorkDate) AS '到',
  sum(WorkHr) AS '周工時'
FROM Data 
left join emp on emp.empname = data.dataname
GROUP BY DATEPART(WEEK,WorkDate)
ORDER BY DATEPART(WEEK,WorkDate)

Results:

| 周期 |          從 |          到 |  周工時 |
|----|------------|------------|------|
| 40 | 2020-10-01 | 2020-10-02 |   17 |
| 41 | 2020-10-05 | 2020-10-07 | 25.5 |
| 42 | 2020-10-13 | 2020-10-16 | 34.5 |
| 44 | 2020-10-27 | 2020-10-30 | 34.5 |
看更多先前的回應...收起先前的回應...
純真的人 iT邦大師 1 級 ‧ 2020-11-25 22:21:00 檢舉

雖然沒有星期一到星期天的表~
這也是快速方式@@~

japhenchen iT邦超人 1 級 ‧ 2020-11-25 22:35:38 檢舉

偷吃步(砍小力一點)
/images/emoticon/emoticon07.gif

SQL Fiddle

MS SQL Server 2017 Schema Setup:

declare @StartDate date = '2020/10/1'
declare @EndDate date = '2020/10/31'
create  table Emp( 
	EmpName nvarchar(20)
)
insert into Emp values ('A')

create table Data(
	DataName nvarchar(20)
	,WorkDate date
	,WorkHr decimal(18, 2)
)
insert into Data
values('A','2020/10/1',8)
,('A','2020/10/2',9)
,('A','2020/10/5',8)
,('A','2020/10/6',9.5)
,('A','2020/10/7',8)
,('A','2020/10/13',9)
,('A','2020/10/14',9.5)
,('A','2020/10/15',8)
,('A','2020/10/16',8)
,('A','2020/10/27',8.5)
,('A','2020/10/28',9)
,('A','2020/10/29',8)
,('A','2020/10/30',9)

Query 1:

DECLARE @FIRST2020 AS DATETIME
SET @FIRST2020 = '2020/1/1'
DECLARE @DW INT
SET @DW = DATEPART(WEEKDAY,@FIRST2020)-2

SELECT 
  DATEPART(WEEK,WorkDate) AS '周期',
  @FIRST2020+7*(DATEPART(WEEK,WorkDate)-1)-@DW AS '從',
  @FIRST2020+7*DATEPART(WEEK,WorkDate)-@DW-1 AS '到',
  sum(WorkHr) AS '周工時'
FROM Data 
left join emp on emp.empname = data.dataname
GROUP BY DATEPART(WEEK,WorkDate)
ORDER BY DATEPART(WEEK,WorkDate)

Results:

| 周期 |                    從 |                    到 |  周工時 |
|----|----------------------|----------------------|------|
| 40 | 2020-09-28T00:00:00Z | 2020-10-04T00:00:00Z |   17 |
| 41 | 2020-10-05T00:00:00Z | 2020-10-11T00:00:00Z | 25.5 |
| 42 | 2020-10-12T00:00:00Z | 2020-10-18T00:00:00Z | 34.5 |
| 44 | 2020-10-26T00:00:00Z | 2020-11-01T00:00:00Z | 34.5 |
純真的人 iT邦大師 1 級 ‧ 2020-11-25 22:43:05 檢舉 
@FIRST2020+7*(DATEPART(WEEK,WorkDate)-1)-@DW AS '從'

這個厲害呢~比我用土法練鋼去算區間調整@@還省~

japhenchen iT邦超人 1 級 ‧ 2020-11-25 22:55:27 檢舉

單用函數來處理遠比join表來跑高效...我的想法就比較直一點了

japhenchen iT邦超人 1 級 ‧ 2020-11-27 08:30:34 檢舉

開大絕，全年度列表，年月日也format讓人好閱讀一點
SQL Fiddle

MS SQL Server 2017 Schema Setup:

declare @StartDate date = '2020/10/1'
declare @EndDate date = '2020/10/31'

create  table Emp( 
	EmpName nvarchar(20)
)
insert into Emp values ('A')

create table Data(
	DataName nvarchar(20)
	,WorkDate date
	,WorkHr decimal(18, 2)
)
insert into Data
values('A','2020/10/1',8)
,('A','2020/10/2',9)
,('A','2020/10/5',8)
,('A','2020/10/6',9.5)
,('A','2020/10/7',8)
,('A','2020/10/13',9)
,('A','2020/10/14',9.5)
,('A','2020/10/15',8)
,('A','2020/10/16',8)
,('A','2020/10/27',8.5)
,('A','2020/10/28',9)
,('A','2020/10/29',8)
,('A','2020/10/30',9)

Query 1:

DECLARE @QYEAR INT 
SET @QYEAR = 2020
DECLARE @QMONTH INT 
SET @QMONTH = 10
DECLARE @fmtDate VARCHAR(10)
SET @fmtDate = 'yyyy/MM/dd'
DECLARE @FIRST2020 AS DATETIME
SET @FIRST2020 = DATEFROMPARTS(@QYEAR,1,1)
DECLARE @DW INT
SET @DW = DATEPART(WEEKDAY,@FIRST2020)-2
DECLARE @sWK INT ,@eWK INT
SET @sWK = 1
SET @eWK =53

SELECT 
  WKS.N AS '周期',
  FORMAT(@FIRST2020+7*(N-1)-@DW,@fmtDate) AS '從',
  FORMAT(@FIRST2020+7*N-(@DW+1),@fmtDate) AS '到',
  ISNULL(sum(WorkHr),0) AS '周工時'
FROM (
  SELECT TOP (@eWK-@sWK+1) @sWK-1+row_number() over(order by t1.number) as N
  FROM master..spt_values t1 CROSS JOIN master..spt_values t2
) WKS
LEFT OUTER JOIN Data on DATEPART(WEEK,Data.WorkDate)=WKS.N 
LEFT JOIN emp on emp.empname = data.dataname
-- DATEPART(MONTH, Data.WorkDate) = @QMonth
GROUP BY N,DATEPART(WEEK,Data.WorkDate) 
ORDER BY N

Results:

| 周期 |          從 |          到 |  周工時 |
|----|------------|------------|------|
|  1 | 2019/12/30 | 2020/01/05 |    0 |
|  2 | 2020/01/06 | 2020/01/12 |    0 |
|  3 | 2020/01/13 | 2020/01/19 |    0 |
|  4 | 2020/01/20 | 2020/01/26 |    0 |
|  5 | 2020/01/27 | 2020/02/02 |    0 |
|  6 | 2020/02/03 | 2020/02/09 |    0 |
|  7 | 2020/02/10 | 2020/02/16 |    0 |
|  8 | 2020/02/17 | 2020/02/23 |    0 |
|  9 | 2020/02/24 | 2020/03/01 |    0 |
| 10 | 2020/03/02 | 2020/03/08 |    0 |
| 11 | 2020/03/09 | 2020/03/15 |    0 |
| 12 | 2020/03/16 | 2020/03/22 |    0 |
| 13 | 2020/03/23 | 2020/03/29 |    0 |
| 14 | 2020/03/30 | 2020/04/05 |    0 |
| 15 | 2020/04/06 | 2020/04/12 |    0 |
| 16 | 2020/04/13 | 2020/04/19 |    0 |
| 17 | 2020/04/20 | 2020/04/26 |    0 |
| 18 | 2020/04/27 | 2020/05/03 |    0 |
| 19 | 2020/05/04 | 2020/05/10 |    0 |
| 20 | 2020/05/11 | 2020/05/17 |    0 |
| 21 | 2020/05/18 | 2020/05/24 |    0 |
| 22 | 2020/05/25 | 2020/05/31 |    0 |
| 23 | 2020/06/01 | 2020/06/07 |    0 |
| 24 | 2020/06/08 | 2020/06/14 |    0 |
| 25 | 2020/06/15 | 2020/06/21 |    0 |
| 26 | 2020/06/22 | 2020/06/28 |    0 |
| 27 | 2020/06/29 | 2020/07/05 |    0 |
| 28 | 2020/07/06 | 2020/07/12 |    0 |
| 29 | 2020/07/13 | 2020/07/19 |    0 |
| 30 | 2020/07/20 | 2020/07/26 |    0 |
| 31 | 2020/07/27 | 2020/08/02 |    0 |
| 32 | 2020/08/03 | 2020/08/09 |    0 |
| 33 | 2020/08/10 | 2020/08/16 |    0 |
| 34 | 2020/08/17 | 2020/08/23 |    0 |
| 35 | 2020/08/24 | 2020/08/30 |    0 |
| 36 | 2020/08/31 | 2020/09/06 |    0 |
| 37 | 2020/09/07 | 2020/09/13 |    0 |
| 38 | 2020/09/14 | 2020/09/20 |    0 |
| 39 | 2020/09/21 | 2020/09/27 |    0 |
| 40 | 2020/09/28 | 2020/10/04 |   17 |
| 41 | 2020/10/05 | 2020/10/11 | 25.5 |
| 42 | 2020/10/12 | 2020/10/18 | 34.5 |
| 43 | 2020/10/19 | 2020/10/25 |    0 |
| 44 | 2020/10/26 | 2020/11/01 | 34.5 |
| 45 | 2020/11/02 | 2020/11/08 |    0 |
| 46 | 2020/11/09 | 2020/11/15 |    0 |
| 47 | 2020/11/16 | 2020/11/22 |    0 |
| 48 | 2020/11/23 | 2020/11/29 |    0 |
| 49 | 2020/11/30 | 2020/12/06 |    0 |
| 50 | 2020/12/07 | 2020/12/13 |    0 |
| 51 | 2020/12/14 | 2020/12/20 |    0 |
| 52 | 2020/12/21 | 2020/12/27 |    0 |
| 53 | 2020/12/28 | 2021/01/03 |    0 |
純真的人 iT邦大師 1 級 ‧ 2020-11-27 09:44:50 檢舉

哈~~

登入發表回應
9
一級屠豬士
iT邦大師 1 級 ‧ 2020-11-25 23:31:52 
create table it201125a (
  id int generated always as identity
, name text not null 
, workdate date not null
, workhr decimal(5,2) not null
);

insert into it201125a(name, workdate, workhr) values
 ('A','2020/10/1',8)
,('A','2020/10/2',9)
,('A','2020/10/5',8)
,('A','2020/10/6',9.5)
,('A','2020/10/7',8)
,('A','2020/10/13',9)
,('A','2020/10/14',9.5)
,('A','2020/10/15',8)
,('A','2020/10/16',8)
,('A','2020/10/27',8.5)
,('A','2020/10/28',9)
,('A','2020/10/29',8)
,('A','2020/10/30',9);

select name
     , date_trunc('week', min(workdate))::date weekstartdate
     , (date_trunc('week', min(workdate))::date + interval '6 days')::date weekenddate
     , sum(workhr) sumwrkhr
  from it201125a
 group by name, EXTRACT(week from workdate)
 order by 2;

 name | weekstartdate | weekenddate | sumwrkhr 
------+---------------+-------------+----------
 A    | 2020-09-28    | 2020-10-04  |    17.00
 A    | 2020-10-05    | 2020-10-11  |    25.50
 A    | 2020-10-12    | 2020-10-18  |    34.50
 A    | 2020-10-26    | 2020-11-01  |    34.50
(4 rows)
純真的人 iT邦大師 1 級 ‧ 2020-11-26 01:36:11 檢舉

這SQL也很厲害@@!

一級屠豬士 iT邦大師 1 級 ‧ 2020-11-26 08:01:40 檢舉

補充另外方式

with t1 as (
select name
     , extract(week from min(workdate))::int minweekn
     , extract(week from max(workdate))::int maxweekn
     , extract(year from min(workdate))::text myear
  from it201125a
 group by name
), t2 as (
select name
     , weekn
     , myear
  from t1
  join lateral (select generate_series(t1.minweekn, t1.maxweekn) weekn) gen
    on true
), t3 as (
select name
     , weekn
     , to_date(myear || weekn::text, 'IYYYIW') weekstartdate
     , (to_date(myear || weekn::text, 'IYYYIW') + interval '6 days')::date weekenddate
  from t2
), t4 as (
select name
     , extract(week from workdate) weekn
     , sum(workhr) sumwrkhr
  from it201125a
 group by 1, 2
)
select t3.name
     , t3.weekstartdate
     , t3.weekenddate
     , coalesce(t4.sumwrkhr,0) as workhrs
  from t3
  left join t4
    on t3.name = t4.name
   and t3.weekn = t4.weekn
;

 name | weekstartdate | weekenddate | workhrs 
------+---------------+-------------+---------
 A    | 2020-09-28    | 2020-10-04  |   17.00
 A    | 2020-10-05    | 2020-10-11  |   25.50
 A    | 2020-10-12    | 2020-10-18  |   34.50
 A    | 2020-10-19    | 2020-10-25  |       0
 A    | 2020-10-26    | 2020-11-01  |   34.50
(5 rows)
一級屠豬士 iT邦大師 1 級 ‧ 2020-11-26 08:22:03 檢舉 
--第二道較為繁複,但會做些填充沒出現在工作紀錄上的週間資料.

insert into it201125a(name, workdate, workhr) values
('A','2020/09/11',9),
('A','2020/11/12',8);

commit;

-- 再使用第二道查詢可得到

 name | weekstartdate | weekenddate | workhrs 
------+---------------+-------------+---------
 A    | 2020-09-07    | 2020-09-13  |    9.00
 A    | 2020-09-14    | 2020-09-20  |       0
 A    | 2020-09-21    | 2020-09-27  |       0
 A    | 2020-09-28    | 2020-10-04  |   17.00
 A    | 2020-10-05    | 2020-10-11  |   25.50
 A    | 2020-10-12    | 2020-10-18  |   34.50
 A    | 2020-10-19    | 2020-10-25  |       0
 A    | 2020-10-26    | 2020-11-01  |   34.50
 A    | 2020-11-02    | 2020-11-08  |       0
 A    | 2020-11-09    | 2020-11-15  |    8.00
(10 rows)
登入發表回應

我要發表回答

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