November 3, 2009 at 11:37 am
I know there is a bit of sql code here but I need some help with getting the correct dates created when the start date is greater than the user selected days. This project is to mimic the recurrence settings in MS Outlook to a minimum. I am probably not approaching this project from the correct direction but if anyone of the Expert out there could help me get the sql code to create the dates when the start date is greater than the user selected days.
Basically what is happening here is a user is looking at the week option of the MS Outlook
Recurrence settings and selecting any pattern of days, Sunday thru Saturday with a certain Start Date.
Thanks in advance for your help.
use sandbox
--test the sp
DECLARE@iEventID int, @dtStartDate datetime,@dtStartTime datetime,@dtEndTime datetime,@iWeekFreq int,@iDayMask int
DECLARE @dtRangeEndDate datetime,@vRangeEndType nvarchar(10),@iRangeEndOccurs int,@vSelectedDays nvarchar(7)
DECLARE @iSunday int,@iMonday int,@iTuesday int,@iWednesday int,@iThursday int,@iFriday int,@iSaturday int
SET @iEventID = 1
SET @dtStartDate = '11/5/2009 00:00:00'
SET @dtStartTime = '1/1/1900 11:00:00'
SET @dtEndTime = '1/1/1900 12:00:00'
SET @iWeekFreq = 1
SET @dtRangeEndDate = '12/10/2009 00:00:00'
SET @vRangeEndType = 'EndDate'
SET @iSunday = 1
SET @iMonday = 2
SET @iTuesday = 4
SET @iWednesday = 8
SET @iThursday = 16
SET @iFriday = 32
SET @iSaturday = 64
SELECT @iDayMask = @iMonday | @iWednesday
BEGIN
--declare @dtNewEndDate and @vDateName for sp
DECLARE @dtNewEndDate datetime,@vDateName nvarchar(10),@dtStartDateTime datetime,@dtEndDateTime datetime
DECLARE @iDayLoop int,@iNumberOfDays int,@iWeek int,@vSelectedDay nvarchar(10)
SET @iNumberOfDays = 6 --6 because I use this in the calc with the Power function 0 to 6 = 7
SET @iWeek = 7
--if @vRangeEndType=EndDate Requires an @dtRangeEndDate value
IF @vRangeEndType='EndDate'
--loop every @iWeekFreq number until the Range end date is reached
WHILE @dtStartDate < @dtRangeEndDate
BEGIN
--***does this sp need an sp to check for EventID?
--loop every
SET @iDayLoop = 0
SET @vDateName = DATENAME(weekday,@dtStartDate)
--find the first day selected then set the date; store selected day's datetime value
--for the next selected day's datetime value calc
DECLARE @iSelectedDayValue int,@dtSelectedDate datetime,@iDayValue int,@bitIsDaySelected bit,@iStartDayValue int,@dtAdjStartDate datetime
WHILE @iDayLoop <= @iNumberOfDays
BEGIN
--set bitmask day value
SET @iDayValue = POWER(2,@iDayLoop)
--set bit Is Day Selected to local variable
SET @bitIsDaySelected = (SELECT CASE WHEN @iDayMask & @iDayValue = @iDayValue THEN 1
ELSE 0
END )
IF @bitIsDaySelected = 1
BEGIN
--find user selected day value to calc new selected date
--set the selected day's value below both case stmts
SET @iSelectedDayValue =
(SELECT CASE
WHEN @iDayValue=1 THEN 1 --Sunday
WHEN @iDayValue=2 THEN 2 --Monday
WHEN @iDayValue=4 THEN 3 --Tuesday
WHEN @iDayValue=8 THEN 4 --Wednesday
WHEN @iDayValue=16 THEN 5 --Thursday
WHEN @iDayValue=32 THEN 6 --Friday
WHEN @iDayValue=64 THEN 7 --Saturday
END)
--find start date name and adjust the date so the selected date
--is correct for the user selected date calc below
--this case stmt
SET @iStartDayValue =
(SELECT CASE
WHEN @vDateName='Sunday' THEN 1 --Sunday
WHEN @vDateName='Monday' THEN 2 --Monday
WHEN @vDateName='Tuesday' THEN 3 --Tuesday
WHEN @vDateName='Wednesday' THEN 4 --Wednesday
WHEN @vDateName='Thursday' THEN 5 --Thursday
WHEN @vDateName='Friday' THEN 6 --Friday
WHEN @vDateName='Saturday' THEN 7 --Saturday
END)
IF @iSelectedDayValue < @iStartDayValue --testing
--set the insert start date for selected day
SET @dtSelectedDate = (@dtStartDate + (@iSelectedDayValue - @iStartDayValue))
SET @dtStartDateTime = (@dtSelectedDate + @dtStartTime)
SET @dtEndDateTime = (@dtSelectedDate + @dtEndTime)
IF @dtStartDateTime < @dtRangeEndDate
INSERT INTO tblEventSchedules ( [EventId],[StartTime],[EndTime] ) VALUES ( @iEventID,@dtStartDateTime,@dtEndDateTime)
--increment @iDayLoop
SET @iDayLoop = @iDayLoop + 1
END
ELSE
SET @iDayLoop = @iDayLoop + 1
END
--set the next start date according to the week frequency
SET @dtStartDate = @dtStartDate + (@iWeekFreq * @iWeek)
END
--else if @vRangeEndType=OccurNum Requires an @iRangeEndOccurs value
--IF @vRangeEndType='OccurNum'
--BEGIN
----delcare and set @dtNewEndDate to include the @iRangeEndOccurs value; we are still in the Days @TypeRequest
----section so we need to still include the @DayFreq number as well
--SET @dtNewEndDate = (@dtStartDate + ((@iWeekFreq * @iRangeEndOccurs) - @iWeekFreq))
--WHILE @dtStartDate <= @dtNewEndDate
--BEGIN
----this will insert into the EventId, StartTime and EndTime fields into the tblEventSchedules
----need the EventID before this can happen
----***does the sp need to check for EventID and New Start and End times?
--SET @dtStartDateTime = (@dtStartDate + @dtStartTime)
--SET @dtEndDateTime = (@dtStartDate + @dtEndTime)
--INSERT INTO tblEventSchedules ( [EventId],[StartTime],[EndTime] ) VALUES ( @iEventID,@dtStartDateTime,@dtEndDateTime)
--SET @dtStartDate = @dtStartDate + @iWeekFreq
--END
--END
END
--for testing; comment out when building sp
SELECT * FROM dbo.tblEventSchedules ORDER BY EventID
--______________________________________________________________________________________________
--below is the create sql for table dbo.tblEventSchedules
--______________________________________________________________________________________________
--USE [SandBox]
--GO
--/****** Object: Table [dbo].[tblEventSchedules] Script Date: 11/03/2009 10:25:06 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--CREATE TABLE [dbo].[tblEventSchedules](
--[EventScheduleID] [int] IDENTITY(1,1) NOT NULL,
--[EventID] [int] NOT NULL,
--[StartTime] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_StartTime] DEFAULT (getdate()),
--[EndTime] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_EndTime] DEFAULT (getdate()),
--[Comment] [nvarchar](500) NULL,
--[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_CreatedOn] DEFAULT (getdate()),
--[ModifiedOn] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_ModifiedOn] DEFAULT (getdate()),
--[DeletedOn] [datetime] NULL,
--[CreatedBy] [nvarchar](100) NOT NULL CONSTRAINT [DF_tblEventSchedules_CreatedBy] DEFAULT ('System'),
--[ModifiedBy] [nvarchar](100) NOT NULL CONSTRAINT [DF_tblEventSchedules_ModifiedBy] DEFAULT ('System'),
--[DeletedBy] [nvarchar](100) NULL,
--[Deleted] [bit] NOT NULL CONSTRAINT [DF_tblEventSchedules_Deleted] DEFAULT ((0)),
--[Active] [bit] NOT NULL CONSTRAINT [DF_tblEventSchedules_Active] DEFAULT ((1)),
--[Hide] [bit] NOT NULL CONSTRAINT [DF_tblEventSchedules_Hide] DEFAULT ((0)),
--[SortOrder] [int] NOT NULL CONSTRAINT [DF_tblEventSchedules_SortOrder] DEFAULT ((0)),
-- CONSTRAINT [PK_tblEventSchedules] PRIMARY KEY CLUSTERED
--(
--[EventScheduleID] ASC
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--) ON [PRIMARY]
November 3, 2009 at 12:53 pm
I don't understand what you're trying to resolve with this. I ran it, and got output, but I don't understand what the problem is, so don't know where to start resolving it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 5:09 pm
the problem is trying to solve the following:
The model for this sql code is MS Outlook's Recurring settings for the Week option and not the Day option or the Month option or the Year option.
create start and end dates persisted in a sql table depending on:
1) start date input by the user
2) and an end date input by a user
3) and the user selecting one or more days of the week, i.e. Monday and Wednesday
recap:
A user inputs Start and End dates and selects any of the days of the week, i.e. Monday and Wednesday.
I hope this make sense.
November 3, 2009 at 11:14 pm
Please post sample imputs and expected outputs. That would help us to better understand the problem.
November 4, 2009 at 1:01 am
I too have run this and get an output that appears correct. So I do not understand the problem. Also from your original post ... please explain "....when the start date is greater than the user selected days"
There is probably a better way to get the result.
November 4, 2009 at 5:54 am
without knowing your exact requirements this may be inappropriate
Instead of this massive loop why not simple use something like below, note:- Below is not a complete solution
Select
PossibleDate
From
(
----AllPossibleDays
Select top( Select DATEDIFF( day, @dtstartdate, @dtRangeEndDate ) +1 )
St = @dtStartDate
,FirstWeekDay = ( ROW_NUMBER() Over(order by name)-1 ) %7
,PossibleDay = ( ROW_NUMBER() Over(order by name)-1 )
,FirstWeekDate = DATEADD(day, ( ROW_NUMBER() Over(order by name)-1 ) %7, @dtstartdate )
,PossibleDate = DATEADD(day, ( ROW_NUMBER() Over(order by name)-1 ), @dtstartdate)
From sysColumns
) as a
Where power( 2, DATEPart( weekday, PossibleDate )-1 ) & @iDayMask > 0
And DATEDIFF( Week, FirstWeekDate, PossibleDate) % @iWeekFreq = 0
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply