7

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

1.計算A先生10月上班工時
2.每週區間以星期一~星期天為一筆

``````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)
``````

--

``````select WeekStartDate
from (
,(
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
)
) 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
``````

``````select EmpName
,WeekStartDate
,isNull((
select Sum(WorkHr)
from @Data
where DataName = EmpName
and WorkDate between WeekStartDate and dateadd(d,6,WeekStartDate)
),0) WorkHr
from (
,(
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
)
) 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

``````

### 3 個回答

9

iT邦研究生 2 級 ‧ 2020-11-25 21:39:56

``````;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
EndDate,
DataName
FROM CTE
WHERE  DATEADD(DAY,7,StartDate) < EndDate
),CTE2 AS (
SELECT StartDate,
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

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)
``````
``````| 周期 |          從 |          到 |  周工時 |
|----|------------|------------|------|
| 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 |
``````

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)
``````
``````| 周期 |                    從 |                    到 |  周工時 |
|----|----------------------|----------------------|------|
| 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 |
``````
``````@FIRST2020+7*(DATEPART(WEEK,WorkDate)-1)-@DW AS '從'
``````

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
``````
``````| 周期 |          從 |          到 |  周工時 |
|----|------------|------------|------|
|  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 |
``````

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)

``````

``````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)

``````
``````--第二道較為繁複,但會做些填充沒出現在工作紀錄上的週間資料.

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)
``````