quote:

---

(if event scheduled on Tuesday(2014-11-04) and repeat count is 4 then it should repeat the 2 row of data)

---

-- *** Test Data in Comsumable Format ***
-- Please provide this in future
CREATE TABLE #t
(
	EventId int NOT NULL
	,EventName varchar(20) NOT NULL
	,EventStartDate datetime NOT NULL
	,EventEndDate datetime NOT NULL
	,RepeatCount int NOT NULL
	,StartTime varchar(10) NOT NULL
	,EndTime varchar(10) NOT NULL
);
INSERT INTO #t
SELECT 1, 'Event1','20141104','20141125', 4, '4:30 PM', '6:30 PM'
UNION ALL SELECT 2, 'Event2','20141105','20141112', 2, '3.00 AM', '4.00 AM'
UNION ALL SELECT 3, 'Event3','20141101','20141115', 3, '1.00 PM', '2.00 PM'
UNION ALL SELECT 4, 'Event4','20141030','20141106', 2, '5.30 PM', '6.30 PM'
UNION ALL SELECT 5, 'Event5','20141016','20141116', 0, '5.30 PM', '6.30 PM';
-- *** End Test Data ***

DECLARE @Now datetime;
SET @Now = '20141104 15:40';

WITH N1(N)
AS 
(
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
	UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, N2(N) AS (SELECT 1 FROM N1 A, N1 B)
, N(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM N2)
, AllEvents
AS
(
	SELECT EventId, EventName
		,DATEADD(week, N.N, EventStartDate) + REPLACE(StartTime, '.', ':') AS EventStart
		,DATEADD(week, N.N, EventStartDate) + REPLACE(EndTime, '.', ':') AS EventEnd
	FROM #t T
		JOIN N
			-- RepeatCount does not make sense for Event5
			--ON T.RepeatCount >= N.N
			ON DATEDIFF(wk, EventStartDate, EventEndDate) + 1 >= N.N
)
SELECT 
	ROW_NUMBER() OVER (ORDER BY EventStart) AS SNo
	,EventId, EventName
	,DATEADD(day, DATEDIFF(day, 0, EventStart), 0) As EventDate
FROM AllEvents
WHERE EventStart <= DATEADD(week, 2, @Now)
	AND EventEnd >= @Now;

quote:

---

Originally posted by ram1986

Hi Khtan,

Only 2 weeks events should be displayed.So for repeat count 4 , 2 rows..

---

quote:

---

Originally posted by khtan

quote:

---

Originally posted by ram1986

Hi Khtan,

Only 2 weeks events should be displayed.So for repeat count 4 , 2 rows..

---

You mean repeat count = 4 means 2 weeks ? and hence 2 rows ?

How do you get from 4 to 2 ? 4 / 2 = 2 ?

---

KH
[spoiler]Time is always against us[/spoiler]

---