T-SQL Query

Here is the real scenerio

CREATE TABLE #resp (id int, Attendance int, Yearattendance  int)

INSERT #resp(id,Attendance,Yearattendance) values (18, 15, 2009)
INSERT #resp(id,Attendance,Yearattendance) values (18, 10,   2011)
INSERT #resp(id,Attendance,Yearattendance) values (18, 0,   2010)
INSERT #resp(id,Attendance,Yearattendance) values (18, 20,  2008)
INSERT #resp(id,Attendance,Yearattendance) values (18, 0,    2012)

INSERT #resp(id,Attendance,Yearattendance) values (19, 20,  2008)
INSERT #resp(id,Attendance,Yearattendance) values (19, 5,  2009)
INSERT #resp(id,Attendance,Yearattendance) values (19, 0,  2010)
INSERT #resp(id,Attendance,Yearattendance) values (19, 0,  2011)
INSERT #resp(id,Attendance,Yearattendance) values (19, 0,   2012)

I want to make display most recent value from current year (2012) to go back 3 years until we find a value.

Some cases there is zero Yearattendance.  

 Query is expected to be displayed under below

ID      ATTENDANCE      YEARATTENDANCE

18      10                        2011
19      5                        2009

Similarly millions of records are there.

Query logic is:

- Take Current year from system

- List out 3 years data

- Compare these 3 years and display only the recent years which has values. As per the above , 2011 for ID 18 and 2009 for ID 19 has the recent year which has values

-      Display ATTENDANCE and YEARATTENDANCE for each  ID.


Kindly provide me right query
Ravee123Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do, or be at least a starter:
select id, attendance, yearAttendance
  from (
     select id, attendance, yearAttendance
          , row_number() over (partition by id order by yearAttendance desc) rn
      from yourtable
       where attendance <> 0
         and YearAttendance >= datepart(year, getdate()) - 3
         and YearAttendance <= datepart(year, getdate()) 
  ) sq
where sq.rn = 1

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.