Querying based on a multi-week schedule

Question

Post reply

Querying based on a multi-week schedule

justin-976626

SSC Journeyman

Points: 97
More actions
April 6, 2009 at 3:13 pm

#132664

Hey all,

I need help writing this query, I've spent hours and still can't get my head around it... There are vending machines that need to be serviced on a schedule, and the schedule can have a begin and end date. Given a date, I need to figure out which machines need to be serviced on that date.

Here is the data structure to make things more clear:

Machine table - MachineID int, MachineScheduleID int

MachineSchedule table - MachineScheduleID int, BeginDate datetime, EndDate datetime

SchedulePattern table - SchedulePatternID int, MachineScheduleID int, RecurrenceInterval int, Sunday bit, Monday bit, Tuesday bit, Wednesday bit, Thursday bit, Friday bit, Saturday bit

If there was only a 1 week interval then it would be simple, I could just do this:

select m.MachineID

from dbo.Machine m

inner join dbo.MachineSchedule ms on m.MachineScheduleID = ms.MachineScheduleID

inner join dbo.SchedulePattern sp on ms.MachineScheduleID = sp.MachineScheduleID

where

(@Date between ms.BeginDate and ms.EndDate)

and (

(datename(weekday, @Date) = 'Monday' and sp.Monday = 1)

or (datename(weekday, @Date) = 'Tuesday' and sp.Tuesday = 1)

or (datename(weekday, @Date) = 'Wednesday' and sp.Wednesday = 1)

or (datename(weekday, @Date) = 'Thursday' and sp.Thursday = 1)

or (datename(weekday, @Date) = 'Friday' and sp.Friday = 1)

or (datename(weekday, @Date) = 'Saturday' and sp.Saturday = 1)

or (datename(weekday, @Date) = 'Sunday' and sp.Sunday = 1)

)

However the SchedulePattern table can have multiple records (notice the ScheduleInterval field which would be 1, 2, 3, etc...), as you might have a multi-week schedule, such as Monday/Wednesday of week 1 then Tuesday/Thursday of week 2, back to Monday/Wednesday for week 3, etc...

How do I write the query to handle multiple weeks??

Thanks,

Justin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

i think you could use something like this

select CASE

WHEN datepart(week,getdate()) %2 = 0

THEN 'Service this machine'

ELSE 'Skip location this week'

END

changing the getdate to your fieldname, and your interval(every week, two weeks,three weeks) to replace the modulus 2 operation;

s say you had Two machines, one that is supposed to be serviced on 04/06/2009, every other week, and the second machine is scheduled for servicing 04/13/2009, also every other week.

does this help get the ball rolling?



select machinename,ServiceDate,serviceinterval,CASE

           WHEN (datepart(week,ServiceDate) %serviceinterval) = 0

           THEN 'Service this machine'

           ELSE 'Skip location this week'

         END

from(select '04/06/2009' as ServiceDate,'machine 1' as machinename, 2 as serviceinterval union all

select '04/13/2009' as ServiceDate,'machine 2' as machinename, 2 as serviceinterval union all

select '04/07/2009' as ServiceDate,'machine 3' as machinename, 4 as serviceinterval union all

select '04/20/2009' as ServiceDate,'machine 4' as machinename, 3 as serviceinterval ) x

--results

machine 1	04/06/2009	2	Skip location this week

machine 2	04/13/2009	2	Service this machine

machine 3	04/07/2009	4	Skip location this week

machine 4	04/20/2009	3	Skip location this week

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Justin,

If that doesn't work out for you, take some time to read the link in my signature line below. A little data goes a long way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

justin-976626 SSC Journeyman Points: 97 More actions · Answer 3

Thanks Lowell, with your tips I was able to get headed in the right direction but am still stuck on this... I included temp tables and test data so that anyone can run the query. Thanks for any help on the issues below, I've spent a lot of time on this query and aren't making much progress.

Issue #1: Week 1 has a recurrenceinterval of 1 and anything mod 1 will return true so week 1's schedulepattern will always have a status of service even on off-weeks.

Issue #2: It picks up week 2 correctly only because week 1 is odd. The date below is 4/6, which returns a week of 15. 15 mod the recurrence interval of 1 is 0, so week 1 is returned (as it always will be, per issue #1). Then week 2 will be week 16, mod the recurrence interval of 2, will be 0. If week 1 is even than week 2 will be odd, then mod 2 will be 1, which won't return week 2 correctly.

Issue #3: The query below implies that the week of the date specified below will be week 1, when really week 1 should be based on the BeginDate in the #MachineSchedule table.

--test data

drop table #Machine

create table #Machine

(

MachineID int,

MachineScheduleID int

)

drop table #MachineSchedule

create table #MachineSchedule

(

MachineScheduleID int,

BeginDate datetime,

EndDate datetime

)

drop table #SchedulePattern

create table #SchedulePattern

(

SchedulePatternID int,

MachineScheduleID int,

RecurrenceInterval int,

Sunday bit,

Monday bit,

Tuesday bit,

Wednesday bit,

Thursday bit,

Friday bit,

Saturday bit

)

insert into #Machine (MachineID, MachineScheduleID)

values (1, 1)

insert into #MachineSchedule (MachineScheduleID, BeginDate, EndDate)

values (1, '2009/04/06', '2010/04/06')

insert into #SchedulePattern (SchedulePatternID, MachineScheduleID, RecurrenceInterval, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)

values (1, 1, 1, 0, 1, 0, 0, 0, 0, 0)

insert into #SchedulePattern (SchedulePatternID, MachineScheduleID, RecurrenceInterval, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)

values (2, 1, 2, 0, 0, 1, 0, 0, 1, 0)

--Week 1: M / Week 2: TuF

--4/6 (pattern 1), 4/14 (pattern 2), 4/17 (pattern 2), 4/20 (pattern 1), 4/28 (pattern 2), 5/1 (pattern 2), etc...

declare @Date datetime

set @Date = '4/6/2009'

select

m.MachineID,

ms.BeginDate,

ms.EndDate,

datepart(week, @Date) Week,

datepart(week, @Date) % RecurrenceInterval WeekModInterval,

CASE

WHEN (datepart(week, @Date) % RecurrenceInterval) = 0

THEN 'Service'

ELSE 'Skip'

END Status,

sp.*

from #Machine m

inner join #MachineSchedule ms on m.MachineScheduleID = ms.MachineScheduleID

inner join #SchedulePattern sp on ms.MachineScheduleID = sp.MachineScheduleID

where

(@Date between ms.BeginDate and ms.EndDate)

and (

(datename(weekday, @Date) = 'Monday' and sp.Monday = 1)

or (datename(weekday, @Date) = 'Tuesday' and sp.Tuesday = 1)

or (datename(weekday, @Date) = 'Wednesday' and sp.Wednesday = 1)

or (datename(weekday, @Date) = 'Thursday' and sp.Thursday = 1)

or (datename(weekday, @Date) = 'Friday' and sp.Friday = 1)

or (datename(weekday, @Date) = 'Saturday' and sp.Saturday = 1)

or (datename(weekday, @Date) = 'Sunday' and sp.Sunday = 1)

)

and CASE