Broadening the gap - 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)

Broadening the gap

Author

Topic

ultradiv
Starting Member

41 Posts

Posted - 2008-07-16 : 08:43:31

As your SQL far surpasses mine, I figured I would be cheeky and ask for more assistance

I'm working on a scheduling app.
the events are in one table and are mostly recurrent although there are one offs in this table too.
The recurring ones appear once. (as do the one off's)
The table is structured thus:

DECLARE @sample TABLE (
[cleanID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [int] NOT NULL ,
[RoundID] [int] NOT NULL ,
[CustomerID] [int] NULL ,
[CleanStart] [datetime] NULL ,
[NoSpecificTime] [int] NOT NULL,
[UseStart] [bit] NOT NULL,
[CleanBefore] [datetime] NULL ,
[UseBefore] [bit] NOT NULL,
[CleanAfter] [datetime] NULL ,
[UseAfter] [bit] NOT NULL,
[CleanLength] [int] NULL ,
[Rec] [int] NOT NULL,
[Rec_daily] [varchar] (50),
[Rec_weekly_int] [int] NOT NULL,
[Rec_monthly_select] [bit] NOT NULL,
[Rec_monthly_int] [int] NOT NULL,
[scheduleName] [varchar] (50))

INSERT @sample
SELECT 7,12,43,'09/12/2008 09:15:00',0,1,'00:00:00',0,'00:00:00',0,45,0,'',0,0,0,'One off with time' UNION ALL
SELECT 7,12,44,'08/09/2008 00:00:00',1,1,'00:00:00',0,'00:00:00',0,60,0,'',0,0,0,'One off with no time' UNION ALL
SELECT 7,12,45,'08/10/2008 00:00:00',0,0,'08/10/2008 09:00:000',1,'08/10/2008 18:00:000',1,30,0,'',0,0,0,'One off using before & after time' UNION ALL
SELECT 7,12,46,'08/10/2008 13:30:00',0,1,'00:00:00',0,'00:00:00',0,60,1,'1,2,3,4',0,0,0,'repeats daily on MTWT' UNION ALL
SELECT 7,12,46,'08/10/2008 14:30:00',0,1,'00:00:00',0,'00:00:00',0,60,2,'',2,0,0,'repeats weekly every other week' UNION ALL
SELECT 7,12,46,'011/10/2008 15:30:00',0,1,'00:00:00',0,'00:00:00',0,60,3,'',0,1,3,'repeats every 3 months on date' UNION ALL
SELECT 7,12,46,'12/10/2008 16:30:00',0,1,'00:00:00',0,'00:00:00',0,60,3,'',0,0,3,'repeats every 3 months on day in month'

Select * from @sample

This table holds the key to all events as
CleanStart holds the datetime (if recurring it is the starting point)
Rec defines recurrence
0=none,
1=dailey (Rec_daily holds string of possible days 1,2,3,4,5,6,7 - 1 being Monday)
2=weekly repeat (Rec_Weekly holds int x of every x weeks)
3=Monthly (Rec_monthly_select is true if recurrence uses the position in the month that this day ocurrurs else recurrence uses this date each month)(Rec_monthly_int holds int x of every x months)

The time is either:
In CleanStart When UseStart is true (If NoSpecificTime=1 then time will be 00:00:000)
OR
If UseStart is false then the UseBefore, UseAfter times are stored in Datetime in CleanBefore and CleanAfter respectivly and the bits for UseBefore and UseAfter will be true if they are to be used.
this gives 3 senarioes UseBefore but not after, UseAfter but not before and use both.

CleanLength holds the duration in minutes

Feel free to ask questions because I appreciate I may not be explaining it very well.

Now when the app comes to displaying this data it uses a one week at a time view, starting on Monday.

So the week is taken from a given date starting the Monday before.

The task is to list all the events for that week
with the output looking like this:

Listing * from WR_schedule but with CleanStart date being set to the actual datetime of the event

Now if the time is not specified or is a usebefore or useafter time then a time needs to be allocated to CleanStart from the given earliest and latest event times
See the previous thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106611

I think that’s all I can give without you guys asking me.

Many thanks for your valued help.
If you think that I am asking too much then please let me know, I really don’t want to be a pain!

Regards
Andy

ultradiv
Starting Member

41 Posts

Posted - 2008-07-16 : 10:31:48

I guess it is not very clear is it?
When I read what I have written it makes sense but maybe only to me because I have the system in my head already.

Do you think I should break it down into smaller bits?

Andy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 10:33:31

Yes, please.
Heimlich is a tricky maneuver.

E 12°55'05.25"
N 56°04'39.16"

ultradiv
Starting Member

41 Posts

Posted - 2008-07-16 : 10:47:22

Each event in the WR_Schedule table is described by columns in that table.
if it is a one off event then the column Rec will be =0
If the event recurs then the column Rec will be >0
if it recurs daily then Rec will be =1
if it recurs weekly 2
if monthly 3
each event will be defined by a date YYYY/MM/DD (column CleanStart)

This is either THE date (if the event is not recurring)
or
Is the base date (from which all recurrences will be extrapolated)

continues.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 10:49:13

So far so good.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 10:52:43

Some sample data to go along with would be nice.

E 12°55'05.25"
N 56°04'39.16"

ultradiv
Starting Member

41 Posts

Posted - 2008-07-16 : 10:56:33

Each recurrence type can be modified.

If Rec=1 (daily) then Rec_daily will hold a string like "1,3,5" which represents the days to recur on each week

If Rec=2 (weekly) the Rec_weekly will hold an integer up to 52 representing the recurring week e.g. every 26th week.

If Rec=3 (monthly) the we have two more options
1. If Rec_monthly_select=1 then the recurrence is by the DATE of the column CleanStart
2. If Rec_monthly_select=1 then the recurrence is by the DAY and the number of recurrences of that day (position of that day in the month, e.g. 2nd Wednesday or 4th Monday)
Plus Rec_monthly_int will hold an integer up to 12 representing the recurring month e.g. every 6th month.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 11:08:43

Something similar to this?

DECLARE	@Sample TABLE (EventName VARCHAR(20), CleanDate DATETIME, Rec INT)

INSERT	@Sample
SELECT	'Empty trash', '20080101', 2 UNION ALL	-- Weekly
SELECT	'Dishwasher',  '20080102', 1 UNION ALL	-- Daily
SELECT	'Wash car',    '20080103', 0 UNION ALL	-- One time only
SELECT	'Mowe lawn',   '20080104', 3		-- Monthly

DECLARE	@Today DATETIME,
	@Monday DATETIME,
	@Tuesday DATETIME,
	@Wednesday DATETIME,
	@Thursday DATETIME,
	@Friday DATETIME,
	@Saturday DATETIME,
	@Sunday DATETIME

SELECT	@Today = '20080705',
	@Monday = DATEADD(DAY, DATEDIFF(DAY, @Today, '19000101') % 7, @Today),
	@Tuesday = DATEADD(DAY, 1, @Monday),
	@Wednesday = DATEADD(DAY, 2, @Monday),
	@Thursday = DATEADD(DAY, 3, @Monday),
	@Friday = DATEADD(DAY, 4, @Monday),
	@Saturday = DATEADD(DAY, 5, @Monday),
	@Sunday = DATEADD(DAY, 6, @Monday)

SELECT	EventName,
	CASE
		WHEN Rec = 0 AND @Monday = CleanDate THEN 1
		WHEN Rec = 1 THEN 1
		WHEN Rec = 2 AND DATEDIFF(DAY, CleanDate, @Monday) % 7 = 0 THEN 1
		WHEN Rec = 3 AND DATEPART(DAY, CleanDate) = DATEPART(DAY, @Monday) THEN 1
		ELSE 0
	END AS Monday,
	CASE
		WHEN Rec = 0 AND @Tuesday = CleanDate THEN 1
		WHEN Rec = 1 THEN 1
		WHEN Rec = 2 AND DATEDIFF(DAY, CleanDate, @Tuesday) % 7 = 0 THEN 1
		WHEN Rec = 3 AND DATEPART(DAY, CleanDate) = DATEPART(DAY, @Tuesday) THEN 1
		ELSE 0
	END AS Tuesday,
	CASE
		WHEN Rec = 0 AND @Wednesday = CleanDate THEN 1
		WHEN Rec = 1 THEN 1
		WHEN Rec = 2 AND DATEDIFF(DAY, CleanDate, @Wednesday) % 7 = 0 THEN 1
		WHEN Rec = 3 AND DATEPART(DAY, CleanDate) = DATEPART(DAY, @Wednesday) THEN 1
		ELSE 0
	END AS Wednesday,
	CASE
		WHEN Rec = 0 AND @Thursday = CleanDate THEN 1
		WHEN Rec = 1 THEN 1
		WHEN Rec = 2 AND DATEDIFF(DAY, CleanDate, @Thursday) % 7 = 0 THEN 1
		WHEN Rec = 3 AND DATEPART(DAY, CleanDate) = DATEPART(DAY, @Thursday) THEN 1
		ELSE 0
	END AS Thursday,
	CASE
		WHEN Rec = 0 AND @Friday = CleanDate THEN 1
		WHEN Rec = 1 THEN 1
		WHEN Rec = 2 AND DATEDIFF(DAY, CleanDate, @Friday) % 7 = 0 THEN 1
		WHEN Rec = 3 AND DATEPART(DAY, CleanDate) = DATEPART(DAY, @Friday) THEN 1
		ELSE 0
	END AS Friday,
	CASE
		WHEN Rec = 0 AND @Saturday = CleanDate THEN 1
		WHEN Rec = 1 THEN 1
		WHEN Rec = 2 AND DATEDIFF(DAY, CleanDate, @Saturday) % 7 = 0 THEN 1
		WHEN Rec = 3 AND DATEPART(DAY, CleanDate) = DATEPART(DAY, @Saturday) THEN 1
		ELSE 0
	END AS Saturday,
	CASE
		WHEN Rec = 0 AND @Sunday = CleanDate THEN 1
		WHEN Rec = 1 THEN 1
		WHEN Rec = 2 AND DATEDIFF(DAY, CleanDate, @Sunday) % 7 = 0 THEN 1
		WHEN Rec = 3 AND DATEPART(DAY, CleanDate) = DATEPART(DAY, @Sunday) THEN 1
		ELSE 0
	END AS Sunday
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

ultradiv
Starting Member

41 Posts

Posted - 2008-07-16 : 11:51:30

Well it needs to be along those lines Pesco.

There is further modification to be made as per my previous post

This is example data of the base table from which all future recurrences are derived. I have labeled entry with a description (schedule_name) showing what it does.

DECLARE @sample TABLE (
[cleanID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [int] NOT NULL ,
[RoundID] [int] NOT NULL ,
[CustomerID] [int] NULL ,
[CleanStart] [datetime] NULL ,
[NoSpecificTime] [int] NOT NULL,
[UseStart] [bit] NOT NULL,
[CleanBefore] [datetime] NULL ,
[UseBefore] [bit] NOT NULL,
[CleanAfter] [datetime] NULL ,
[UseAfter] [bit] NOT NULL,
[CleanLength] [int] NULL ,
[Rec] [int] NOT NULL,
[Rec_daily] [varchar] (50),
[Rec_weekly_int] [int] NOT NULL,
[Rec_monthly_select] [bit] NOT NULL,
[Rec_monthly_int] [int] NOT NULL,
[scheduleName] [varchar] (50))

INSERT @sample
SELECT 7,12,43,'09/12/2008 09:15:00',0,1,'00:00:00',0,'00:00:00',0,45,0,'',0,0,0,'One off with time' UNION ALL
SELECT 7,12,44,'08/09/2008 00:00:00',1,1,'00:00:00',0,'00:00:00',0,60,0,'',0,0,0,'One off with no time' UNION ALL
SELECT 7,12,45,'08/10/2008 00:00:00',0,0,'08/10/2008 09:00:000',1,'08/10/2008 18:00:000',1,30,0,'',0,0,0,'One off using before & after time' UNION ALL
SELECT 7,12,46,'08/10/2008 13:30:00',0,1,'00:00:00',0,'00:00:00',0,60,1,'1,2,3,4',0,0,0,'repeats daily on MTWT' UNION ALL
SELECT 7,12,46,'08/10/2008 14:30:00',0,1,'00:00:00',0,'00:00:00',0,60,2,'',2,0,0,'repeats weekly every other week' UNION ALL
SELECT 7,12,46,'011/10/2008 15:30:00',0,1,'00:00:00',0,'00:00:00',0,60,3,'',0,1,3,'repeats every 3 months on date' UNION ALL
SELECT 7,12,46,'12/10/2008 16:30:00',0,1,'00:00:00',0,'00:00:00',0,60,3,'',0,0,3,'repeats every 3 months on day in month'

Select * from @sample

more to come....

ultradiv
Starting Member

41 Posts

Posted - 2008-07-16 : 11:55:11

now given a week in the future we need to pull out all the rows that occur during that week and replace the time element for that instance of the event if needed.

ultradiv
Starting Member

41 Posts

Posted - 2008-07-16 : 11:59:02

I have a plan to do this using a cursor and a VERY long winded way using reams of script! but I just know that you guys are smarter than that.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 14:59:46

Can we agree that I am the right track, before doing the small pieces like substituting the time part?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources