add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQLite
SQL Server
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
5.5
5.6
5.7
8.0
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
3.8
3.16
3.27
3.39
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
2.11
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
run
markdown
donate
feedback
about
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
SET DATEFORMAT mdy; CREATE TABLE TableA ( PstngDate date, WorkingDayOutput int ); INSERT INTO TableA VALUES('12/1/2020', 221), ('12/3/2020', 327), ('12/4/2020', 509), ('12/5/2020', 418), ('12/7/2020', 390), ('12/8/2020', 431), ('12/9/2020', 244), ('12/10/2020', 246), ('12/11/2020', 314), ('12/12/2020', 301), ('12/14/2020', 411), ('12/15/2020', 530), ('12/16/2020', 554), ('12/17/2020', 300), ('12/18/2020', 375), ('12/23/2020', 402), ('12/24/2020', 302), ('12/25/2020', 269), ('12/26/2020', 382), ('12/28/2020', 608); CREATE TABLE TableB ( PstngDate date, HolidayOutput int, isWorkingDay bit, ); INSERT INTO TableB VALUES('12/2/2020', 20, 0), ('12/6/2020', 24, 0), ('12/13/2020', 31, 0), ('12/19/2020', 82, 0), ('12/22/2020', 507, 0), ('12/27/2020', 537, 0);
26 rows affected
;WITH CTE AS ( SELECT PstngDate, LEAD(PstngDate) OVER(ORDER BY PstngDate) nextdate, WorkingDayOutput FROM TableA ) SELECT a.PstngDate, a.WorkingDayOutput, SUM(b.HolidayOutput) HolidayOutput FROM CTE a LEFT JOIN TableB b ON b.PstngDate > a.PstngDate AND b.PstngDate < a.nextdate GROUP BY a.PstngDate, a.WorkingDayOutput ORDER BY a.PstngDate
PstngDate
WorkingDayOutput
HolidayOutput
2020-12-01
221
20
2020-12-03
327
null
2020-12-04
509
null
2020-12-05
418
24
2020-12-07
390
null
2020-12-08
431
null
2020-12-09
244
null
2020-12-10
246
null
2020-12-11
314
null
2020-12-12
301
31
2020-12-14
411
null
2020-12-15
530
null
2020-12-16
554
null
2020-12-17
300
null
2020-12-18
375
589
2020-12-23
402
null
2020-12-24
302
null
2020-12-25
269
null
2020-12-26
382
537
2020-12-28
608
null
Warning: Null value is eliminated by an aggregate or other SET operation.