Tricky Datetime manipulation problem

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)

Tricky Datetime manipulation problem

Author

Topic

t0mm0
Starting Member

3 Posts

Posted - 2007-08-20 : 20:41:06

I'm hoping that I can get some pointers to similar scripts or just ideas to help get me started with this.

Background: See the diagram of a mock-up below. Master table is basically a template for what will go into the child table. Child table really holds an instance at a particular date. The dates that the Child table will hold for any Master record are determined by what days of the week (int) in the DayOfWeek table are associated with the master record and what weekly frequency is stated in the Master table.

For example a Master record may have associated DayOfWeek records of 4 and 6 and an EachWeek value of 2. In this case Child table should contain records with date values for this MasterID falling on Wednesday and Friday of every second week (projected out 28 days from now).

My aim is to have server agent run a procedure at regular intervals that ensures that the child records are complete (28 days in advance)according to the schedule defined in Master and DayOfWeek tables. I will also need to have another colum in Master that identifies where the current week sits in relation to the EachWeek value.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 02:42:39

Something similar to this. Maybe we can give a better solution if you provided some proper sample data and your expected output based on the sample data.

SELECT		wd.ChildID
FROM		(
			SELECT		c.ChildID,
					m.MasterID
			FROM		Master AS m
			INNER JOIN	Child AS c ON c.MasterID = m.MasterID
			INNER JOIN	DayOfWeek AS dow ON dow.MasterID = m.MasterID
			WHERE		dow.DayOfWeek <> DATEPART(WEEKDAY, c.Date)
		) AS wd
INNER JOIN	(
			SELECT		m.MasterID
			FROM		Master AS m
			INNER JOIN	DayOfWeek AS dow ON dow.MasterID = m.MasterID
			GROUP BY	m.MasterID
			HAVING		COUNT(dow.DayOfWeek) <> m.EachWeek
		) AS v ON v.MasterID = wd.MasterID
WHERE		c.Date >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
		AND c.Date < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 29)

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

t0mm0
Starting Member

3 Posts

Posted - 2007-08-21 : 10:05:30

I'll try to explain what I'm trying to do a bit better.

Master Table
(one record only)
MasterID 1
EachWeek 2 (means every 2nd week)
Last Scheduled Date 17/08/2007 (added cause we need to know where in the cycle we are for Master records when EachWeek >1)

DayOfWeek Table

(record numebr 1)
DayOfWeek 4
MasterID 1

(record numebr 2)
DayOfWeek 6
MasterID 1

We'll say there are no records in the Child table.
I want records with these dates inserted into the Child table:

29/08/2007, 31/08/2007, 12/09/2007, 14/09/2007

(all MasterIDs are 1 obviously)

... these are Wednesdays and Fridays of alternating weeks within 28 days of today (22/08/2007). We know to start with next week because the 'last scheduled date' is within last week.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 10:18:58

[code]-- prepare sample data
set dateformat dmy

declare @master table (masterid int, eachweek tinyint, lastscheduleddate smalldatetime)

insert @master
select 1, 2, '17/08/2007'

declare @dayofweek table (dayofweek tinyint, masterid int)

insert @dayofweek
select 4, 1 union all
select 6, 1

-- Show the expected output
SELECT m.MasterID,
DATEADD(DAY, v.Number, m.LastScheduledDate) AS theDate
FROM master..spt_values as v
CROSS JOIN @Master AS m
INNER JOIN @DayOfWeek AS dow ON dow.MasterID = m.MasterID
WHERE v.Type = 'p'
AND v.Number BETWEEN 1 AND 28
AND v.Number % m.EachWeek = 0
AND DATEPART(WEEKDAY, DATEADD(DAY, v.Number, m.LastScheduledDate)) = dow.DayOfWeek[/code]

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

t0mm0
Starting Member

3 Posts

Posted - 2007-08-22 : 22:26:39

Awesome thanks. I'm new to SQL so maybe I have a gap in my knowledge but I understand everything you've doing there apart from this line

AND v.Number % m.EachWeek = 0

how does this work?

Also: How do I insert this record set into Child table only when there is not an existing record for the same MasterID and on the same date?

would use this function to find the dates to compare:

create function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
go

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 03:43:47

[code]-- prepare sample data
set dateformat dmy

declare @master table (masterid int, eachweek tinyint, lastscheduleddate smalldatetime)

insert @master
select 1, 2, '17/08/2007'

declare @dayofweek table (dayofweek tinyint, masterid int)

insert @dayofweek
select 4, 1 union all
select 6, 1

declare @child table (childid int, masterid int, date smalldatetime)

-- Show the expected output
INSERT @Child
(
MasterID,
Date
)
SELECT m.MasterID,
DATEADD(DAY, v.Number, m.LastScheduledDate) AS theDate
FROM master..spt_values as v
CROSS JOIN @Master AS m
INNER JOIN @DayOfWeek AS dow ON dow.MasterID = m.MasterID
LEFT JOIN @Child AS c ON c.MasterID = m.MasterID AND c.Date = DATEADD(DAY, v.Number, m.LastScheduledDate)
WHERE v.Type = 'p'
AND v.Number BETWEEN 1 AND 28
AND v.Number % m.EachWeek = 0
AND DATEPART(WEEKDAY, DATEADD(DAY, v.Number, m.LastScheduledDate)) = dow.DayOfWeek
AND c.ChildID IS NULL[/code]

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