we only want to see id, who, either before, or CURRENT in the year 2005,

Question

question

thennarasu asked Nov 6, '15

we only want to see id, who, either before, or CURRENT in the year 2005,

I have a table like this


Name	ID	StartDate	EndDate
James 	232	  2005	          2015
James	232	  2001	          2006
Joe	600	  1982	         2005
Lord	608	  2003	        2005
Lord 	608	  2006	        2012
Lord 	608	  1999	        2004
Lee 	122       2001	        2003
Lee 	122	  2002	         2006

Condition is I need the name whose startdate is equal or less than 2005 **ONLY** *AND* Enddate is equal or Greater than 2005 **ONLY** So the output like this Because james and joe have fullfill this condition only Name ID StartDate EndDate James 232 2005 2015 James 232 2001 2006 Joe 600 1982 2005

sql-server-2008 sql-server-2005 sql

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Nov 06, 2015 at 08:54 AM

Seems straightforward enough - what's causing you problems? Is it the operator precedence? If so, try https://msdn.microsoft.com/en-us/library/ms190276.aspx for guidance.

2 ·

anthony.green commented · Nov 06, 2015 at 10:04 AM

Isn't the answer James 232 2005 2015 James 232 2001 2006 Joe 600 1982 2005 Lord 608 2003 2005 Lee 122 2002 2006 SELECT * FROM ..... WHERE StartDate = 2005 Lord and Lee also have a startdate less than or equal to 2005 and also they have a end date greater than or equal to 2005

0 ·

Answer 1 · 2015-11-06T13:54:13Z

Tom Staab answered Nov 6, '15

At first glance, this sounded simple enough, but then I reread your condition. These are the criteria as you wrote them: Condition is I need the name whose startdate is equal or less than 2005 ONLY AND Enddate is equal or Greater than 2005 ONLY This is my translation: I need to list every row for which the person's maximum StartDate is <= 2005 and minimum EndDate >= 2005. Here's the test table I just used to confirm the correct results: CREATE TABLE #test ( Name varchar(50) , Id int , startdate int , enddate int ); INSERT #test (Name, Id, startdate, enddate) VALUES ('James', 232, 2005, 2015) , ('James', 232, 2001, 2006) , ('Joe' , 600, 1982, 2005) , ('Lord' , 608, 2003, 2005) , ('Lord' , 608, 2006, 2012) , ('Lord' , 608, 1999, 2004) , ('Lee' , 122, 2001, 2003) , ('Lee' , 122, 2002, 2006) ; SELECT * FROM #test; Given that, I believe this is what you need: WITH MyCriteria AS ( SELECT Id FROM #test GROUP BY Id HAVING MAX(StartDate) <= 2005 AND MIN(EndDate) >= 2005 ) SELECT t.Name, t.Id, t.StartDate, t.EndDate FROM #test t WHERE EXISTS (SELECT 1 FROM MyCriteria c WHERE c.Id = t.Id) ;

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thennarasu commented · Nov 07, 2015 at 10:16 AM

startdate must be <= 2005 and enddate must be >= 2005 if other then that it should not include as output ex;lee and lord fullfill the condition but they also have other satartdate and enddate so it is not included only james and joe shoud include .

0 ·

Tom Staab ♦ commented · Nov 08, 2015 at 04:45 AM

Right. You only want an individual if all of that person's startdates are = 2005. The min and max functions handle that. I just tested it. I'll add the test table to my answer.

0 ·

question