Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in soling a SQL issue Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 7:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 820, Visits: 1,175
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)
Post #1463576
Posted Friday, June 14, 2013 8:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:22 PM
Points: 3,637, Visits: 7,935
With the DDL and sample data was easy to get a solution (even if you manage to have 30 hours days ).

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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1463623
Posted Friday, June 14, 2013 10:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 820, Visits: 1,175
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)

Post #1463664
Posted Friday, June 14, 2013 10:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:22 PM
Points: 3,637, Visits: 7,935
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?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1463669
Posted Friday, June 14, 2013 10:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 820, Visits: 1,175
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.


  Post Attachments 
SchResults_New.xlsx (2 views, 10.45 KB)
Post #1463685
Posted Friday, June 14, 2013 11:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 820, Visits: 1,175
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)



  Post Attachments 
SchResults with new Test Case.xlsx (2 views, 8.91 KB)
Post #1463702
Posted Saturday, June 15, 2013 10:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
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?
Post #1463878
Posted Saturday, June 15, 2013 3:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 820, Visits: 1,175
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.
Post #1463900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse