quote:

---

Originally posted by anantharengan

comments (sno is an identity column)
--------

sno statusid areaid year comments date entityId
1 1 1 2012 abc 12/12/2010 1
2 2 1 2012 def 11/12/2010 1
3 3 1 2012 def 10/12/2010 1
4 1 2 2013 abc 09/12/2010 1
5 2 2 2013 def 08/12/2010 1
6 3 2 2013 def 07/12/2010 1
7 2 1 2013 def 06/12/2010 1
8 3 1 2013 def 05/12/2010 1
9 1 2 2012 abc 04/12/2010 2
10 2 2 2012 def 03/12/2010 2
11 3 2 2012 def 02/12/2010 2
12 1 2 2013 abc 01/12/2010 2
13 2 2 2013 def 30/11/2010 2
14 3 2 2013 def 29/11/2010 2



entity
------
entityId name
1 entity1
2 entity2


status
-------
statusid status-description
1 approved
2 disapproved
3 submitted


My i/p --> areaid (for example areadid=1)

my o/p should be

a largest year from comments table which contains all the entities(2 in this case)
and their last dated statusid should be 1(approved),if it satisfies this condition, then

that year should be the o/p


example 1
--------------
i/p : area id=1
o/p : should be empty


example 2
--------------
i/p : area id=2
o/p : 2013


I am able to get the o/p through my query but I had used table variables and while loops
how to achieve my goal in a most efficient way?

---

SELECT TOP 1 year
FROM table
where areaid = @yourareaidvalue
group by year 
having count(distinct entityid) = (select count(entityid) from entity)
ORDER BY year desc

DECLARE	@Comments TABLE
	(
		sno INT,
		StatusID INT,
		AreaID INT,
		Yr INT,
		Comments VARCHAR(100),
		Dte DATE,
		EntityID INT
	)

SET DATEFORMAT DMY

INSERT	@Comments
VALUES	( 1, 1, 1, 2012, 'abc', '12/12/2010', 1),
	( 2, 2, 1, 2012, 'def', '11/12/2010', 1),
	( 3, 3, 1, 2012, 'def', '10/12/2010', 1),
	( 4, 1, 2, 2013, 'abc', '09/12/2010', 1),
	( 5, 2, 2, 2013, 'def', '08/12/2010', 1),
	( 6, 3, 2, 2013, 'def', '07/12/2010', 1),
	( 7, 2, 1, 2013, 'def', '06/12/2010', 1),
	( 8, 3, 1, 2013, 'def', '05/12/2010', 1),
	( 9, 1, 2, 2012, 'abc', '04/12/2010', 2),
	(10, 2, 2, 2012, 'def', '03/12/2010', 2),
	(11, 3, 2, 2012, 'def', '02/12/2010', 2),
	(12, 1, 2, 2013, 'abc', '01/12/2010', 2),
	(13, 2, 2, 2013, 'def', '30/11/2010', 2),
	(14, 3, 2, 2013, 'def', '29/11/2010', 2)

DECLARE	@Entity TABLE
	(
		EntityID INT,
		Name VARCHAR(100)
	)

INSERT	@Entity
VALUES	(1, 'Entity1'),
	(2, 'Entity2')

DECLARE	@Status TABLE
	(
		StatusID INT,
		[Description] VARCHAR(100)
	)

INSERT	@Status
VALUES	(1, 'approved'),
	(2, 'disapproved'),
	(3, 'submitted')

-- SwePeso
SELECT		AreaID,
		CASE (SELECT COUNT(*) FROM @Entity)
			WHEN COUNT(*) THEN MAX(Yr)
			ELSE NULL
		END AS [Year]
FROM		(
			SELECT	AreaID,
				Dte,
				EntityID,
				StatusID,
				Yr,
				ROW_NUMBER() OVER (PARTITION BY AreaID, EntityID, DATEPART(YEAR, Dte) ORDER BY Dte DESC) AS rn
			FROM	@Comments
		) AS d
WHERE		rn = 1
		AND StatusID = (SELECT StatusID FROM @Status WHERE [Description] = 'Approved')
GROUP BY	AreaID