• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

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
0
Ravee123
Asked:
Ravee123
1 Solution
 
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now