Need help with the sql code - rolling 12 months count

Transact-SQL
1

Hi all,
We are having a standard Period table and Hours table. I have created the below dummy data and inserted them into @Period table and @Hours table.

I would like to get the data for the last 12 months (i.e as we are now in 202410, I would like to get data from 202310 to 202409) and as below and this has to be rolling every month:

output
output935×355 6.19 KB

Can you please help me to know how to achieve this output ?

Dummy data below -

Declare @Period table (period int, StartDate datetime, EndDate datetime)
Insert @Period
Select 202301,'2022-07-01','2022-07-31' UNION
Select 202302,'2022-08-01','2022-08-31' UNION
Select 202303,'2022-09-01','2022-09-30' UNION
Select 202304,'2022-10-01','2022-10-31' UNION
Select 202305,'2022-11-01','2022-11-30' UNION
Select 202306,'2022-12-01','2022-12-31' UNION
Select 202307,'2023-01-01','2023-01-31' UNION
Select 202308,'2023-02-01','2023-02-28' UNION
Select 202309,'2023-03-01','2023-03-31' UNION
Select 202310,'2023-04-01','2023-04-30' UNION
Select 202311,'2023-05-01','2023-05-31' UNION
Select 202312,'2023-03-01','2023-06-30' UNION
Select 202401,'2023-07-01','2023-07-31' UNION
Select 202402,'2023-08-01','2023-08-31' UNION
Select 202403,'2023-09-01','2023-09-30' UNION
Select 202404,'2023-10-01','2023-10-31' UNION
Select 202405,'2023-11-01','2023-11-30' UNION
Select 202406,'2023-12-01','2023-12-31' UNION
Select 202407,'2024-01-01','2024-01-31' UNION
Select 202408,'2024-02-01','2024-02-29' UNION
Select 202409,'2024-03-01','2024-03-31' UNION
Select 202410,'2024-04-01','2024-04-30' UNION
Select 202411,'2024-05-01','2024-05-31' UNION
Select 202412,'2024-03-01','2024-06-30'

Select * from @Period

Declare @Hours table (period int, EmployeeCode varchar(10), WorkedHours decimal(5,2))
Insert into @Hours
Select 202301,1000,1.2 UNION
Select 202301,1010,2.6 UNION
Select 202302,1010,1.8 UNION
Select 202302,1020,0.8 UNION
Select 202302,1030,4.9 UNION
Select 202303,1000,4.0 UNION
Select 202303,1030,3.7 UNION
Select 202304,1010,2.6 UNION
Select 202304,1020,0.7 UNION
Select 202304,1040,1.9 UNION
Select 202304,1050,2.6 UNION
Select 202305,1020,4.9 UNION
Select 202305,1050,3.8 UNION
Select 202306,1000,0.6 UNION
Select 202306,1010,0.4 UNION
Select 202306,1030,0.2 UNION
Select 202307,1020,0.3 UNION
Select 202307,1040,1.2 UNION
Select 202308,1030,0.1 UNION
Select 202308,1050,0.3 UNION
Select 202309,1010,0.4 UNION
Select 202309,1060,0.2 UNION
Select 202310,1000,0.4 UNION
Select 202310,1030,0.1 UNION
Select 202310,1060,0.1 UNION
Select 202311,1060,0.2 UNION
Select 202311,1070,0.2 UNION
Select 202312,1040,2.1 UNION
Select 202312,1080,0.4 UNION
Select 202401,1020,0.8 UNION
Select 202401,1070,0.1 UNION
Select 202402,1090,0.5 UNION
Select 202402,1020,1.2 UNION
Select 202403,1060,0.3 UNION
Select 202403,1080,0.2 UNION
Select 202404,1090,0.1 UNION
Select 202404,1100,0.3 UNION
Select 202405,1090,1.2 UNION
Select 202405,1100,0.4 UNION
Select 202406,1070,0.4 UNION
Select 202406,1080,0.1 UNION
Select 202407,1110,1.2 UNION
Select 202407,1120,0.9 UNION
Select 202408,1000,0.8 UNION
Select 202408,1110,0.8 UNION
Select 202409,1090,0.6 UNION
Select 202409,1060,0.1

Select * from @Hours

2

Maybe:

SELECT P.[Period], COUNT(X.EmployeeCode) AS EmpCount12
FROM @Period P
	OUTER APPLY
	(
		SELECT H.EmployeeCode
		FROM @Hours H
		WHERE H.[Period] >= (P.[Period] / 100 -1) * 100 + P.[Period] % 100
			AND H.[Period] < P.[Period]
		GROUP BY H.EmployeeCode
		HAVING SUM(WorkedHours) >= 1
	) X
GROUP BY P.[Period]
ORDER BY [Period];
3

That worked as expected Ifor. Thanks a lot.