 # Return the Count of Weekday in a period

,

Two Method for Return the Count of Weekday between two date!

```--Two Method for Return the Count of Weekday between two date!

declare @b datetime,@e datetime
set @b = '2004-10-03'
set @e = '2004-11-04'

set datefirst 3

--playyuer@Microshaoft.com Invent
select
@b as BeginDate
,@e as EndDate
,datediff(week,@b,@e) + 1
--+ case when (@@datefirst + datepart(weekday,@b)) % 7 = 1 then 1 else 0 end
- case when (@@datefirst + datepart(weekday,@e)) % 7 = 1 then 1 else 0 end as CountOfWeeks
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 2 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 2 then 0 else 1 end as CountOfMonday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 3 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 3 then 0 else 1 end as CountOfTuesday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 4 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 4 then 0 else 1 end as CountOfWednesday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 5 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 5 then 0 else 1 end as CountOfThursday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 6 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 6 then 0 else 1 end as CountOfFriday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 7 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 7 then 0 else 1 end as CountOfSaturday
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 1 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 1 then 0 else 1 end as CountOfSunday

--zjcxc Invent
select
@b as BeginDate
,@e as EndDate
,CountOfWeeks
,CountOfMonday=case a
when -1 then case when 1 between b and c then 1 else 0 end
when  0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,CountOfTuesday=case a
when -1 then case when 2 between b and c then 1 else 0 end
when  0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,CountOfWednesday=case a
when -1 then case when 3 between b and c then 1 else 0 end
when  0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,CountOfThursday=case a
when -1 then case when 4 between b and c then 1 else 0 end
when  0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,CountOfFriday=case a
when -1 then case when 5 between b and c then 1 else 0 end
when  0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,CountOfSaturday=case a
when -1 then case when 6 between b and c then 1 else 0 end
when  0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,CountOfSunday=case a
when -1 then case when 0 between b and c then 1 else 0 end
when  0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select CountOfWeeks=case when @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
,a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end) T```

1 (1)

1 (1)