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

Rate

1 (1)

Share

Share

Rate

1 (1)