SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help in soling a SQL issue


Need help in soling a SQL issue

Author
Message
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1097 Visits: 1334
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)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16311 Visits: 19064
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




Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1097 Visits: 1334
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)


Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16311 Visits: 19064
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1097 Visits: 1334
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.
Attachments
SchResults_New.xlsx (6 views, 10.00 KB)
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1097 Visits: 1334
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)


Attachments
Nevyn
Nevyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1506 Visits: 3149
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?
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1097 Visits: 1334
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search