Using the sample code I created before you can also get days in/days not in using this:

DECLARE @startdate bigint=20121101,

@endDate bigint=20121130;

;WITH

notthere AS

(SELECTStudentID, COUNT(*) AS DaysAbs

FROM #x

WHERE DateID>=@startdate AND DateID<=@endDate

AND attendaceind=0

GROUP BY Studentid

),

wasthere AS

(SELECTStudentID, COUNT(*) AS DaysIn

FROM #x

WHERE DateID>=@startdate AND DateID<=@endDate

AND attendaceind=1

GROUP BY Studentid

)

SELECT nt.StudentID, @startdate AS startdate, @endDate AS endDate, nt.DaysAbs, wt.DaysIn

FROM notthere nt

OUTER APPLY wasthere wt

I did this query for 11/2012 since there was a better mix of data. This will get you:

StudentID startdate endDate DaysAbs DaysIn

----------- -------------------- -------------------- ----------- -----------

1234 20121101 20121130 13 4