querying for recurring calendar events

I'd like some help / ideas on how to go about the following
Calendar events will be stored in the db
There will be a variety of recurrence types such as:
once, annually, every nth [day or week or month]
I'm querying once for a month and adding the events via javascript

lets say the current month is january 2016 for example
and lets say i have an event which is to display every 2 months on the 10th and january is it's month to display
how would I go about querying for this?
right now i have 2 columns that pertain to this - recurUnit (which can be day, week, or month) and recurEvery (which is a number)
my intent would be to display every 2 months if recurUnit = month and recurEvery = 2

thanks
LVL 25
dgrafxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
--define start and end limits
Declare @start int, @end int
Select @start=1, @end=10
 
;With NumberSequence( Number ) as
(
    Select @start as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @end
), Cal(EventName, RecurUnit, RecurEvery, StartDate) as 
(
	select 'Bi-Monthly Magazine', 'month', 2, cast('01-Jan-2015' as datetime)
	union select 'My BirthDay', 'year', 1, cast('01-August-2000' as datetime)
)
select 
	Cal.*, 
	NumberSequence.Number, 
	CASE Cal.RecurUnit
		WHEN 'month' THEN DATEADD(month, RecurEvery * NumberSequence.Number, StartDate)
		WHEN 'year' THEN DATEADD(year, RecurEvery * NumberSequence.Number, StartDate)
	END EventDate
from Cal, NumberSequence
option (MaxRecursion 1000)

Open in new window


Sequence of numbers
Am using a CTE to generate a series of numbers. This is useful to calculate the future dates. (This is from https://smehrozalam.wordpress.com/2009/06/09/t-sql-using-common-table-expressions-cte-to-generate-sequences/)

DATEADD
With the sequence of numbers, we can easily do a simple calculation to calculate the date on which the event should happen again.
The CASE statements can be used to handle all your different RecurUnits

Watch out
There is a limit for the CTE, Generating too many numbers causes an error (@end). So just beware.
The method can be tweaked to eliminate date values before today, but I'm not doing that here. Should be easu enough.

Other
SQL 2012 introduced the CREATE SEQUENCE (which might be better than the CTE)
it also introduced the LEAD command, which might make offer an alternative approach. But I haven't fiddled with it much.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Everything in this question is in my article SQL Server Calendar Table.  Knock yourself out.

And I just kicked out a sequel SQL Server Calendar Table:  Tips and Tricks, let me know how you like that one too.
1
dgrafxAuthor Commented:
Jim
I ran the scripts and created the calendar db and the days table
BUT I don't get how to then query as I was asking.
Maybe if you could explain ...

MlandaT
I'm working with your code
The issue I'm working on now is the following:
I have an event that recurs every 3 days
It's start date is January 18 2016
So it recurs on 1-21, 1-24, 1-27, 1-30, 2-2, 2-5, 2-8, 2-11, 2-14, 2-17 and then it stops after 10 reps of course because it is told to repeat 10 times.
So here's the deal - I want it to run forever - if a user pulls up 10 years into the future then there it will be still recurring every 3 days.
So what  ideas do you have that doesn't involve generating multiple events?

Thanks guys ...
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

MlandaTCommented:
You could just increase the value of @end to suit your needs. Set it to a thousand or ten thousand if you like.

However, whatever your user is doing, they will need a limited number of events. I imagine that you will be giving your user a limited view at a time. So like, maybe the user is just viewing a calendar for Jan 2025 -  Feb 2025. What could be done is to introduce a faux startdate, so that we don't show events starting from the actual start date, but we "jump" into that point in future and start from there, as if that were our actual start date. I'll work out the sql for you in a bit.
0
MlandaTCommented:
Have a look at this. I didn't put comments here, but I hope my logic is easy to follow. The final result currently shows you the steps in the calculation
--define the view window period, this is the start date of the 'view' which you are presenting to your user (i.e. date 10 years into the future).
declare @window_start datetime
select @window_start = '01-Dec-2020'

--define start and end limits
Declare @start int, @end int
Select @start=1, @end=10 --increase value of @end to suit your needs (or introduce a @window_end).
 
;With NumberSequence( Number ) as
(
    Select @start as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @end
), Cal(EventName, RecurUnit, RecurEvery, StartDate) as 
(
	select 'Bi-Monthly Magazine', 'month', 3, cast('01-Jan-2015' as datetime)
	union select 'My BirthDay', 'year', 1, cast('01-August-2000' as datetime)
	union select 'Presidential Elections', 'year', 4, cast('01-August-2000' as datetime)
), CalTemp as
(
	select 
		Cal.*,
		CASE Cal.RecurUnit
			WHEN 'month' THEN DATEDIFF(month, Cal.StartDate, @window_start)
			WHEN 'year' THEN DATEDIFF(year, Cal.StartDate, @window_start)
		END NumberOfIntervalsPassed
	from Cal
), CalData as
(
	select 
		CalTemp.*, 
		CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int) NumberOfWholeRecurPeriodsToWindowStart,
		CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)) LastRecurPeriodBeforeWindowStart,
		CASE CalTemp.RecurUnit
			WHEN 'month' THEN DATEADD(month, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), StartDate)
			WHEN 'year' THEN DATEADD(year, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), StartDate)
		END FauxStartDate
	from CalTemp 
)
select 
	CalData.*, @window_start WindowStart,
	NumberSequence.Number, 
	CASE CalData.RecurUnit
		WHEN 'month' THEN DATEADD(month, RecurEvery * NumberSequence.Number, FauxStartDate)
		WHEN 'year' THEN DATEADD(year, RecurEvery * NumberSequence.Number, FauxStartDate)
	END EventDate
from CalData, NumberSequence
option (MaxRecursion 1000)

Open in new window

0
dgrafxAuthor Commented:
MlandaT
This code appears to be excellent!
I'm making a couple modifications but can't get a couple things

1.  how to make events show only between their start date and end date that is in the db.
in the code below cDate is the start date and I'm not using an end date in this code.

2. I need to break the numbersequence up into one for day - one for week - one for month.
day needs to be 42 just in case it's a daily event and the max number of days shown for one view is 42 - I'm showing the last previous month days greyed out and the beginning next month days also greyed out and events do show on them - see attached image.
and week needs to be 6 and month needs to be 2
also - is there a way to limit the generation of events from this numbersequence?
for ex if there is a need for 10 then don't do all 42
cause the more events the longer it takes to populate the calendar even if they are not all used.

See attachment
There is StartView which is Dec 27 2015
There is EndView which is Feb 7 2016
And MonthView which is Jan 2016
The calendar shows those 42 grid days
Here is your code slightly modified:

Declare @SeqStart int, @SeqEnd int, @StartView DateTime, @EndView DateTime, @MonthView datetime
Select @SeqStart=1, @SeqEnd=10,  
@StartView = Cast('2015-12-27' as DateTime), @EndView = Cast('2016-02-07' as DateTime), @MonthView = Cast('2016-01-01' as DateTime)
 
;With NumberSequence( Number ) as
(
    Select @SeqStart as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @SeqEnd
),
Cal(cID, cName, RecurUnit, RecurEvery, cDate) as
(
      Select cID, cName, recurUnit, recurEvery, cDate
      From Calendar_Events with (nolock)
      Where (recurUnit IN ('Day','Week','Month'))
),
CalTemp as
(
select
      Cal.*,      
      CASE Cal.RecurUnit
            WHEN 'day' THEN DATEDIFF(day, Cal.cDate, @MonthView)
            WHEN 'week' THEN DATEDIFF(week, Cal.cDate, @MonthView)
            WHEN 'month' THEN DATEDIFF(month, Cal.cDate, @MonthView)
      END NumberOfIntervalsPassed
from Cal
), CalData as
(
      select
            CalTemp.*,
            CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int) NumberOfWholeRecurPeriodsToWindowStart,
            CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)) LastRecurPeriodBeforeWindowStart,
            CASE CalTemp.RecurUnit
                  WHEN 'day' THEN DATEADD(day, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), cDate)
                  WHEN 'week' THEN DATEADD(week, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), cDate)
                  WHEN 'month' THEN DATEADD(month, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int))-1, cDate)
            END FauxStartDate
      from CalTemp
)
select
      CalData.cID, CalData.cName, CalData.recurUnit, CalData.recurEvery,
      CASE CalData.RecurUnit
            WHEN 'day' THEN DATEADD(day, RecurEvery * NumberSequence.Number, FauxStartDate)
            WHEN 'week' THEN DATEADD(week, RecurEvery * NumberSequence.Number, FauxStartDate)
            WHEN 'month' THEN DATEADD(month, RecurEvery * NumberSequence.Number, FauxStartDate)
      END EventDate
from CalData, NumberSequence

union all

Select cID, cName, recurUnit, recurEvery, cDate
      From Calendar_Events with (nolock)
      Where
      (recurUnit = 'None' and cDate between @StartView and @EndView)                  
      or
      (recurUnit = 'Year' and Month(cDate) IN (Month(@StartView),Month(@MonthView),Month(@EndView))
      )

Order by eventDate
option (MaxRecursion 1000)

=================

I added a -1 in CalData because a recurring month was always showing in the next months greyed out view but when you went to the next month it wasn't there. Dumping the query on the backend showed that it kept writing further and further into the future - weird ...

note that i don't need annual events in the recurring function you created because I simply test for month(cDate) (I will be testing for between start date and end date also) then I rewrite the year part of  those dates with Year(MonthView) (which is the current year)  

Thanks a lot for the help!
0
dgrafxAuthor Commented:
looks like I forgot the attachment ...january
0
MlandaTCommented:
Hey. Got a bit tied up. I hope you coming alright. I get and error now, on table Calendar_Events.
0
dgrafxAuthor Commented:
Actually I've been trying some stuff with no success!
And was kind of waiting for you to post again ...
0
MlandaTCommented:
I wouldnt introduce @MonthView. I would just have @startdate and @enddate. A month view can be worked out and your calling code can provie the SQL code with @startdate and @enddate which represent that month.

I also don't understand the -1 on the months. From my tests, I dont think it's necessary.

I also didn't really get into your logic with the annual events. I assume you have them under control.

I reverted to using my test data coz I don't have a similar table to yours. I added a cEndDate column which can be NULL or will have a date value specifying the date when that recurring event ends.

I hope this code performs well when there are lots of events (00s, 000s, 0000s). In a production system, it might be best to write the sequences to a table which gets rebuilt when the parameters on certain events change. I've just been testing with a few events at a time.
Declare @SeqStart int, @SeqEnd int, @StartView DateTime, @EndView DateTime
Select 
	@SeqStart=1, @SeqEnd=42,  
	@StartView = Cast('2015-12-27' as DateTime), 
	@EndView = Cast('2016-02-07' as DateTime)
 
;With NumberSequence( Number ) as
(
    Select @SeqStart as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @SeqEnd
), 
Cal(cID, cName, RecurUnit, RecurEvery, cDate, cEndDate) as 
(
	select 1, '10 Day Fun Run', 'day', 10, cast('01-August-2015' as datetime), cast('01-Jan-2018' as datetime)
	union select 2, 'Bi-Weekly Check-up', 'week', 2, cast('01-August-2000' as datetime), cast('2016-08-23' as datetime)
	union select 3, 'Bi-Monthly Magazine', 'month', 2, cast('01-Jan-2015' as datetime), cast('01-Jan-2017' as datetime)
	union select 3, 'Quarterly Games', 'month', 3, cast('01-Jan-2015' as datetime), null

      --Select cID, cName, recurUnit, recurEvery, cDate
      --From Calendar_Events with (nolock)
      --Where (recurUnit IN ('Day','Week','Month'))
), 
CalTemp as
(
	select 
		  Cal.*,
		  CASE 
			WHEN Cal.cDate > @StartView THEN 0
			ELSE CASE Cal.RecurUnit
					WHEN 'day' THEN DATEDIFF(day, Cal.cDate, @StartView)
					WHEN 'week' THEN DATEDIFF(week, Cal.cDate, @StartView)
					WHEN 'month' THEN DATEDIFF(month, Cal.cDate, @StartView)
				END 
			END NumberOfIntervalsPassed
	from Cal
	where Cal.cEndDate is null or Cal.cEndDate > @StartView --it has no specified end date, or the end date is before our view window start
), CalData as
(
      select 
            CalTemp.*, 
            CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int) NumberOfWholeRecurPeriodsToWindowStart,
            CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)) LastRecurPeriodBeforeWindowStart,
            CASE CalTemp.RecurUnit
                  WHEN 'day' THEN DATEADD(day, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), cDate)
                  WHEN 'week' THEN DATEADD(week, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), cDate)
                  WHEN 'month' THEN DATEADD(month, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), cDate)
            END FauxStartDate
      from CalTemp 
)
select 
      --CalData.*, 
	  CalData.cID, CalData.cName, CalData.recurUnit, CalData.recurEvery,
      CASE CalData.RecurUnit
            WHEN 'day' THEN DATEADD(day, RecurEvery * NumberSequence.Number, FauxStartDate)
            WHEN 'week' THEN DATEADD(week, RecurEvery * NumberSequence.Number, FauxStartDate)
            WHEN 'month' THEN DATEADD(month, RecurEvery * NumberSequence.Number, FauxStartDate)
      END EventDate
from CalData, NumberSequence
where
        --we want EventDates before @EndView or CalData.cEndDate, whatever comes first
	CASE WHEN @EndView < CalData.cEndDate THEN @EndView ELSE CalData.cEndDate END >= CASE CalData.RecurUnit
        WHEN 'day' THEN DATEADD(day, RecurEvery * NumberSequence.Number, FauxStartDate)
        WHEN 'week' THEN DATEADD(week, RecurEvery * NumberSequence.Number, FauxStartDate)
        WHEN 'month' THEN DATEADD(month, RecurEvery * NumberSequence.Number, FauxStartDate)
    END 



--union all

--Select cID, cName, recurUnit, recurEvery, cDate
--      From Calendar_Events with (nolock)
--      Where
--      (recurUnit = 'None' and cDate between @StartView and @EndView)                  
--      or
--      (recurUnit = 'Year' and Month(cDate) IN (Month(@StartView),Month(@MonthView),Month(@EndView))
--      )

Order by eventDate
option (MaxRecursion 1000)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgrafxAuthor Commented:
Thanks - I can try this shortly ...
0
dgrafxAuthor Commented:
MlandaT
It's looking good - but take a look at this:

swap these lines in your code so that the dates start in Jan 2016 so we can see what's going on better:

select 1, '10 Day Fun Run', 'day', 10, cast('05-Jan-2016' as datetime), cast('01-Jan-2018' as datetime)
      union select 2, 'Bi-Weekly Check-up', 'week', 2, cast('02-Jan-2016' as datetime), cast('2016-08-23' as datetime)
      union select 3, 'Every Other Month Magazine', 'month', 2, cast('04-Jan-2016' as datetime), cast('01-Jan-2017' as datetime)
      union select 4, 'Quarterly Games', 'month', 3, cast('01-Jan-2016' as datetime), cast('01-Jan-2017' as datetime)

then also swap your code with the following which has the -1 I spoke of

WHEN 'day' THEN DATEADD(day, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)-1), cDate)
WHEN 'week' THEN DATEADD(week, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)-1), cDate)
WHEN 'month' THEN DATEADD(month, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)-1), cDate)

The code above is attachment image 1
----------------------------------------------------
Here is image 2
The difference is that cEndDate is null as was original
And see how that row (quarterly games) doesn't show without a value
----------------------------------------------------
Here is image 3
Which is the original except that I left the dates modified so that they start in Jan 2016 we can more easily see them.
So this is code where the -1 has been removed again
----------------------------------------------------

Note that if an event starts on Jan 2 2016 it needs to display on Jan 2 2016

I really appreciate this code - it's awesome work!
image1.png
image2.png
image3.png
0
dgrafxAuthor Commented:
MlandaT
It is fine that the enddate being null isn't exactly working because all events do have an enddate - just fyi
I added a new column named forever that if 1 then doesn't ever expire
CASE WHEN CalData.forever = 1 then @EndView ELSE
            CASE WHEN @EndView < CalData.eDate THEN @EndView ELSE CalData.eDate END
      END >= ...

Thanks

I will ask more question no doubt and will post a link here
Thanks much!
0
dgrafxAuthor Commented:
here is another question about holidays
http://www.experts-exchange.com/questions/28712324/calculating-calendar-holidays.html

thanks

I also have an Easter calculation in a function - you just send in year like dbo.getEaster(2016) - and was thinking I'd only call the calculation if the month is March or April (cause they are the only 2 months that Easter occurs)  but with the date range I'm sending in there might be 2 different years like 2015-12-27 to 2016-02-07 so could you comment on that as well when you have time to look at this question.
my thought was why calculate on every view when you know there will not be an Easter in June for ex

! important - could you give me a clue on how to add the recurUnit = 'None' which means that it is a one time event and that it will only display exactly on cDate and that's it
thanks
0
MlandaTCommented:
Recur unit none... Treat it like a thousand years. That will deal with it. The Easter  function... Will check if I've time. Day looking  hectic
0
dgrafxAuthor Commented:
MlandaT
Posted here is my final (for the time being) code.
I seem to have picked up a bug somewhere in modifying because I believe it was all good to go.
The issue is that events display that shouldn't as you can see when you run the code.
I added the commented out code at the bottom which seems to fix the issue but wanted you to look at it all to see if I did break something.
Plus can you give an opinion as to if efficient or not.

Thanks again!

Declare @SeqStart int, @SeqEnd int, @StartView DateTime, @EndView DateTime
Select @SeqStart=0, @SeqEnd=42, @StartView = Cast('2015-12-27' as DateTime), @EndView = Cast('2016-02-07' as DateTime)

SET NOCOUNT ON;

;With NumberSequence( Number ) as
(
	Select @SeqStart as Number
	union all
	Select Number + 1
	from NumberSequence
	where Number < @SeqEnd
), 
Cal(cID, eventName, RecurUnit, RecurEvery, startDate, endDate, endless) as 
(
	select 1, '10 Day Fun Run', 'day', 10, cast('07-Jan-2016' as datetime), cast('01-Feb-2016' as datetime), 0
	union select 2, 'Bi-Weekly Check-up', 'week', 3, cast('02-Jan-2016' as datetime), cast('2016-03-23' as datetime), 0
	union select 3, 'Every Other Month Magazine', 'month', 2, cast('04-Jan-2016' as datetime), cast('01-Jan-2017' as datetime), 0
	union select 4, 'Quarterly Games', 'month', 3, cast('01-Jan-2016' as datetime), cast('01-Jan-2017' as datetime), 0
	union select 5, 'Yearly Sacrifice', 'year', 2, cast('26-Dec-2015' as datetime), cast('01-Apr-2018' as datetime), 0
	union select 6, 'Dorian bday', 'year', 1, cast('27-Apr-2015' as datetime), cast('28-Apr-2015' as datetime), 1
	union select 7, 'Daniel bday', 'year', 1, cast('14-Mar-2012' as datetime), cast('15-Mar-2012' as datetime), 1
), 
CalTemp as
(
	SELECT 
		Cal.cID, Cal.eventName, Cal.RecurUnit, Cal.RecurEvery, Cal.startDate, Cal.endDate, Cal.endless,
		CASE 
			WHEN Cal.startDate > @StartView THEN 0 
			ELSE CASE Cal.RecurUnit
				WHEN 'day' THEN DATEDIFF(day, Cal.startDate, @StartView)
				WHEN 'week' THEN DATEDIFF(week, Cal.startDate, @StartView)
				WHEN 'month' THEN DATEDIFF(month, Cal.startDate, @StartView)
				WHEN 'year' THEN DATEDIFF(year, Cal.startDate, @StartView)
			END 
		END NumberOfIntervalsPassed
	FROM Cal
	WHERE
	(
	Cal.endless = 1 or (Cal.endDate > @StartView and Cal.startDate < @EndView)
	)
), CalData as
(
	SELECT
		CalTemp.cID, CalTemp.eventName, CalTemp.RecurUnit, CalTemp.RecurEvery, CalTemp.startDate, CalTemp.endDate, CalTemp.endless, 
		CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int) NumberOfWholeRecurPeriodsToWindowStart,
		CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)) LastRecurPeriodBeforeWindowStart,
		CASE CalTemp.RecurUnit			
			WHEN 'day' THEN DATEADD(day, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
			WHEN 'week' THEN DATEADD(week, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
			WHEN 'month' THEN DATEADD(month, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
			WHEN 'year' THEN DATEADD(year, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
		END FauxStartDate
	FROM CalTemp 
)
SELECT     
	CalData.cID, CalData.eventName, CalData.recurUnit, CalData.recurEvery, CalData.endDate, CalData.endless,	CalData.FauxStartDate,
	CASE CalData.RecurUnit
		WHEN 'day' THEN DATEADD(day, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'week' THEN DATEADD(week, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'month' THEN DATEADD(month, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'year' THEN DATEADD(year, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
	END EventDate
FROM CalData, NumberSequence
WHERE
	CASE 
		WHEN CalData.endless = 1 THEN 
			@EndView 
		ELSE 
			CASE WHEN @EndView < CalData.endDate THEN @EndView ELSE CalData.endDate END
		END >=	
		CASE CalData.RecurUnit
			WHEN 'day' THEN DATEADD(day, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
			WHEN 'week' THEN DATEADD(week, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
			WHEN 'month' THEN DATEADD(month, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
			WHEN 'year' THEN DATEADD(year, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		END
		
	--AND
		--@StartView <=
		--CASE CalData.RecurUnit
			--WHEN 'day' THEN DATEADD(day, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
			--WHEN 'week' THEN DATEADD(week, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
			--WHEN 'month' THEN DATEADD(month, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
			--WHEN 'year' THEN DATEADD(year, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)			
		--END

Order by eventName,eventDate
option (MaxRecursion 1000)

Open in new window

0
MlandaTCommented:
I see the years are misbehaving. i'll have a look later tonight.
0
dgrafxAuthor Commented:
I appreciate it!
0
MlandaTCommented:
I think this does it:
Declare @SeqStart int, @SeqEnd int, @StartView DateTime, @EndView DateTime
Select @SeqStart=0, @SeqEnd=42, @StartView = Cast('2015-12-27' as DateTime), @EndView = Cast('2016-02-07' as DateTime)
--Select @SeqStart=0, @SeqEnd=42, @StartView = Cast('2016-04-01' as DateTime), @EndView = Cast('2016-04-30' as DateTime)

SET NOCOUNT ON;

;With NumberSequence( Number ) as
(
	Select @SeqStart as Number
	union all
	Select Number + 1
	from NumberSequence
	where Number < @SeqEnd
), 
Cal(cID, eventName, RecurUnit, RecurEvery, startDate, endDate, endless) as 
(
	select 1, '10 Day Fun Run', 'day', 10, cast('07-Jan-2016' as datetime), cast('01-Feb-2016' as datetime), 0
	union select 2, 'Bi-Weekly Check-up', 'week', 3, cast('02-Jan-2016' as datetime), cast('2016-03-23' as datetime), 0
	union select 3, 'Every Other Month Magazine', 'month', 2, cast('04-Jan-2016' as datetime), cast('01-Jan-2017' as datetime), 0
	union select 4, 'Quarterly Games', 'month', 3, cast('01-Jan-2016' as datetime), cast('01-Jan-2017' as datetime), 0
	union select 5, 'Yearly Sacrifice', 'year', 2, cast('26-Dec-2015' as datetime), cast('01-Apr-2018' as datetime), 0
	union select 6, 'Dorian bday', 'year', 1, cast('27-Apr-2015' as datetime), cast('28-Apr-2015' as datetime), 1
	union select 7, 'Daniel bday', 'year', 1, cast('14-Mar-2012' as datetime), cast('15-Mar-2012' as datetime), 1
), 
CalTemp as
(
	SELECT 
		Cal.cID, Cal.eventName, Cal.RecurUnit, Cal.RecurEvery, Cal.startDate, Cal.endDate, Cal.endless,
		CASE 
			WHEN Cal.startDate > @StartView THEN 0 
			ELSE CASE Cal.RecurUnit
				WHEN 'day' THEN DATEDIFF(day, Cal.startDate, @StartView)
				WHEN 'week' THEN DATEDIFF(week, Cal.startDate, @StartView)
				WHEN 'month' THEN DATEDIFF(month, Cal.startDate, @StartView)
				WHEN 'year' THEN DATEDIFF(year, Cal.startDate, @StartView)
			END 
		END NumberOfIntervalsPassed
	FROM Cal
	WHERE
	(
		Cal.endless = 1 
		or (Cal.endDate > @StartView and Cal.startDate < @EndView)
	)
), CalData as
(
	SELECT
		CalTemp.cID, CalTemp.eventName, CalTemp.RecurUnit, CalTemp.RecurEvery, CalTemp.startDate, CalTemp.endDate, CalTemp.endless, 
		CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int) NumberOfWholeRecurPeriodsToWindowStart,
		CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)) LastRecurPeriodBeforeWindowStart,
		CASE CalTemp.RecurUnit			
			WHEN 'day' THEN DATEADD(day, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
			WHEN 'week' THEN DATEADD(week, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
			WHEN 'month' THEN DATEADD(month, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
			WHEN 'year' THEN DATEADD(year, CalTemp.RecurEvery * (CalTemp.NumberOfIntervalsPassed / cast(CalTemp.RecurEvery as int)), startDate)
		END FauxStartDate
	FROM CalTemp 
)
SELECT     
	CalData.cID, CalData.eventName, CalData.recurUnit, CalData.recurEvery, CalData.endDate, CalData.endless, CalData.FauxStartDate,
	CASE CalData.RecurUnit
		WHEN 'day' THEN DATEADD(day, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'week' THEN DATEADD(week, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'month' THEN DATEADD(month, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'year' THEN DATEADD(year, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
	END EventDate
FROM CalData, NumberSequence
WHERE
	/*event date must be less than @endView*/
	CASE CalData.RecurUnit
		WHEN 'day' THEN DATEADD(day, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'week' THEN DATEADD(week, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'month' THEN DATEADD(month, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
		WHEN 'year' THEN DATEADD(year, CalData.RecurEvery * NumberSequence.Number, CalData.FauxStartDate)
	END 
	BETWEEN 
		@StartView 
		AND CASE /*the end date is dependent on several factors*/
			WHEN 
				CalData.Endless = 1 --it's an endless event,
				OR CalData.endDate is null --it does not have an end-date (I know you dont!!! :) )
				OR @EndView < CalData.endDate --the end date of the view we want is bef
			THEN @EndView 
			ELSE CalData.endDate 
		END 

Order by eventName,eventDate
option (MaxRecursion 1000)

Open in new window

0
dgrafxAuthor Commented:
I do appreciate - have an excellent day - or it may be night where you are
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.