Solved

Posted on 2011-10-12
166 Views
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
Question by:vikas_nm

LVL 51

Expert Comment

what is "1st      Friday" is it 1st friday on every month or year?
0

LVL 51

Expert Comment

also is there any limit on time interval, like show events in max 365 gun or max 90 days...
0

LVL 51

Expert Comment

also OccursOn is varchar? why dont you replace it with int?

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

to make our lives easy :)
0

LVL 51

Expert Comment

also I guess there is a typo here (0-->4):

8      All      Wednesday      0

-->

8      All      Wednesday      4
0

Author Comment

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

LVL 51

Accepted Solution

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
``````
0

LVL 51

Expert Comment

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

LVL 51

Expert Comment

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

LVL 51

Expert Comment

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

Author Comment

Will this work if i have got n number of events
0

LVL 51

Expert Comment

there should not be any limit on above solution...
what do you mean by "n number of events"?
0

Author Comment

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

LVL 51

Expert Comment

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
``````
0

LVL 51

Assisted Solution

function, table and view described in my previous post as well (@ 36957693)
0

Author Comment

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

LVL 51

Expert Comment

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

Author Comment

ok
0

LVL 51

Assisted Solution

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

Author Comment

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

LVL 51

Expert Comment

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

Author Comment

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

LVL 51

Assisted Solution

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

Author Closing Comment

Thanks a million. Truly a Genius
0

## Featured Post

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed â€¦
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiâ€¦
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax â€” just include tâ€¦
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, frâ€¦