SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
	Jul 25 2020 11:26:55 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table t (yyyymmdd date, period varchar(20), country varchar(20), cat1 varchar(20), cat2 varchar(20), cat3 varchar(20), score INT);

insert into t values('20191201', 'A', 'US', 'Sports',	'Cycle',	'long',	33);
insert into t values('20191201', 'A', 'US', 'Sports',	'Cycle',	'Short',	35);
insert into t values('20191201', 'A', 'US', 'Sports',	'Running',	'long',	28);
insert into t values('20191201', 'A', 'US', 'Sports',	'Running',	'short',	29);
insert into t values('20191201', 'A', 'US', 'Arts',	NULL,NULL	,NULL	);
insert into t values('20191202', 'A', 'US', 'Sports',	'Cycle',	'long',	43);
insert into t values('20191202', 'A', 'US', 'Sports',	'Cycle',	'Short',	44);
insert into t values('20191202', 'A', 'US', 'Sports',	'Running',	'long',	35);
insert into t values('20191202', 'A', 'US', 'Sports',	'Running',	'short',	37);
insert into t values('20191202', 'A', 'US', 'Arts',	NULL,NULL,NULL);	
insert into t values('20201201', 'B', 'US', 'Sports',	'Cycle'	,'long'	,33);
insert into t values('20201201', 'B', 'US', 'Sports',	'Cycle',	'Short',	35);
insert into t values('20201201', 'B', 'US', 'Sports',	'Running',	'long',	28);
insert into t values('20201201', 'B', 'US', 'Sports',	'Running',	'short',	29);
insert into t values('20201201', 'B', 'US', 'Arts',	NULL,NULL,NULL);		
15 rows affected

select * from t


yyyymmdd
period
country
cat1
cat2
cat3
score


2019-12-01
A
US
Sports
Cycle
long
33

2019-12-01
A
US
Sports
Cycle
Short
35

2019-12-01
A
US
Sports
Running
long
28

2019-12-01
A
US
Sports
Running
short
29

2019-12-01
A
US
Arts
null
null
null

2019-12-02
A
US
Sports
Cycle
long
43

2019-12-02
A
US
Sports
Cycle
Short
44

2019-12-02
A
US
Sports
Running
long
35

2019-12-02
A
US
Sports
Running
short
37

2019-12-02
A
US
Arts
null
null
null

2020-12-01
B
US
Sports
Cycle
long
33

2020-12-01
B
US
Sports
Cycle
Short
35

2020-12-01
B
US
Sports
Running
long
28

2020-12-01
B
US
Sports
Running
short
29

2020-12-01
B
US
Arts
null
null
null


SELECT t.yyyymmdd, t.period, t.country, t.cat1, t.cat2, t.cat3, t.score, 
       sq.score AS score_day1 
FROM t
LEFT JOIN
          (
           SELECT yyyymmdd, period, country, cat1, cat2, cat3, score 
           FROM 
               (
                SELECT yyyymmdd, period, country, cat1, cat2, cat3, score, 
                       RANK() OVER(PARTITION BY period ORDER BY yyyymmdd ASC) AS rnk
                FROM t
               ) AS r
           WHERE rnk=1
          ) AS sq
     ON t.period=sq.period 
     AND t.country=sq.country 
     AND t.cat1=sq.cat1
     AND t.cat2=sq.cat2
     AND t.cat3=sq.cat3
ORDER BY t.period, t.yyyymmdd


yyyymmdd
period
country
cat1
cat2
cat3
score
score_day1


2019-12-01
A
US
Sports
Cycle
long
33
33

2019-12-01
A
US
Sports
Cycle
Short
35
35

2019-12-01
A
US
Sports
Running
long
28
28

2019-12-01
A
US
Sports
Running
short
29
29

2019-12-01
A
US
Arts
null
null
null
null

2019-12-02
A
US
Sports
Cycle
long
43
33

2019-12-02
A
US
Sports
Cycle
Short
44
35

2019-12-02
A
US
Sports
Running
long
35
28

2019-12-02
A
US
Sports
Running
short
37
29

2019-12-02
A
US
Arts
null
null
null
null

2020-12-01
B
US
Sports
Cycle
long
33
33

2020-12-01
B
US
Sports
Cycle
Short
35
35

2020-12-01
B
US
Sports
Running
long
28
28

2020-12-01
B
US
Sports
Running
short
29
29

2020-12-01
B
US
Arts
null
null
null
null

yyyymmdd	period	country	cat1	cat2	cat3	score
2019-12-01	A	US	Sports	Cycle	long	33
2019-12-01	A	US	Sports	Cycle	Short	35
2019-12-01	A	US	Sports	Running	long	28
2019-12-01	A	US	Sports	Running	short	29
2019-12-01	A	US	Arts	null	null	null
2019-12-02	A	US	Sports	Cycle	long	43
2019-12-02	A	US	Sports	Cycle	Short	44
2019-12-02	A	US	Sports	Running	long	35
2019-12-02	A	US	Sports	Running	short	37
2019-12-02	A	US	Arts	null	null	null
2020-12-01	B	US	Sports	Cycle	long	33
2020-12-01	B	US	Sports	Cycle	Short	35
2020-12-01	B	US	Sports	Running	long	28
2020-12-01	B	US	Sports	Running	short	29
2020-12-01	B	US	Arts	null	null	null