T-SQL Query

Posted on 2012-08-23
Last Modified: 2012-08-29
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


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
Question by:Ravee123
    1 Comment
    LVL 142

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now