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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-03-07 : 08:25:35
|
| Fred writes "First off, I'm running the following...Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)My application is a classified board with an events calendar. I need to be able to display the events in a calendar view as well as an upcoming events list. Below are example links of the two:Calendar viewEvents ListToday you'll see a good example of my present solution: When an event spans several days, my script inserts an additional record for each day. This method has one advantage... the poster can enter specific information about each day. For instance on Sundays, the event frequently ends earlier, like at 3:00 PM.But instead, I would like to implement a single record/multiple dates model. So I am trying to determine an efficient query that will produce multiple records as follows:I have a startdate and enddate fields, which are the short date format, for instance (03/06/2006).For flexibility, I also added a checkbox for each day of the week. So for instance, an event could be every monday night during March. startdate=03/01/2006 enddate=03/31/2006 and mon=true. If no weekdays are checked, assume all.The calendar view will display a record for every matching day. The events list will display only one occurence of each event... the NEXT occurence from today's date.I got most of this to work in MS Access by joining to a date table. But there were problems when I tried it in SQL Server. I'm just a novice in SQL Server, and I'm sure one of the professionals out there knows how to solve this one." |
|
|
frasmus
Starting Member
1 Post |
Posted - 2006-03-07 : 22:22:10
|
| Hurray, I figured it out. This is the query I got to work:SELECT tblAds.*, tbldates.mydate FROM tbldates, tblAds WHERE (tblAds.catName='Events' AND (tblDates.mydate BETWEEN dateadd(mm,-1,getdate() - day(getdate())) AND dateadd(mm,1,getdate() - day(getdate())+90)-1) AND (tblDates.mydate BETWEEN tblAds.sdate AND tblAds.edate)) AND ((tblAds.mon=1 AND DATEPART(dw,tblDates.myDate)=2) OR (tblAds.tue=1 AND DATEPART(dw,tblDates.myDate)=3) OR (tblAds.wed=1 AND DATEPART(dw,tblDates.myDate)=4) OR (tblAds.thu=1 AND DATEPART(dw,tblDates.myDate)=5) OR (tblAds.fri=1 AND DATEPART(dw,tblDates.myDate)=6) OR (tblAds.sat=1 AND DATEPART(dw,tblDates.myDate)=7) OR (tblAds.sun=1 AND DATEPART(dw,tblDates.myDate)=1) OR (tblAds.mon=0 AND tblAds.tue=0 AND tblAds.wed=0 AND tblAds.thu=0 AND tblAds.fri=0 AND tblAds.sat=0 AND tblAds.sun=0))ORDER BY tblDates.myDate;Thanks to everyone anyhow. |
 |
|
|
|
|
|
|
|
single record to display a range of dates and weekdays