SQL/LastNotNullByWeek.sql at master · cjfaulkner/SQL · GitHub

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
DECLARE @testtemp TABLE (ISO3 char(3), WeekNo int, Val1 int, Val2 int)

INSERT INTO @testtemp (ISO3, WeekNo, Val1, Val2)
VALUES
('CHN', 1, 1000, 1000),
('CHN', 2, NULL, 2000),
('CHN', 3, 2000, NULL),
('CHN', 4, NULL, NULL),
('HKG', 1, 100,  150),
('HKG', 2, 200,  NULL),
('HKG', 3, NULL, 250),
('HKG', 4, 400,  350),
('MAC', 1, 10,    15),
('MAC', 2, 20,    25),
('MAC', 3, 30,    35),
('MAC', 4, 40,    45)


SELECT * FROM @testtemp
ORDER BY ISO3, WeekNo

;WITH CTE_LastNotNull AS
(
SELECT
	tt.ISO3,
	tt.WeekNo,
	MAX(nn1.WeekNo) AS Val1NotNullWeekNo,
	MAX(nn2.WeekNo) AS Val2NotNullWeekNo
FROM
	@testtemp tt
LEFT JOIN
	@testtemp nn1
ON
	tt.ISO3 = nn1.ISO3
AND
	nn1.WeekNo <= tt.WeekNo
AND
	nn1.Val1 IS NOT NULL
LEFT JOIN
	@testtemp nn2
ON
	tt.ISO3 = nn2.ISO3
AND
	nn2.WeekNo <= tt.WeekNo
AND
	nn2.Val2 IS NOT NULL
GROUP BY tt.ISO3, tt.WeekNo
)
SELECT
	lnn.ISO3,
	lnn.WeekNo,
	v1.Val1,
	v2.Val2
--	SUM(tt.Val) AS Total
FROM
	CTE_LastNotNull lnn
INNER JOIN
	@testtemp v1
ON
	v1.ISO3 = lnn.ISO3
AND
	v1.WeekNo  = lnn.Val1NotNullWeekNo
INNER JOIN
	@testtemp v2
ON
	v2.ISO3 = lnn.ISO3
AND
	v2.WeekNo  = lnn.Val2NotNullWeekNo
--GROUP BY
--	tt.ISO3,
--	tt.WeekNo
ORDER BY ISO3, WeekNo