How do I loop through a date range and count only the weekdays?

Question

question

cackleg asked Oct 30, '18

How do I loop through a date range and count only the weekdays?

I tested with the following code which wasn't successful:

while (@datetest1 < @datetest2) begin if datepart(d,@datetest1) = 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 2 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 3 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 4 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 5 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 6 set @counter = @counter + 1 set @datetest1 = dateadd(d, 1, @datetest1) if datepart(d,@datetest1) = 7 set @datetest1 = dateadd(d, 1, @datetest1) end

count date loop

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-10-30T15:23:51Z

Jon Crawford answered Oct 30, '18

Best answer is to build a date table that gives you a column that increments for "business days" such that Friday, Saturday and Sunday all have the same value, and it only increments on the next Monday. Then you can also add in the holidays to that table, so it doesn't increment when there is a holiday present.

But, also, you're going about this wrong. Ignoring holidays, we know that there are five weekdays in each week, so first count the number of weeks, multiply by five. Then you have to figure out whether your start and end dates are on the same day of the week or not, fix that. You end up with something like this:

DECLARE @startdate date, @enddate date;

SET @startdate = '1/1/2018';

SET @enddate = '1/31/2018';


SELECT datediff(ww,@startdate,@enddate) as weeks,datepart(dw,@startdate) as startdateWeekday,datepart(dw,@enddate) AS enddateWeekday;

SELECT datepart(dw,@startdate) - datepart(dw,@enddate) as offset;

SELECT (

		datediff(ww,@startdate,@enddate)

		*5

		)--weekdays in the number of weeks between the dates

		- 

		(--subtract the offset between the days of the week

			datepart(dw,@startdate) - datepart(dw,@enddate)

		);

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question