Hi All, Can anyone help me in this, as it is so urgent ..My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken.Given below the scenario

ID          StartDate

1            2014-01-01

1            2014-02-01

1            2014-05-01-------After Gap Restarted

1            2014-06-01

1            2014-09-01---------After last gap restarted

1            2014-10-01

1            2014-11-01

2            2014-01-01

2           2014-02-01

2            2014-03-01

2            2014-04-01

2            2014-05-01

2            2014-06-01

2            2014-07-01

For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output

id             Startdate

1             2014-10-01

2             2014-01-01

Expecting your help...Thanks in advance

If you're using SQL Server 2012 this will work for you...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
	ID INT,
	StartDate DATE 
	)
INSERT #temp (ID, StartDate) VALUES
(1,'2014-01-01'),
(1,'2014-02-01'),
(1,'2014-05-01'),
(1,'2014-06-01'),
(1,'2014-09-01'),
(1,'2014-10-01'),
(1,'2014-11-01'),
(2,'2014-01-01'),
(2,'2014-02-01'),
(2,'2014-03-01'),
(2,'2014-04-01'),
(2,'2014-05-01'),
(2,'2014-06-01'),
(2,'2014-07-01')

-- SQL 2012 and later --
;WITH gg AS (
	SELECT
		*,
		COALESCE(DATEDIFF(mm, LAG(t.StartDate, 1) OVER (PARTITION BY t.ID ORDER BY t.StartDate), t.StartDate), 2) AS GetGap
	FROM #temp t
), did AS (
	SELECT DISTINCT t.ID FROM #Temp t
)
	SELECT
		did.ID,
		x.StartDate
	FROM 
		did 
		CROSS APPLY (
			SELECT TOP 1
			gg.StartDate
			FROM gg
			WHERE did.ID = gg.ID
			AND gg.GetGap > 1
			ORDER BY gg.StartDate DESC
			) x

If you're on an earlier version than 2012, let me know. It's an easy rewrite but the final code isn't as efficient.