Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    5

    Unanswered: Check available Time procedure

    Hey Guys,
    I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table.
    Example User enters today's date and 5:30 AM and the search for all available techs to perform that task

    the tables ddl is
    USE [Schedule]
    GO
    /****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[AllDays](
    [ID] [int] NOT NULL,
    [DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    USE [Schedule]
    GO
    /***Appointments Table where trouble is *****
    /****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Appointments](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Customer_ID] [int] NOT NULL,
    [Tech_ID] [int] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [App_Date] [datetime] NOT NULL,
    [Created_By] [int] NOT NULL,
    [Date_Created] [datetime] NOT NULL,
    [Sku_ID] [int] NOT NULL,
    [Comment_ID] [int] NOT NULL,
    CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    USE [Schedule]
    GO
    ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID])
    REFERENCES [dbo].[Comments] ([ID])
    GO
    ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID])
    REFERENCES [dbo].[Techs] ([ID])

    USE [Schedule]
    GO
    /****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Schedule](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Tech_ID] [int] NOT NULL,
    [AllDayID] [int] NOT NULL,
    [ShiftStartTime] [datetime] NOT NULL,
    [ShiftEndTime] [datetime] NOT NULL,
    CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    USE [Schedule]
    GO
    ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID])
    REFERENCES [dbo].[AllDays] ([ID])

    Plus the Techs Table which holds their ID's names etc
    I have a snapshot of the relationship posted here if that can help

    Thanks for any input

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Define "first available tech".
    Does an appointment have to fall completely within a single tech's shift?
    Can an appointment span a day?
    And why are you using varchar to store date values?

    Look, a basic query would look like this:

    Code:
    select	top 1 Schedule.Tech_ID
    from	Schedule
    	inner join Appointments
    		on Schedule.ShiftStartTime <= Appointments.StartTime
    		and Schedule.ShiftEndTime <= Appointments.EndTime
    where	not exists
    	(select	*
    	from	Appointments Committed
    	where	Commited.TechID = Schedule.Tech_ID
    		and	((Committed.ShiftStartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
    			or
    			(Committed.ShiftEndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
    			or
    			(Appointments.StartTime BETWEEN Committed.ShiftStartTime and Committed.ShiftEndTime)))
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2006
    Posts
    5
    Thanks for the reply blindman,
    What I meant by first tech can be best explained with this scenario.
    End user selects date as 06/02/2006 at 10:00 AM then search. the query should return all available techs for that date and period of time and the closest time available if 10:00 AM is not available on that day.
    Example*******************
    Tech available from date available
    Tech1 10:00 AM 06/02/2006
    Tech2 11:00 AM 06/02/2006
    tech2 9:00 AM 06/02/2006
    ************************
    now as for your question
    >Does an appointment have to fall completely within a single tech's shift?
    Yes, all techs will be required to reschedule their appointment if they cant finish them on day assigned.
    Can an appointment span a day?
    No they shouldnt, its just not within the nature of the business to do this.
    And why are you using varchar to store date values?
    I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

    I hope that answers your questions.

    Thanks for your time

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ecreations
    ...
    And why are you using varchar to store date values?
    I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.
    "[DayString] [varchar](20) "??

    Will the logic I gave you in my sample code work for your situation?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2006
    Posts
    5
    "[DayString] [varchar](20) "??
    Oh this table hold an integer representing the day and DayString is the day itself.
    **************************
    ID DayString
    1 Sunday
    2 Monday
    **************************
    I tried to run the SQL the following
    select top 1 Schedule.Tech_ID
    from Schedule
    inner join Appointments
    on Schedule.ShiftStartTime <= Appointments.StartTime
    and Schedule.ShiftEndTime <= Appointments.EndTime
    where not exists
    (select *
    from Appointments Committed
    where Committed.Tech_ID = Schedule.Tech_ID
    and ((Committed.StartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
    or
    (Committed.EndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
    or
    (Appointments.StartTime BETWEEN Committed.StartTime and Committed.EndTime)))
    the result was no records. Am I missing something here?
    Thanks for your help

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Logic error. Should have been:
    Code:
    select top 1 Schedule.Tech_ID
    from Schedule
    inner join Appointments
    on Schedule.ShiftStartTime <= Appointments.StartTime
    and Schedule.ShiftEndTime >= Appointments.EndTime 
    .........
    ...to find shifts that bracket the appointments.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    May 2006
    Posts
    5
    Thanks Blindman :-)
    After the I changed the script to what you recommended, The statements returned one row.
    Now the stupid question, Is it possible to return the top 1 then the closest available techs as well?
    IE, If user enters 12/02/2007 10:30 AM and we have tech1 who starts at 9:30 AM on that day and have no appointment but tech2 starts 12:00 PM. and have an appointment starting 12/02/2007 1:30 PM and ending 12/02/2007 4:00 PM. is it possible to list tech2 with his/her availablity. this way the user doesnt have to go back and query the database again by changing date and time.

    second stupid question and that is related to my lack of experience in SQL is how can send this SP the date and time to look for.

    Thank you very much for your help.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In answer to your first question, just about anything can be done in SQL. But you are getting into some fairly complex business logic that is particular to your application. It will require a substantial amount of effort in requirements gathering and programming to get this right. You either need to get educated on SQL fast, or hire a contractor to do the work for you.

    To answer your second question, the datetime value can be sent to the stored procedure as a parameter. Please read about stored procedures in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2006
    Posts
    5
    Thank you for all your help blindman
    You have been great

    I'll be hitting the book store today!

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hit Books Online first. It's free.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •