Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2002
    Posts
    16

    Unanswered: Checkout /Checkin - dates Query Help

    Hi All

    I need help with a query that tests for conflicts in the schedule of equipment checkout. It almost works but I have some problems when one checkout ends at the same time as another begins. For example I have a camera checked out from 9:00AM until 10:00AM on a particular day. When someone wants to checkout the camera from 10:00 until 10:30 , my query says it is already checked out because the start time of the new checkout is the same time as the return time on the previous checkout.

    I know there has to be a better way than what I've got but I don't know how.

    Thanks in advance
    Russ


    Here is my table:
    CREATE TABLE [Equipment_checkout] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [Equipment_id] [int] NULL ,
    [checkout_date] [datetime] NULL ,
    [return_date] [datetime] NULL ,
    [username] [varchar] (255) NULL ,
    [event_id] [int] NULL ,
    [recur_id] [int] NOT NULL DEFAULT (0)
    )

    Here is my query:

    SELECT ec.[Equipment_id], ec.[checkout_date], ec.[return_date], ec.[username],
    ec.[id], ec.[event_id],eq.[Equipment]FROM [Equipment_checkout] ec

    INNER JOIN [Equipment]eq ON eq.[Id]= ec.[Equipment_id]

    WHERE equipment_id = 5
    AND event_id <> 6

    AND ((DateDiff(mi,ec.[checkout_date],'11/12/2004 10:00 AM')> 0 and DateDiff(mi,ec.[return_date],'11/12/2005 10:30 AM')< 0)
    or (DateDiff(mi,ec.[checkout_date],'11/12/2004 10:00 AM')<=0 and DateDiff(mi,ec.[checkout_date],'11/12/2005 10:30 AM')> 0 and DateDiff(mi,ec.[return_date],'11/12/2005 10:30 AM') <= 0 )
    or (DateDiff(mi,ec.[checkout_date],'11/12/2004 10:00 AM')> 0 and DateDiff(mi,ec.[return_date],'11/12/2004 10:00 AM')<0)
    or (DateDiff(mi,ec.[checkout_date],'11/12/2004 10:00 AM')<=0 and Datediff(mi,ec.[return_date],'11/12/2005 10:30 AM') >= 0))



    Here is the data that comes back: (NOTE THE ORDER OF MY SELECT IS NOT THE SAME AS THE TABLE!)
    5 2004-11-17 09:00:00.000 2004-11-17 10:00:00.000 jscopp 7750 8458 Center Conference Room
    5 2004-11-24 09:00:00.000 2004-11-24 10:00:00.000 jscopp 7751 8459 Center Conference Room
    5 2004-12-01 09:00:00.000 2004-12-01 10:00:00.000 jscopp 7752 8460 Center Conference Room
    5 2004-12-08 09:00:00.000 2004-12-08 10:00:00.000 jscopp 7753 8461 Center Conference Room
    5 2004-12-15 09:00:00.000 2004-12-15 10:00:00.000 jscopp 7754 8462 Center Conference Room
    5 2004-12-22 09:00:00.000 2004-12-22 10:00:00.000 jscopp 7755 8463 Center Conference Room
    5 2004-12-29 09:00:00.000 2004-12-29 10:00:00.000 jscopp 7757 8465 Center Conference Room
    Last edited by CaptainEstock; 11-18-04 at 10:32. Reason: Typos

Posting Permissions

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