query for count

• sqlfriend

  SSC Guru

  Points: 52769

  More actions

  October 21, 2016 at 3:56 pm

  #318294

  I have an attendance table with each student has each absent date in the table.

  How can I make a query that in any rollover 30 days back from a beginning date to now for any student has more than 7 absence days-Find the student and the first date that met the threshold?

  Thanks,

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  October 21, 2016 at 5:55 pm

  #1908137

  Something like the following?

  WITH absences AS (

  SELECT *, COUNT(absence_dt) OVER( PARTITION BY student_id ORDER BY attendence_dt ROWS UNBOUNDED PRECEDING) AS absence_cnt

  FROM attendence

  WHERE attendence_dt >= DATEADD(DAY, -30, @begin_dt)

  )

  SELECT *

  FROM absences

  WHERE absence_cnt = 7

  Drew

  Edit: Added ROWS UNBOUNDED PRECEDING

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA