super duper query - 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 2000 Forums

Transact-SQL (2000)

super duper query

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2003-09-13 : 14:39:55

Hi,

Say I have a table that holds events for a calender:

Columns

StartDate, EndDate, IsEveryWeek, IsEveryMonth

So I will query for all events in a given month, BUT the catch is, some events happen EVERY week (IsEveryWeek) or every month (IsEveryMonth) on specific days....I could insert a record for each week/month but I would rather have 1 row entry that will handle all these cases.

SO the basic query is:

select *
from Events
WHERE getDate() is between startDate and EndDate

1. How can I grab all the events that are re-occuring? Use a UNION?
2. better table design ideas?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-13 : 17:10:35

select *
from Events
WHERE
(IsEveryWeek=1 and
datepart('dw',getdate())>=datepart('dw',StartDate) and
datepart('dw',getdate())<=datepart('dw',EndDate))
OR
(IsEveryMonth=1 and
datepart('dd',getdate())>=datepart('dd',StartDate) and
datepart('dd',getdate())<=datepart('dd',EndDate))
OR
(getdate()>=StartDate and getdate()<=EndDate)

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2003-09-14 : 00:08:45

Not to shabby!

Basically I want to cover all the options that are avialable, similar to SQL JOb options (occurs every day at...the 1st of every month at...) etc.

So this is very doable with 1 record in the db, great news!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-14 : 04:43:25

Sorry man... I've understood nothing.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2003-09-14 : 16:14:17

Sorry, let me make it clearer.
Scheduling jobs in sqlserver gives you the following options which I want to mimick in my events table.

///////////////
Occurs:
- daily
- weekly
- monthly

Daily:
Every x day(s)

Frequency:
- occurs once at: 24 hour timing selection
- occurs every : 1 hour starting: 24:00:00 ending at:_____

Duratation:
- start date
- end date
- no end date
///////////////

I'm pretty sure my simple table design will need to be modified to handle all these cases correct?

Not sure if I can handle the " occur daily, every 2 days" using 1 query though...hmmmm

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-14 : 17:39:19

if you have a table of numbers (called "Numbers", with a single int column called "Number" with values from 1 - 1000 or so) and you have a table of events like this:

Events:
EventID
StartDate
EndDate
IncrType -- the type of time incrememt, dd = days, hh = hours, ww = weeks, etc ...
IncrValue -- # to increment; i.e., 1 = every day, 2 = every two days, etc ...

then you can get a table of all possible events and the days they will occur on with something like this:


select * from
(
select eventID, 
  case IncrType 
    when 'dd' then dateadd(dd, (number-1) * IncrValue, StartDate)
    when 'ww' then dateadd(ww, (number-1) * IncrValue, StartDate)
    when 'hh' then dateadd(hh, (number-1) * IncrValue, StartDate)
    when 'mm' then dateadd(mm, (number-1) * IncrValue, StartDate)
  end as EventDate,
  EndDate
from
  Events
cross join
  Numbers
) a
where EventDate <= EndDate

This can be made more efficient, i may post an improvment if no one else does later on tonight or tomorrow. the problem is it will generate a LOT of unnecessary events that are filtered out at the last step.

a better way might be to calculate or store a "# of repetitions" columns which calculates, based on the start date, end date, and time increment, how many repitions of that event there will be. Then, instead of cross joining, you would say:


Events
  INNER JOIN
Numbers
  ON Numbers.Number <= Events.Iterations

or something like that ....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources