June 14, 2013 at 7:49 am
Hi ,
I am trying to get the schedule for a resource. It may have multiple weekly schedules & onetime schedule.
If it is onetime schedule it always take reference over weekly.
But while checking the records for a resource we have to make sure there exists at least one row for IsResSchedule = 1.
CREATE TABLE [dbo].[W_O_Schedule](
[LocationId] [int] NOT NULL,
[resourceid] [int] NOT NULL,
[availability] [varchar](11) NOT NULL,
[ScheduleRepeat] [varchar](7) NOT NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
IsResSchedule int
) ON [PRIMARY]
GO
Insert into [W_O_Schedule]
Values
--Only row 1 and 2 from here
(1, 2,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 12:00:00',0),
(1, 2,'Available','Weekly','2000-01-03 13:00:00','2000-01-03 17:00:00',0),
(1, 2,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 17:00:00',1),
--From the below 2 I wasnt only row with 'Unavailable'
(1,4,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 17:00:00',1),
(1,4,'Unavailable','OneTime','2013-06-03 00:00:00','2013-06-04 00:00:00',0),
--Nothing from here since IsResSchedule = 0 for both rows
(1,5,'Available','Weekly','2013-06-03 00:00:00','2013-06-03 20:00:00',0),
(1,5,'Available','OneTime','2013-06-03 00:00:00','2013-06-03 30:00:00',0)
--If I want to get schedule for '2013-06-03' for resource 2, 4 & 5
--My result sould be
(1, 2,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 12:00:00',0)
(1, 2,'Available','Weekly','2000-01-03 13:00:00','2000-01-03 17:00:00',0)
(1,4,'Unavailable','OneTime','2013-06-03 00:00:00','2013-06-04 00:00:00',0)
June 14, 2013 at 8:47 am
With the DDL and sample data was easy to get a solution (even if you manage to have 30 hours days :-D).
It's not the only way, but it can do the job.
WITH CTE AS(
SELECT LocationId,
resourceid,
COUNT( DISTINCT IsResSchedule) ResScheduleCount
FROM #W_O_Schedule
GROUP BY LocationId,
resourceid)
SELECT WOS.*
FROM #W_O_Schedule WOS
JOIN CTE ON WOS.LocationId = CTE.LocationId
AND WOS.resourceid = CTE.resourceid
WHERE CTE.ResScheduleCount > 1
AND IsResSchedule = 0
June 14, 2013 at 10:11 am
Thank you very much Luis for looking into this.
This is very helpful. Sorry about 30 hours in a day.
I was just testing it and it looks very promising, but is failing for the below mentioned case.
In this case there are no rows where IsResSchedule = 1 but I am getting 2.
If I change the where IsResSchedule = 0 to AND IsResSchedule = 1 , the results for Resource 2 gets bad.
Again thank you very much for your help.
Insert into [W_O_Schedule]
Values
(3,4,'Available','Weekly','2000-01-04 09:00:00.000','2000-01-04 17:00:00.000',0),
(3,4,'Available','Weekly','2000-01-04 09:00:00.000','2000-01-04 17:00:00.000',1),
(3,4,'Unavailable','OneTime','2013-06-04 00:00:00.000','2013-06-05 00:00:00.000',0),
(3,4,'Unavailable','OneTime','2013-06-04 00:00:00.000','2013-06-05 00:00:00.000',1)
June 14, 2013 at 10:22 am
I can't understand the problem. You say there are no rows where IsResSchedule = 1 , but the code for the new sample data shows 2 rows with IsResSchedule = 1.
Could you explain some more or give sample data with the expected results for that particular case just as you did on your original post?
June 14, 2013 at 10:45 am
Sorry about that. Let me try to explain it more.
Location may be wide open, but resource schedule may be limited or vice- versa. In that case I have to select which one is restricted.
Ex:-Location 1 , Resource2, Resource Sch is wide open but location Sch is 9-12 & 13-17 which we have to take.
Another case : If either location or schedule any of them is unavailable then, it should be unavailable.
Ex :- Location1, Resource 4,
Another : - Both Location & Resource have weekly & onetime, but one time is unavailable , so we have to take one time only.
Location3, Resource 4
I also have attached excel from the data & results which I would be expecting.
Again thank you very much for putting your time to look into it.
June 14, 2013 at 11:05 am
Luis,
Here is the new set of data just on one location & sheet with expected results, if that helps.
Insert into [W_O_Schedule] Values
--Nothing from here since No Location Schedule
--SO Nothing
(1,1,'Available','Weekly','2013-06-03 00:00:00','2013-06-03 20:00:00',1),
(1,1,'Available','OneTime','2013-06-03 00:00:00','2013-06-03 20:00:00',1),
--Nothing from here since IsResSchedule = 0 for both rows --No ResourceSchedule
--SO Nothing
(1,2,'Available','Weekly','2013-06-03 00:00:00','2013-06-03 20:00:00',0),
(1,2,'Available','OneTime','2013-06-03 00:00:00','2013-06-03 20:00:00',0),
--From the below 2 I wasnt only row with 'Unavailable'
(1,3,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 17:00:00',1),
(1,3,'Unavailable','OneTime','2013-06-03 00:00:00','2013-06-04 00:00:00',0),
--Only row 1 and 2 from here Resource wide open but Location is restricted
(1, 4,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 12:00:00',0),
(1, 4,'Available','Weekly','2000-01-03 13:00:00','2000-01-03 17:00:00',0),
(1, 4,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 17:00:00',1),
-- Location wide open but Resource is restricted row 1 & 2 only
(1, 5,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 12:00:00',1),
(1, 5,'Available','Weekly','2000-01-03 13:00:00','2000-01-03 17:00:00',1),
(1, 5,'Available','Weekly','2000-01-03 09:00:00','2000-01-03 17:00:00',0),
-- Only Unavailable row
(1,6,'Available','Weekly','2000-01-04 09:00:00.000','2000-01-04 17:00:00.000',0),
(1,6,'Available','Weekly','2000-01-04 09:00:00.000','2000-01-04 17:00:00.000',1),
(1,6,'Unavailable','OneTime','2013-06-04 00:00:00.000','2013-06-05 00:00:00.000',0),
(1, 6,'Unavailable','OneTime','2013-06-04 00:00:00.000','2013-06-05 00:00:00.000',1)
June 15, 2013 at 10:43 am
Its still pretty tough to understand all your business rules here. I think its probably just a weird data model throwing this out of whack, and location and resource schedules would ideally have separate tables, with a many to many table in between guiding the relationship.
But let me try to summarize.
So you only want to see results that have both a Location Schedule (IsResSchedule = 0) and a Resource Schedule (ISResSchedule =1).
Of those results, you only want to see the MOST LIMITING schedules for the pairing. So if location is only available part of the day, but resource all day, you want to see location. And if the opposite is true you want resource.
If there is a ONETIME schedule, you want to see it and not the WEEKLY schedules.
This still leaves some open questions.
What if neither Location nor Resource schedule are limited. Show location or show resource or show both? (Or show one but it doesn't matter which).
What if BOTH Location and Resource are limited? Show both? Show neither? Show intersection of the schedule times?
Can there be multiple ONETIME schedule records? In all your examples there is only ever one to show per pairing. Is this ever not the case?
What happens is the Location has a limited WEEKLY schedule but the Resource has a wide open ONETIME schedule?
June 15, 2013 at 3:10 pm
Nevyn,
First of all thank you very much for looking into it.
You got all business rules right.
You have rightly answered all the questions but let me answer them again..
What if neither Location nor Resource schedule are limited. Show location or show resource or show both? (Or show one but it doesn't matter which). – Show any one.
What if BOTH Location and Resource are limited? Show both? Show neither? Show intersection of the schedule times?- Show intersection of scheduled times.
Can there be multiple ONETIME schedule records? In all your examples there is only ever one to show per pairing. Is this ever not the case? – Yes multiple records for same day with breaks.
What happens is the Location has a limited WEEKLY schedule but the Resource has a wide open ONETIME schedule? - the MOST LIMITING schedules for the pairing.
I am banging my head against the wall since last 2 days, but couldn't figure out how to do it. So posted it here, hoping that somebody might give me some idea of which direction I should go.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply