question

thennarasu avatar image
thennarasu asked

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-2008sql-server-2005sql
2 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 2 ·
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 Likes 0 ·

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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