overlapping problem

Question

Post reply

overlapping problem

natalie_orlovsky

Mr or Mrs. 500

Points: 535
More actions
October 19, 2010 at 3:33 pm

#230643

Hi guys,
I have a table resource_schedule
fields resource_id int,startime int schedule varchar(36)
and other table appointments
appointment_id int
resource_id int
start_time int
end_time int
I need to show appointments which overlapping with technician(resource_id) time
field schedule in resource_schedule has data like this
1111111111111111111111111111111111110000000000001111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111110000
when is 0 that mean resource not working,1 working time
first 1 =540 start_time
in appointment table I have
appointment_id start_time end_time resource_id
4537562 6006304315363
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Is there a question?

Please see the link in my signature and reformulate your question. Help us to help you.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

natalie_orlovsky Mr or Mrs. 500 Points: 535 More actions · Answer 2

Yes, it is a question.

I need to show in my report,overlapping appointments - appointments books on break/lucn etc time

it is my tables

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[appointment_line](

[appointment_line_id] [bigint] NOT NULL,

[appointment_id] [bigint] NOT NULL,

[resource_id] [bigint] NOT NULL,

[service_type_id] [bigint] NOT NULL,

[start_time] [int] NOT NULL,

[pretime] [int] NOT NULL,

[aftertime] [int] NOT NULL,

[duration] [int] NOT NULL,

[end_time] [int] NOT NULL,

[associate_resource_id] [bigint] NULL,

[price] [decimal](10, 4) NULL,

[parent_id] [bigint] NULL,

[lastupdated] [datetime] NOT NULL,

[updatedby] [bigint] NOT NULL,

[concurrent_ts] [bigint] NOT NULL,

[cid] [int] NOT NULL,

[appointment_line_type_id] [bigint] NOT NULL DEFAULT (501),

[equipment_id] [bigint] NULL,

[authorizedby] [bigint] NULL,

[rule_id] [int] NULL,

[package_template_line_id] [bigint] NULL,

[facility_id] [bigint] NULL,

[process_time] [smallint] NOT NULL DEFAULT (0),

[override_equipment] [bit] NOT NULL DEFAULT (0),

[created_date] [datetime] NOT NULL,

CONSTRAINT [PK_appointment_line_1] PRIMARY KEY CLUSTERED

(

[appointment_line_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO/****** Object: Table [dbo].[resource_daily_schedule] Script Date: 10/20/2010 08:48:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[resource_daily_schedule](

[resource_id] [bigint] NOT NULL,

[year] [smallint] NOT NULL,

[day] [smallint] NOT NULL,

[schedule] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[starttime] [smallint] NOT NULL,

[isOpen] [bit] NOT NULL,

[cid] [int] NOT NULL,

[hasAppointment] [bit] NOT NULL,

CONSTRAINT [PK_resource_daily_schedule_1] PRIMARY KEY CLUSTERED

(

[resource_id] ASC,

[year] ASC,

[day] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

Data to insert to rsource_daily_schedule tbl

INSERT dbo.resource_daily_schedule SELECT 4315363,2010,365,'ÿÿÿÿð',540,1,8888,0

INSERT dbo.resource_daily_schedule SELECT 4315363,2010,364,'ÿÿÿÿð',540,1,8888,0

INSERT dbo.resource_daily_schedule SELECT 4315363,2010,363,'ÿÿÿÿð',540,1,8888,0

some data to appointmet tbl

INSERT dbo.appointment_line SELECT 4537563,4537562,4315363,4240815,600,0,0,30,630,NULL,25.0000,NULL,'2010-10-19 15:50:57.820',4481728,1,8888,601,NULL,NULL,NULL,NULL,4299730,0,0,'2010-10-19 14:46:18.700'

INSERT dbdbo.appointment_line SELECT 4537567,4537565,4315363,4332180,705,0,0,60,765,NULL,100.0000,NULL,'2010-10-19 14:50:41.160',4481728,1,8888,601,NULL,4238922,1278,NULL,NULL,00,'2010-10-19 14:51:07.500'

INSERT dbo.appointment_line SELECT 4537593,4537592,4315363,4271007,810,0,0,15,825,NULL,25.0000,NULL,'2010-10-19 15:50:38.930',4481728,1,8888,601,NULL,NULL,NULL,NULL,NULL,30,0,'2010-10-19 15:17:41.857'

and it is function to decode the schedule filed in the resource_daily_schedule tbl

****** Object: UserDefinedFunction [dbo].[FN_DecodeSchedule] Script Date: 10/20/2010 09:28:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[FN_DecodeSchedule]

(

@schedule varchar(36)

)

returns varchar(288)

as

BEGIN

DECLARE @decoded_schedule varchar(288), @pos smallint, @C char(1)

SET @decoded_schedule = ''

WHILE len(@schedule) >0

BEGIN

SET @C = substring(@schedule, 1, 1)

SET @schedule = substring(@schedule, 2, len(@schedule) -1)

SET @decoded_schedule = @decoded_schedule + dbo.FN_CharToBinary(@c)

END

--IF (LEN(@decoded_schedule) > @len)

--SET @decoded_schedule = SUBSTRING(@decoded_schedule, 1, @len)

RETURN @decoded_schedule

END

I am really desporte with that

Thanks you

WayneS SSC Guru Points: 95460 More actions · Answer 3

natalie,

First, thanks for posting the CREATE TABLE and INSERT statements - believe it or not, it really does help us.

Second, the most likely reason that people haven't been helping you is that they just don't understand what you are trying to do. One thing that does help is if you were to provide us what the expected output is, based upon the sample data that you provided. Can you do this for us please?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

natalie_orlovsky Mr or Mrs. 500 Points: 535 More actions · Answer 4

Hi Wayne,

for example I have an appointment start at 11 :45 AM finish at 1PM,but resource(technician) at 12:30 has lunch,,that mean appointment has overlap.that info in schedule fields ,when it is 0,I break my head how I can calculate that!I now .fist 1 in that field(data looks like 1111110001...)-540(9AM),and after that each 1 or 0 =5min.

Thanks

natalie_orlovsky Mr or Mrs. 500 Points: 535 More actions · Answer 5

Hi gurus,

I am really need help

How I can found start and end positions for all '0' which appier in the string.

I have a string for example schedule ='1111111111111111111111111111111111110000000000001111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111110000'

I try to select schedule, resource_id,charindex('0',schedule)as pos

,charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule)))

,substring(schedule,charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))),len(schedule)-charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))))

--540 + 5*(charindex('0',schedule) + charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))))

,charindex('0',substring(schedule,charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))),len(schedule)-charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule)))))

--,charindex('0',substring(schedule,charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))),len(schedule)-charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule)))))

,substring(substring(schedule,charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))),len(schedule)-charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule)))),

charindex('0',substring(schedule,charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))),len(schedule)-charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))))),len(schedule)-charindex('0',substring(schedule,charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule))),len(schedule)-charindex('1',substring(schedule,charindex('0',schedule),len(schedule)-charindex('0',schedule)))))).

Please help!!!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

OK seriously, you have to formulate your questions better.

We are all unpaid volunteers here. Help us to help you.

I really think most of us won't even try to read/understand your last post, just because it is such a mess. You'd have to spent at least 30 minutes trying to understand your question, and then we haven't spent any time yet actually solving the issue.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

natalie_orlovsky Mr or Mrs. 500 Points: 535 More actions · Answer 7

I am sorry, but as it is even better to generate question?

I need to in the my string find a position of the first zero, in the beginning of the first block and a position of last zero in the same block then a position of the first zero in the following block, etc.