是否有一种方法来计算sql中每一天的数量

Question

declare @date1 as date 2021-02-01
declare @date1 as date 2021-02-20

这里我想获得给定日期之间发生的每一天的天数。结果应该显示每天发生的天数

Monday 3
Tuesday 3
Wednesday 3
Thursday 3
Friday 3
Saturday 3
Sunday 2

Answer 1

我试过了

declare @date1 as date = '2021-02-01'
declare @date2 as date= '2021-02-23'
drop table #days
create table #days
(
daynames varchar(50)
)
while (@date1<=@date2)
begin
insert into #days
values(datename(dw,@date1));
set @date1=dateadd(d,1,@date1)
end
select tb1.daynames,max(tb1.count) as 'visit count' from  (select  
daynames,ROW_NUMBER() over(partition by daynames order by daynames) as count
from #days) as tb1
group by tb1.daynames

Answer 2

试试这个，

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '1/1/2021'
SET @EndDate = '1/31/2021'
select 
datediff(dd, -7, @EndDate)/7-datediff(dd, -6, @StartDate)/7 AS MON,
datediff(dd, -6, @EndDate)/7-datediff(dd, -5, @StartDate)/7 AS TUE,
datediff(dd, -5, @EndDate)/7-datediff(dd, -4, @StartDate)/7 AS WED,
datediff(dd, -4, @EndDate)/7-datediff(dd, -3, @StartDate)/7 AS THU,
datediff(dd, -3, @EndDate)/7-datediff(dd, -2, @StartDate)/7 AS FRI,
datediff(dd, -2, @EndDate)/7-datediff(dd, -1, @StartDate)/7 AS SAT,
datediff(dd, -1, @EndDate)/7-datediff(dd, 0, @StartDate)/7   AS SUN