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

Table 1 
owner_id	close_date	opp_id
Peter	2014-01-02 00:00:00.000	1
Peter	2014-01-02 00:00:00.000	2
Peter	2014-02-04 00:00:00.000	3
Peter	2014-02-04 00:00:00.000	4
Peter	2014-03-06 00:00:00.000	5
Peter	2014-03-06 00:00:00.000	6
Peter	2014-04-08 00:00:00.000	7
Paul	2014-01-02 00:00:00.000	8
Paul	2014-01-02 00:00:00.000	9
Paul	2014-02-02 00:00:00.000	10
Paul	2014-02-02 00:00:00.000	11
Paul	2014-03-02 00:00:00.000	12
Paul	2014-04-02 00:00:00.000	13
Paul	2014-04-02 00:00:00.000	14
Mary	2014-06-01 00:00:00.000	15


Table 2
opp_id	amtfinanced
1	2000.00
2	12000.00
3	12000.00
4	13000.00
5	14000.00
6	15000.00
7	16000.00
8	12000.00
9	13000.00
10	14000.00
11	15000.00
12	16000.00
13	17000.00
14	18000.00
15	1000000.00
17	2000000.00

Declare @CalendarTable table
	(
	[DateKey] [int] NOT NULL,
	[FullDateAlternateKey] [date] NOT NULL,
	[DayNumberOfWeek] [tinyint] NOT NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NULL,
	[DayNumberOfMonth] [tinyint] NOT NULL,
	[DayNumberOfYear] [smallint] NOT NULL,
	[WeekNumberOfYear] [tinyint] NULL,
	[EnglishMonthName] [nvarchar](10) NULL,
	[MonthNumberOfYear] [tinyint] NOT NULL,
	[CalendarQuarter] [tinyint] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarSemester] [tinyint] NULL
	)

-- Populate the table with a TALLY table approach.
Declare @StartDate datetime 
Declare @EndDate datetime 

Select @StartDate = '20140101',
		@EndDate = '20141231'

;WITH cTally
AS
(
select top 1100 ROW_NUMBER() Over (order by (select null)) - 1 as num
from syscolumns c
cross join syscolumns cc
) 


INSERT INTO @CalendarTable(DateKey, FullDateAlternateKey, DayNumberOfWeek,
	        EnglishDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear,
	        WeekNumberOfYear, EnglishMonthName, MonthNumberOfYear,
	        CalendarQuarter, CalendarYear)
SELECT 	  
	  CONVERT(INT, CONVERT(nvarchar(12), DATEADD(dd,num,@StartDate), 112)),
	  DATEADD(dd,num,@StartDate),
	  DATEPART(dw, DATEADD(dd,num,@StartDate)),
	  DATENAME(dw, DATEADD(dd,num,@StartDate)),
	  DATEPART(dd, DATEADD(dd,num,@StartDate)),
	  DATEPART(dayofyear, DATEADD(dd,num,@StartDate)),
	  DATEPART(week, DATEADD(dd,num,@StartDate)),
	  DATENAME(MONTH, DATEADD(dd,num,@StartDate)),
	  DATEPART(mm, DATEADD(dd,num,@StartDate)),
	  DATEPART(qq, DATEADD(dd,num,@StartDate)),
	  DATEPART(yyyy,  DATEADD(dd,num,@StartDate))
from cTally
Where  DATEADD(dd,num,@StartDate) <= @EndDate

Declare @Table1 table
	(
		owner_id		varchar(10),
		close_date		datetime,
		opp_id			int
	)

INSERT INTO @Table1
(
	owner_id,	
	close_date,
	opp_id		
)

select 'Peter'	, '2014-01-02 00:00:00.000',	1	union all
select 'Peter'	, '2014-01-02 00:00:00.000',	2	union all
select 'Peter'	, '2014-02-04 00:00:00.000',	3	union all
select 'Peter'	, '2014-02-04 00:00:00.000',	4	union all
select 'Peter'	, '2014-03-06 00:00:00.000',	5	union all
select 'Peter'	, '2014-03-06 00:00:00.000',	6	union all
select 'Peter'	, '2014-04-08 00:00:00.000',	7	union all
select 'Paul'	, '2014-01-02 00:00:00.000',	8	union all
select 'Paul'	, '2014-01-02 00:00:00.000',	9	union all
select 'Paul'	, '2014-02-02 00:00:00.000',	10	union all
select 'Paul'	, '2014-02-02 00:00:00.000',	11	union all
select 'Paul'	, '2014-03-02 00:00:00.000',	12	union all
select 'Paul'	, '2014-04-02 00:00:00.000',	13	union all
select 'Paul'	, '2014-04-02 00:00:00.000',	14	union all
select 'Mary'	, '2014-06-01 00:00:00.000',	15

Declare @Table2 table 
	(
	opp_id int,
	amtfinanced float
	)

INSERT INTO @Table2
	(
	opp_id,
	amtfinanced 
	)


select 1	, 2000.00			union all
select 2	, 12000.00			union all
select 3	, 12000.00			union all
select 4	, 13000.00			union all
select 5	, 14000.00			union all
select 6	, 15000.00			union all
select 7	, 16000.00			union all
select 8	, 12000.00			union all
select 9	, 13000.00			union all
select 10	, 14000.00			union all
select 11	, 15000.00			union all
select 12	, 16000.00			union all
select 13	, 17000.00			union all
select 14	, 18000.00			union all
select 15	, 1000000.00		union all
select 17	, 2000000.00

;WITH CTE
AS
(
SELECT DISTINCT T1.owner_id, c.EnglishMonthName, c.CalendarYear, c.MonthNumberOfYear
FROM @CalendarTable C
CROSS JOIN @Table1 T1
)

SELECT C.owner_id, c.EnglishMonthName, c.CalendarYear, isnull(cpg.TOTA_amtfinanced,0) AS amtfinanced
FROM CTE C
LEFT JOIN (
			SELECT t1.owner_id, datepart(mm,T1.close_date) as mon,  datepart(yy,T1.close_date) as yr,  SUM(T2.amtfinanced) AS TOTA_amtfinanced
			FROM @Table1 t1
			INNER JOIN @Table2 t2 on t1.opp_id = t2.opp_id
			GROUP BY t1.owner_id,T1.close_date
		  ) cpg on c.CalendarYear = cpg.yr
				and c.MonthNumberOfYear = cpg.mon
				and C.owner_id = cpg.owner_id

ORDER BY C.owner_id, c.CalendarYear, c.MonthNumberOfYear