• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Loading Recurring Events in SQL 2005

Hi,
Experts please find the attached xl sheet for my question. Really an emergency for me as we have to deliver this i a short span of time.

Book2.xlsx
0
vikas_nm
Asked:
vikas_nm
  • 15
  • 8
4 Solutions
 
HainKurtSr. System AnalystCommented:
what is "1st      Friday" is it 1st friday on every month or year?
0
 
HainKurtSr. System AnalystCommented:
also is there any limit on time interval, like show events in max 365 gun or max 90 days...
0
 
HainKurtSr. System AnalystCommented:
also OccursOn is varchar? why dont you replace it with int?

1st-->1
2nd-->2
...
All-->0

to make our lives easy :)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
HainKurtSr. System AnalystCommented:
also I guess there is a typo here (0-->4):


8      All      Wednesday      0

-->

8      All      Wednesday      4
0
 
vikas_nmAuthor Commented:
what is "1st      Friday" is it 1st friday on every month or year?
Yes it is first friday of every month


also is there any limit on time interval, like show events in max 365 gun or max 90 days...
Not more than 1 year

also OccursOn is varchar? why dont you replace it with int?
It is actually int for understanding purpose i have mentioned as 1st, 2nd

8      All      Wednesday      0
This is not typo 0 means All wednesdays
0
 
HainKurtSr. System AnalystCommented:
ok, I tried to create all in one query as follow

where getWeekDay(...) is a function I grabbed from

http://www.codeproject.com/KB/database/SQL_Server_2000.aspx

and v_nnn is a view

create view [dbo].[v_nnn] as
select n1.n*100+n2.n*10+n3.n as n from numbers n1, numbers n2, numbers n3

where numbers is a tiny table that I use for these purposes

n
0
1
2
3
4
5
6
7
8
9

so, v_nnn is a view with 1000 rows (0,1,2,...999)

and here it goes...
declare @startdate date = convert(date, '20111001');
declare @enddate date = convert(date, '20111030');

with e as (
select 1 Event, 1 OccursOn, 6 wd
union select 2,	1, 7
union select 3,	3, 7
union select 4,	2, 6
union select 5,	2, 7
union select 6,	3, 7
union select 8,	0, 4
)
select * from (
select n, Event, OccursOn, wd, DATEADD(day, n,@startDate) EvtDate 
from dbo.v_nnn days, e
where days.n < DATEDIFF(day, @startdate, @enddate)
) x
where ((dbo.getWeekDay(OccursOn,wd,EvtDate)=EvtDate) or (DATEPART(weekday, EvtDate) = wd and OccursOn=0))
order by n

Open in new window

0
 
HainKurtSr. System AnalystCommented:
above query gives me this result (not tested :)

n      Event      OccursOn      wd      EvtDate
4      8      0      4      2011-10-05
6      1      1      6      2011-10-07
7      2      1      7      2011-10-08
11      8      0      4      2011-10-12
13      4      2      6      2011-10-14
14      5      2      7      2011-10-15
18      8      0      4      2011-10-19
21      6      3      7      2011-10-22
21      3      3      7      2011-10-22
25      8      0      4      2011-10-26
0
 
HainKurtSr. System AnalystCommented:
this is based on my data e, which is

Event      OccursOn      wd
1      1      6
2      1      7
3      3      7
4      2      6
5      2      7
6      3      7
8      0      4

if you can convert your data to this one (or write a query to convert your table into this structure, you can use my script as is)

OccursOn = 0 : all
wd : weekday, 1=sunday, 2=monday...

simple setup... but a bit different than yours...
0
 
HainKurtSr. System AnalystCommented:
i recommend this structure for your table

Event      OccursOn      wd
1      1      6
2      1      7
3      3      7
4      2      6
5      2      7
6      3      7
8      0      4

if it is possible... all combinations is also possible here, no weekday name is stored...
0
 
vikas_nmAuthor Commented:
Will this work if i have got n number of events
0
 
HainKurtSr. System AnalystCommented:
there should not be any limit on above solution...
what do you mean by "n number of events"?
0
 
vikas_nmAuthor Commented:
I have created a table [dbo].[Event] which has columns
EventId int,
OccursOn int,
WeekDay int

How do i use this table in your above query.
0
 
HainKurtSr. System AnalystCommented:
just remove line 4-12 (that is for me to test) and instead of e use Event

assuming you created the function, numbers table (n:int) and out 10 records inside (0,1,2,...9) and also create the view v_nnn
declare @startdate date = convert(date, '20111001');
declare @enddate date = convert(date, '20111030');

select * from (
select n, Event, OccursOn, WeekDay, DATEADD(day, n,@startDate) EvtDate 
from dbo.v_nnn days, [dbo].[Event] e
where days.n < DATEDIFF(day, @startdate, @enddate)
) x
where ((dbo.getWeekDay(OccursOn, WeekDay ,EvtDate)=EvtDate) or (DATEPART(weekday, EvtDate) = WeekDay and OccursOn=0))
order by n

Open in new window

0
 
HainKurtSr. System AnalystCommented:
function, table and view described in my previous post as well (@ 36957693)
0
 
vikas_nmAuthor Commented:
I have checked it but in your query result given above:
above query gives me this result (not tested :)

n      Event      OccursOn      wd      EvtDate
4      8      0      4      2011-10-05
6      1      1      6      2011-10-07
7      2      1      7      2011-10-08
11      8      0      4      2011-10-12
13      4      2      6      2011-10-14
14      5      2      7      2011-10-15
18      8      0      4      2011-10-19
21      6      3      7      2011-10-22
21      3      3      7      2011-10-22
25      8      0      4      2011-10-26


Saturday is not coming propery 'cause
1st saturday is 1-Oct-11 which it shows 08-Oct-11
3rd saturday is 15-Oct-11 which it shows 22-Oct-11.


0
 
HainKurtSr. System AnalystCommented:
looks like that function is not working properly :)

select dbo.getWeekDay(1,7,GETDATE())
2011-10-08 00:00:00.000

let me check...
0
 
vikas_nmAuthor Commented:
ok
0
 
HainKurtSr. System AnalystCommented:
ok, on the function getWeekDay() line 23

 if(@DayDiffrence > 0)

-->

 if(@DayDiffrence >= 0)

and run again... with my sample, I get this

n      Event      OccursOn      wd      EvtDate
0      2      1      7      2011-10-01
4      8      0      4      2011-10-05
6      1      1      6      2011-10-07
7      5      2      7      2011-10-08
11      8      0      4      2011-10-12
13      4      2      6      2011-10-14
14      3      3      7      2011-10-15
14      6      3      7      2011-10-15
18      8      0      4      2011-10-19
25      8      0      4      2011-10-26
0
 
vikas_nmAuthor Commented:
I checked everything seems to be ok. But can u tell me why we are using the numbers table and seprate view to achieve this are there any limitations to that.
0
 
HainKurtSr. System AnalystCommented:
I guess we should fix his code, at least put a comment :) which I did...
hope you know how to alter the function (right click, script function as -> alter to -> new query editor : change the code and compile, F5)
0
 
vikas_nmAuthor Commented:
i have done that. But you haven't answered the question.
why we are using the numbers table and seprate view to achieve this are there any limitations to that.
0
 
HainKurtSr. System AnalystCommented:
limitations is that it gives numbers from 0 to 999 (it covers 3 years :)

select n1.n*100+n2.n*10+n3.n as n from numbers n1, numbers n2, numbers n3

I have more of these

0 - 99
create view [dbo].[v_nn] as
select n1.n*10+n2.n as n from numbers n1, numbers n2

0-999
create view [dbo].[v_nnn] as
select n1.n*100+n2.n*10+n3.n as n from numbers n1, numbers n2, numbers n3

0-9999
create view [dbo].[v_nnnn] as
select n1.n*1000+n2.n*100+n3.n*10+n4.n as n from numbers n1, numbers n2, numbers n3, numbers n4

and so on... you got the idea...

all are using the same numbers table (which are the digits)

depending on your needs use appropriate view
0
 
vikas_nmAuthor Commented:
Thanks a million. Truly a Genius
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now