Database

WITH cteStartingPoints As
(
    -- Find the rows with no row ending on the previous day:
    SELECT
        A.EmpCode,
        A.StartDate,
        ROW_NUMBER() OVER (ORDER BY A.StartDate) As RN
    FROM
        YourTable As A
    WHERE
        Not Exists
        (
            SELECT 1
            FROM YourTable As B
            WHERE B.EmpCode = A.EmpCode
            And B.EndDate = DateAdd(day, -1, A.StartDate)
        )
),
cteEndingPoints As
(
    -- Find the rows with no row starting on the next day:
    SELECT
        A.EmpCode,
        A.EndDate,
        ROW_NUMBER() OVER (ORDER BY A.StartDate) As RN
    FROM
        YourTable As A
    WHERE
        Not Exists
        (
            SELECT 1
            FROM YourTable As B
            WHERE B.EmpCode = A.EmpCode
            And B.StartDate = DateAdd(day, 1, A.EndDate)
        )
)
SELECT
    S.EmpCode,
    S.StartDate,
    E.EndDate
FROM
    cteStartingPoints As S
    INNER JOIN cteEndingPoints As E
    ON E.EmpCode = S.EmpCode
    And E.RN = S.RN
;

Richard Deeming wrote:

but the obvious solution would be

Declare @tableA table ( startdate datetime, Enddate datetime, ID int)


insert into @tableA
select '2014-01-01', '2014-01-31', 12345 ID	union all
select '2014-02-01', '2014-02-28', 12345	union all
select '2014-03-03', '2014-03-25', 12345	union all
select '2014-04-01', '2014-04-30', 12345	union all
select '2014-05-01', '2014-05-31', 12345	union all
select '2014-07-01', '2014-07-31', 12345



select ID, min(cdate) as startdate, max(cdate) as enddate
from (
select startdate as cdate, MONTH(startdate) as mon, ID
from @tableA
union all
select enddate as cdate, Month(enddate) as mon, ID
from @tableA
) Z

Group by ID, mon

sathish48 wrote:

what are the necessary steps to be followed to face the long running queries

SELECT Person,
		S.Mth,
		s.Yr,
		s.Amount
	FROM SalesPersonTable P
		LEFT JOIN (SELECT SalesPersonID,
									DATEPART(MONTH, SalseDate) Mth,
									DATEPART(YEAR, salesDate) Yr,
									SUM(amount) Amount
								FROM Sales
								WHERE DATEPART(YEAR, salesDate) = 2014
								GROUP BY SalesPersonID,
									DATEPART(MONTH, SalseDate),
									DATEPART(YEAR, salesDate)) S ON S.SalesPersonID = P.SalesPersonID