asked on

Need Query

Here is one scenario.

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

INSERT #resp(id,Attendance,Yearattendance) values (18, 15, 2009)
INSERT #resp(id,Attendance,Yearattendance) values (18, 0, 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)

Like that millions data are there in a table for different ids.

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. In the above data example for id=18

Id      Attendance      Yearattendance
18      15            2009
18      0            2011
18      0            2010
18      20            2008
18      0            2012

Now I want to display like

Id      Attendance
18      15.

Here current year is 2012. Going back 3 years…. 2011,2010,2009

Compare these 3 years. Which has data. In the above example 2009 only have data. If we have data in more than two or three years, we have to take recent years to 2012 and take that Attendance data and make it to display.

Kindly write a query .

ASKER CERTIFIED SOLUTION

Jesus Rodriguez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ravee123

ASKER

Simple and Excellent Solution.

Ravee123

ASKER

Hi

The above query is not working

For example: I am changing the value as (18, 10, 2011). Instead of 0 now the new value is 10. It should be display only (18, 10, 2011) according to your query . But it displays for 2009 also. Can u correct the query pls

Jesus Rodriguez

LEt me check it right now. eASY FIX

Add top 1 like this

SELECT TOP 1 ID,ATTENDANCE,YEARATTENDANCE
FROM #RESP
WHERE ATTENDANCE>0
AND YEARATTENDANCE>=(SELECT MAX(YEARATTENDANCE)-3 FROM #RESP)
ORDER BY YEARATTENDANCE DESC

Ravee123

ASKER

Hi Fiend

Once again problem.

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, 10, 2009)
INSERT #resp(id,Attendance,Yearattendance) values (19, 50, 2010)
INSERT #resp(id,Attendance,Yearattendance) values (19, 30, 2011)
INSERT #resp(id,Attendance,Yearattendance) values (19, 0, 2012)

SELECT * FROM #resp

By using query which one u provided

SELECT TOP 1 ID,ATTENDANCE,YEARATTENDANCE
FROM #RESP
WHERE ATTENDANCE>0
AND YEARATTENDANCE>=(SELECT MAX(YEARATTENDANCE)-3 FROM #RESP)
ORDER BY YEARATTENDANCE DESC

it returns only

ID      ATTENDANCE      YEARATTENDANCE
18      10        2011

No displayment for 19.

For 19,

id      Attendance      Yearattendance
19      30        2011

Your qeury is expected to be displayed both

ID      ATTENDANCE      YEARATTENDANCE

18      10        2011
19      30        2011

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 has the recent year which has values