I have T1 which is formatted as
STUDENT SCORE DATE
1 6 2022-02-01
1 0 2022-03-12
1 5 2022-04-30
1 1 2022-04-30
1 1 2022-05-14
1 1 2022-05-19
1 8 2022-05-26
2 9 2022-01-02
2 10 2022-04-11
2 2 2022-04-12
2 0 2022-04-17
2 7 2022-05-08
2 4 2022-05-12
3 10 2022-01-09
3 2 2022-02-11
3 6 2022-03-16
3 3 2022-03-18
3 2 2022-04-02
3 9 2022-04-27
4 4 2022-02-24
4 0 2022-02-26
4 9 2022-02-28
4 2 2022-03-27
4 8 2022-04-02
4 4 2022-04-14
5 3 2022-01-28
5 5 2022-02-12
5 6 2022-02-18
5 0 2022-02-21
5 4 2022-04-05
XX 0.711094564 2022-02-28
XX 0.60584994 2022-03-31
XX 0.087965016 2022-04-30
YY 0.497937992 2022-02-28
YY 0.727796963 2022-03-31
YY 0.974471085 2022-04-30
YY 0.780187398 2022-05-30
First I wish to extract the UNIQUE values of DATE when STUDENT equals to XX and YY. There are some duplicates. These are the trimester ending dates.
Then I wish to SUM up the SCORE values for each STUDENT across the date ranges from Step 1:
And together I wish to generate this table T2
STUDENT SCORE DATE.START DATE.END
1 6 2022-01-01 2022-02-28
1 0 2022-03-01 2022-03-31
1 6 2022-04-01 2022-04-30
1 10 2022-05-01 2022-05-30
2 9 2022-01-01 2022-02-28
2 NA 2022-03-01 2022-03-31
2 12 2022-04-01 2022-04-30
2 11 2022-05-01 2022-05-30
3 12 2022-01-01 2022-02-28
3 9 2022-03-01 2022-03-31
3 11 2022-04-01 2022-04-30
3 NA 2022-05-01 2022-05-30
4 13 2022-01-01 2022-02-28
4 2 2022-03-01 2022-03-31
4 12 2022-04-01 2022-04-30
4 NA 2022-05-01 2022-05-30
5 14 2022-01-01 2022-02-28
5 NA 2022-03-01 2022-03-31
5 4 2022-04-01 2022-04-30
5 NA 2022-05-01 2022-05-30
And I try this,
WITH T2 AS(
SELECT DISTINCT(DATE),
COALESCE(LEAD(DATE) OVER(ORDER BY DATE) AS DATE.START,
ROW_NUMBER() OVER (ORDER BY DATE) AS DATE.END
FROM T1 WHERE (STUDENT = 'XX' OR STUDENT = 'YY')
)
SELECT STUDENT, SUM(SCORE), DATE.START, DATE.END
FROM T1 JOIN T2 ON T1.DATE.START BETWEEN DATE.START AND DATE.END
4
Answers
Your date ranges are just month boundaries, so you can use ordinary
GROUP BY
In this one we’re looking at score by month so we don’t really need the bottom half of the information. In fact, it makes it better not to have it cause then we won’t be able to save
student
andscore
as int.Fiddle
Here we create a calendar divided to trimesters and join it with our table matching each date to the correct trimester in the calendar.
Fiddle
This answer is EXACTLY what the question was asking for, including the hacks to accommodate for the ill-posed specifications. Here it goes…
In case anyone wants to play with the query, here goes a link to a Fiddle page.