occurence value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

occurence value

Author

Topic

karthikeyan2004k
Starting Member

5 Posts

Posted - 2012-05-07 : 07:47:44

Hi i have a reminder task in my application

in this if the reminder type is weekly

means i have given a option to get occurences (end after ) value from the user

so if the occurence (end after (i.e total occurence)) value is 10 means it will calculate the date from event date

for example if the start of event date (mm/dd/yy) is 5/7/2012 and the reminder will occur in sunday,monday,tuesday,saturday of the week

and the reminder will end in(mm/dd/yy) 6/5/2012

Note:- just like M.s. outlook Calender concept

I hope you have understood my task

Pls tell me the idea to create query for this task
Thanks in advance

karthikeyan.r

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-07 : 08:22:23

First create a calendar table or numbers table if you don't already have one in your database. You can create one like this:

CREATE TABLE #N(n INT NOT NULL PRIMARY KEY CLUSTERED);
;WITH cte AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n < 100)
INSERT INTO #N SELECT n FROM cte;

Now query against the numbers table to get the days on which the event has to occur:

DECLARE @IntervalDays INT; SET @IntervalDays = 2;
DECLARE @Occurrences INT; SET @Occurrences = 5;
DECLARE @startDate DATETIME; SET @startDate = '20120507';

SELECT
	DATEADD(dd,(n-1)*@IntervalDays,@startDate)
FROM
	#N
WHERE
	n <= @Occurrences

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources