当天所在月如何划分周并按周进行统计？（表达得不是很清楚，进来看看）

Question

不好意思，表格变形了/*Result Table Type :AFirstWeek:2005-04-01~2005-04-07 (7 days)
SecondWeek:2005-04-08~2005-04-14(7 days)
ThirdWeek:2005-04-15~2005-04-21(7 days)
FourthWeek:2005-04-22~2005-04-30(more than 7 days)|FullName |FirstWeek |SecondWeek |ThirdWeek |FourthWeek
|TestScholl_Name1 |1 |0 |0 |4
|TestScholl_Name2 |0 |3 |1 |1
|TestScholl_Name3 |0 |2 |3 |1
*//*Result Table Type :AFirstWeek:2005-04-01~2005-04-02 (less than 7 days)
SecondWeek:2005-04-03~2005-04-09(7 days)
ThirdWeek:2005-04-10~2005-04-16(7 days)
FourthWeek:2005-04-17~2005-04-23(7 days)
FifthWeek:2005-04-23~2005-04-30(7 days)|FullName          |FirstWeek|SecondWeek|ThirdWeek|FourthWeek|FifthWeek
|TestScholl_Name1 |0  |1    |0    |2      |2 |
|TestScholl_Name2 |0  |1    |3    |0      |1 |
|TestScholl_Name3 |0  |0    |3    |2      |1 |*/

Answer 1

根据当天所在月（比如4月25日为4月 month(canceldate)=month(getdate())）第一天到第7天为第一周是第一种查询，这个可能容易一点；
根据每月第一天（比如4月1号为第一周中的第5天）所在周为第一周查询是第而种查询；不知道大家是否明白小生我的意思？
应该根据哪种划分进行查询？查询语句如何写？（考虑到字段的变化可以写成存储过程？）

Answer 2

declare 
@ThisWeekDateStart DATETIME,
@ThisWeekDateEnd   DATETIME
set @ThisWeekDateStart=(select dateadd(dd,datediff(dd,'1900-01-01',getdate()) - datepart(dw,getdate()) + 1,'1900-01-01'))
set @ThisWeekDateEnd=(select dateadd(dd,datediff(dd,'1900-01-01',getdate()) + (7 - datepart(dw,getdate())),'1900-01-01'))select @ThisWeekDateStart,@ThisWeekDateEnd
select fullname,
--...
ThisWeek=sum(case when canceldate  between @ThisWeekDateStart and @ThisWeekDateEnd then 1 else 0  end)
from test_050425_school,test_050425_records
where test_050425_school.id=schoolid and type='A'
group by fullname

Answer 3

第一种：
select s.FullName,
 sum(case when DATEDIFF(dd,'2005-03-31',CancelDate)<=7 then 1 else 0 end) FirstWeek,
 sum(case when DATEDIFF(dd,'2005-03-31',CancelDate) between 8 and 14 then 1 else 0 end) SecondWeek,
 sum(case when DATEDIFF(dd,'2005-03-31',CancelDate) between 15 and 21 then 1 else 0 end) ThirdWeek,
 sum(case when DATEDIFF(dd,'2005-03-31',CancelDate)>21 then 1 else 0 end) FourthWeek
from [TEST_050425_School] s
 inner join [TEST_050425_RECORDS] r on r.SchoolID = s.ID
where r.Type = 'A'
group by s.FullName第二种：
select s.FullName,
 sum(case when DATEPART(dw,'2005-03-31')+DATEDIFF(dd,'2005-03-31',CancelDate)<=7 then 1 else 0 end) FirstWeek,
 sum(case when DATEPART(dw,'2005-03-31')+DATEDIFF(dd,'2005-03-31',CancelDate) between 8 and 14 then 1 else 0 end) SecondWeek,
 sum(case when DATEPART(dw,'2005-03-31')+DATEDIFF(dd,'2005-03-31',CancelDate) between 15 and 21 then 1 else 0 end) ThirdWeek,
 sum(case when DATEPART(dw,'2005-03-31')+DATEDIFF(dd,'2005-03-31',CancelDate) between 22 and 28 then 1 else 0 end) FourthWeek,
 sum(case when DATEPART(dw,'2005-03-31')+DATEDIFF(dd,'2005-03-31',CancelDate)>28 then 1 else 0 end) FifthWeek
from [TEST_050425_School] s
 inner join [TEST_050425_RECORDS] r on r.SchoolID = s.ID
where r.Type = 'A'
group by s.FullName

Answer 4

第一种
select (DATEPART(day, CancelDate - 1) / 7 as CalcelWeek
第二种
select DATEPART(week, CancelDate) as CalcelWeek
有了第几周，然后按周次汇总