tsql - Week to Date data filter -

given following data:

studentabsences([studentid], [dateabsent], [reasoncode])

studentdetails([studentid],[givennames],[surname],[yearlevel],[house])

problem: trying generate report client want see top 3+ students have been absent during particular period. period can anywhere last week last month last year. current report giving them:

student's name (concatenation of givennames , surname) unexplained (number of unexplained absences during particular period) year date (the count of different types of absence reasons ytd) year level (the student's year level) 

trouble want "week date" column unexplained absences. mean want see number of absences each student starting monday of particular week.

any suggestions?

here first take. top 3 students missing week.

declare @startdate datetime,         @enddate datetime  select @startdate=dateadd(ww, datediff(ww,0,getdate()), 0), @enddate=getdate()  select d.* studentdetails d inner join  (select top 3 studentid studentabsences dateabsent between @startdate , @enddate group studentid, convert (nvarchar(10),dateabsent, 102) order  count(convert (nvarchar(10),dateabsent, 102)) desc) on a.studentid = d.studentid