Over partition Syntax for consecutive absences not working

• clare thomas

  Newbie

  Points: 5

  More actions

  November 23, 2009 at 9:17 am

  #135416

  Hi,

  I am trying to identify students who have 2 consecutive absences in any class. I have found the answer in Oracle syntax, but cannot replicate it for SQL 2005.

  SELECT DISTINCT student_id, register_id,week_no

  FROM (SELECT student_id, register_id,week_no,

  SUM(abs_check)

  OVER(PARTITION BY register_id, week_no ORDER BY student_id, register_id, week_no ROWS 2 PRECEDING)

  FROM cAbsence

  GROUP BY student_id, register_id,week_no)

  AS X (student_id, register_id,week_no, last_two)

  WHERE last_two = 2;

  please can anyone help?

  My data is like this:

  Student_id Register_id Week_no abs_check

  ADA09001765PERFST1O141

  ALV06020206E1MUS3 O101

  ALV06020206E1MUS3 O121

  ALV06020206E1MUS3 O141

  ARN09001444PERFST1O141

  ARS06019990E1MUS3 O121

  ARS06019990E1MUS3 O131

  AST07021488E1MUS3 O91

  AST07021488E1MUS3 O131

  AST07021488MEC1SWBO91

  AST07021488MEC1SWBO101

  AST07021488MEC1SWBO111

  AST07021488MEC1SWBO121

  AST07021488MEC1SWBO131

  AST07021488MEC1SWBO141

  AST07021488PS3 O91

  AST07021488PS3 O101

  BAI08000474MUS2SP1O101

  BAI08000474MUS2SP1O111

  BAI08000474MUS2SP1O131

  BAI08000474MUS2SP1O151

  BAI08000474MUS2SP1O171

  BAI09001904MUS1SP1O131

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  November 23, 2009 at 9:26 am

  #1083006

  Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass