April 6, 2009 at 3:13 pm
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
April 6, 2009 at 4:50 pm
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 104/06/20092Skip location this week
machine 204/13/20092Service this machine
machine 304/07/20094Skip location this week
machine 404/20/20093Skip location this week
Lowell
April 7, 2009 at 12:49 am
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
Change is inevitable... Change for the better is not.
April 7, 2009 at 12:58 pm
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
WHEN (datepart(week, @Date) % RecurrenceInterval) = 0
THEN 'Service'
ELSE 'Skip'
END = 'Service'
April 7, 2009 at 2:13 pm
Ended up figuring it out with a function to get the # intervals and 1 line in the where clause, had to take a step back from the sql and figure out the math first...
select
m.MachineID
from #Machine m
inner join #MachineSchedule ms on m.MachineScheduleID = ms.MachineScheduleID
inner join #SchedulePattern sp on ms.MachineScheduleID = sp.MachineScheduleID
where
--get schedule's date range.
(@Date between ms.BeginDate and ms.EndDate)
--get correct week.
and ((datepart(week, @Date) - datepart(week, ms.BeginDate)) % dbo.GetNumSchedulePatterns(ms.MachineScheduleID)) + 1 = sp.RecurrenceInterval
--get correct day.
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)
)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply