SQL Server Stored Procedure that counts the number of weeks

-- Used to count the number list of Sunday to Saturday within the specified date range
If exists (SELECT * FROM dbo. sysobjects WHERE id = OBJECT_ID (n' [dbo]. [usp_GetWeekDayCount] ') and objectproperty (id, n' IsProcedure') = 1)
Drop proc [dbo]. [usp_GetWeekDayCount]
GO

Create proc [dbo]. [usp_GetWeekDayCount]
@ BeginDate DATETIME, @ EndDate DATETIME
AS
Set nocount on;

DECLARE @ WeekDay TABLE
(
[Index] tinyint not null primary key,
[Name] NVARCHAR (20) not null,
[Count] INT
);

DECLARE @ DateFirst DATETIME, @ DateLast DATETIME;
SELECT @ DateFirst = DATEADD (week, DATEDIFF (week, 0, GETDATE (), 0)
, @ DateLast = DATEADD (week, DATEDIFF (week, 0, GETDATE () + 1, 0 );

WHILE @ DateFirst <@ DateLast
BEGIN
Insert into @ WeekDay ([Index], [Name], [Count])
VALUES (DATEPART (weekday, @ DateFirst), DATENAME (weekday, @ DateFirst), 0 );

SET @ DateFirst = DATEADD (day, 1, @ DateFirst );
END;

SELECT @ DateFirst = DATEADD (week, DATEDIFF (week, 0, @ BeginDate) + 1, 0)
, @ DateLast = DATEADD (week, DATEDIFF (week, 0, @ EndDate), 0 );
IF @ DateLast <@ DateFirst
UPDATE @ WeekDay
SET [Count] = [Count] + 1
WHERE [Index]> = DATEPART (weekday, @ BeginDate)
AND [Index] <= DATEPART (weekday, @ EndDate );
ELSE
BEGIN
UPDATE @ WeekDay
SET [Count] = [Count] + DATEDIFF (day, @ DateFirst, @ DateLast)/7;

UPDATE @ WeekDay
SET [Count] = [Count] + 1
WHERE [Index]> = DATEPART (weekday, @ BeginDate );

UPDATE @ WeekDay
SET [Count] = [Count] + 1
WHERE [Index] <= DATEPART (weekday, @ EndDate );
END;

-- Unified with. NET weekly index (0-6)
UPDATE @ WeekDay
SET [Index] = [Index]-1;

SELECT *
FROM @ WeekDay;

Set nocount off;
GO

 

 

 

 

Call example:

 

 

EXEC usp_GetWeekDayCount '2017-04-01 ', '2017-04-30'
Result:
Index Name Count
------------------------------------
0 Sunday 5
1 Monday 5
2 Tuesday 4
3 Wednesday 4
4 Thursday 4
5 Friday 4
6 Saturday 4