?
Solved

Need Query

Posted on 2012-08-23
5
Medium Priority
?
757 Views
Last Modified: 2012-08-23
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 .
0
Comment
Question by:Ravee123
  • 3
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 2000 total points
ID: 38326602
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, 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)

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

Author Closing Comment

by:Ravee123
ID: 38326773
Simple and Excellent Solution.
0
 

Author Comment

by:Ravee123
ID: 38327081
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
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38327116
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
0
 

Author Comment

by:Ravee123
ID: 38327509
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 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