We help IT Professionals succeed at work.

Rental System - Date trouble

Fracture
Fracture asked
on
Medium Priority
291 Views
Last Modified: 2011-04-14

Hi All,

I am building a product rental system and have the following 3 tables -

RProduct:
ProductID (PK)
ProductName
ProductTypeID (FK)

RProductRental:
RentalID (FK)
ProductID (FK)

RRental:
RentalID (PK)
DateOut
DateIn

In the RRental Table the field "DateOut" represents the start date of the product rental period and "DateIn" is the return date. I have set the datatype of these fields as "datetime" in my database.

I am trying to retrieve all products that are not already booked out for a given set of dates.

I have created the following stored procedure

Create Procedure getRentalProductsAvailable --'22/04/2006', '26/04/2006'
@DateOut datetime,
@DateIn datetime
AS
Select * From RProduct
Inner Join RProductType ON RProduct.ProductTypeID = RProductType.ProductTypeID
Where RProduct.ProductID NOT IN(Select PR.ProductID From RProductRental PR
Inner Join RRental R ON PR.RentalID = R.RentalID
WHERE (@DateOut >= DateOut) And (@DateIn <= DateIn))

I seem to be getting errors on the date fields, i have tried using Cast and Convert and changed the datatypes but cant seem to run the procedure and get it to work.


Any help would be greatly appreciated.

Thanks in advance.

Fracture.
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
What error you are getting..

Create Procedure getRentalProductsAvailable --'22/04/2006', '26/04/2006'
@DateOut datetime,
@DateIn datetime
AS
SET DATEFORMAT DMY
Select * From RProduct
Inner Join RProductType ON RProduct.ProductTypeID = RProductType.ProductTypeID
Where RProduct.ProductID NOT IN(
Select PR.ProductID From RProductRental PR
Inner Join RRental R ON PR.RentalID = R.RentalID
WHERE (@DateOut >= DateOut) And (@DateIn <= DateIn))
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
when you call the procedure like this:
getRentalProductsAvailable '22/04/2006', '26/04/2006'

this might be the problem.

declare @datetin datetime
declare @dateout datetime
set @datetin = convert(datetime, '22/04/2006', 103)
set @datetout = convert(datetime, '26/04/2006', 103)
getRentalProductsAvailable @datein, @dateout

Commented:
First of all I would like to suggest you to pass dates in the format 'yyyy-mm-dd' becasue this is the standard format that is never going to confuse SQL server or your front end application.  
The filter in your statement is WHERE (@DateOut >= DateOut) And (@DateIn <= DateIn))
Now suppose data in the database is DateOut ='2006-03-23' and DateIn = '2006-03-20'.
Now if you parameters are as follows @DateOut = '2006-03-22' and @Datein='2006-03-21'

In this case the comparison in your WHEREclasue will be as follows
Where
(2006-03-22 >= 2006-03-23) --Logically false
           and
(2006-03-21 <= 2006-03-20) --Logically false

So effectively your where clause should be as follows
WHERE (@DateOut <= DateOut) And (@DateIn >= DateIn))

Let me know if I got it rright. cheers :)
CERTIFIED EXPERT
Top Expert 2011
Commented:
i think i'd do it this way...



create view VWproductRental
  as
 Select Productid, DateOut,DateIN
   from RproductRental as a
  Inner Join RRental as B
     on A.rentalID=B.RentalID
go

Create Procedure getRentalProductsAvailable --'22/04/2006', '26/04/2006'
@DateOut datetime,
@DateIn datetime
AS

Select @dateOut = Convert(varchar(11),@dateout)
      ,@DateIn=Convert(varchar(11),@dateIn)

/*
 
  I'd Adjust the date in and out date ranges here
  dependant on what the Dates actually represent...

  e.g. DateIn 22/04 at 9am   is that article available or not for the 22/04

    so I'd suggest modifying the search range here rather than altering the
    >= etc in the following select....    


*/


Select *
  From RProduct as P
 Inner Join RProductType as PT
    ON P.ProductTypeID = PT.ProductTypeID
  Left Outer Join        
       (select A.ProductID ,Max(a.datein) as DueIN, Min(b.DateOut) as DueOut
          from VWproductRental as A
          Left Outer Join VWproductRental as B
            on A.ProductID = B.ProductID
         Where Not exists (select RentalID from VWproductRental as x
                            where x.ProductID=a.ProductID
                              and (
                                     (dateout between @StartDate and @EndDate)
                                   or (datein  between @startdate and @enddate)
                                   or (dateout < @startDate and Datein > @startdate)
                                  )
                           )
           and A.DateIn <= @startdate
           and B.DateOut >= @enddate
         Group by A.ProductID
       ) as R
    on P.ProductID=R.productid

Return
Go

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:

Hi guys, thanks for all your replys, sorry for my delay in response.

Lowfatspread - I tried your suggestion but got the following error - "Invalid column name 'RentalID'." I'm sure its just a small bug but i have no idea why? I have not used Views before.

angelIII - I have taken your advice and implemented the convertion method you are using.

iloya - I think you are correct that my logic was very wrong, but i'm not so sure your logic suites the rental requirements i need either - i should have been more specific in my original post. I need to only return products that are not booked for rent for any two dates given, lets say product A is being rented from the 25/04 - 26/04, if the dates entered are 22/04 - 25/04 then Product A should not be displayed.

I have since come up with this -

ALTER Procedure getRentalProductsAvailable2 '22/04/2006', '26/04/2006', 1

@DateOut varchar(50),
@DateIn varchar(50),
@ProductTypeID INT

AS

Select * From RProduct
Inner Join RProductType ON RProduct.ProductTypeID = RProductType.ProductTypeID
Where RProduct.ProductTypeID = @ProductTypeID AND RProduct.ProductID NOT IN(Select PR.ProductID From RProductRental PR
Inner Join RRental R ON PR.RentalID = R.RentalID
WHERE ((convert(datetime, @DateOut, 103) >= DateOut) And (convert(datetime, @DateOut, 103) <= DateIn)) OR ((convert(datetime, @DateIn, 103) <= DateIn) And (convert(datetime, @DateOut, 103) >= DateOut)))

Logically this looks good to me (what do you guys think?) But this still doesnt seem to work. I think it may have something to do with the way im joining PR and R?
In the R table  i have rental id 1 and set the dates 24/04/2005 - 25/04/2005 and in the PR table i have products id's 1 and 2 relating to a rental id of 1. But when i pass in the dates of '22/04/2006', '26/04/2006' into the stored proc it still displays products 1 and 2 in the results.

Any ideas?

(*Phew* sorry about the novel!)


Fracture
CERTIFIED EXPERT
Top Expert 2011

Commented:
yes sorry the rentalid should have been a  productid column instead....


can't work out why your's doen't work...
but i notice you haven't cover the instance of a product never beeing rented before... (ie you need an outer join to the productrental table
at least

 
try this....

create view VWproductRental
  as
 Select Productid, DateOut,DateIN
   from RproductRental as a
  Inner Join RRental as B
     on A.rentalID=B.RentalID
go

Create Procedure getRentalProductsAvailable --'22/04/2006', '26/04/2006'
@DateOut datetime,
@DateIn datetime,
@producttype int
AS

Declare @startdate Datetime,@enddate datetime

Select @StartDate = Convert(varchar(11),@dateout)
      ,@EndDate=Convert(varchar(11),@dateIn)

/*
 
  I'd Adjust the date in and out date ranges here
  dependant on what the Dates actually represent...

  e.g. DateIn 22/04 at 9am   is that article available or not for the 22/04

    so I'd suggest modifying the search range here rather than altering the
    >= etc in the following select....    


*/


Select P.productid
      ,case when r.previous is null and r.next is null  
            Then space(22)+'************ FIRST'
            Else Coalesce(r.[previous],space(22)+'Not Required until>')
            end as [Previous Rental Period]    
      ,case when r.previous is null and r.next is null  
            Then 'RENTAL ****************'
            Else Coalesce(r.[next],'<No Future Rentals')
            end as [Next Rental Period]    

  From RProduct as P
 Inner Join RProductType as PT
    ON P.ProductTypeID = PT.ProductTypeID
  Left Outer Join        
       (select c.ProductID, --Max(a.datein) as DueIN, Min(b.DateOut) as DueOut

(select convert(char(19),dateout) + ' - ' + convert(char(19),datein)
  from vwproductrental as  x
 where x.dateout = (select max(dateout)
                   from vwproductrental as y
                  where y.dateout < @startdate
                    and x.productid=y.productid )                  
   and x.Datein = (select max(datein)
                  from vwproductrental as y
                  where y.datein <= @startdate
                    and x.productid=y.productid )
   and x.productid=c.productid) as Previous

,(select convert(char(19),dateout) + ' - ' + convert(char(19),datein)
  from vwproductrental as  x
 where x.dateout = (select min(dateout)
                   from vwproductrental as y
                  where y.dateout >= @enddate
                    and x.productid=y.productid )                  
   and x.Datein = (select min(datein)
                  from vwproductrental as y
                  where y.datein >= @enddate
                    and y.datein>x.dateout
                    and x.productid=y.productid )
   and x.productid=c.productid) as Next

          from VWproductRental as A
          right outer Join rproduct as c
             on a.productid = c.productid  
          Left Outer Join VWproductRental as B
            on A.ProductID = B.ProductID
         Where Not exists (select x.productid from VWproductRental as x
                            where x.ProductID=C.ProductID
                              and (
                                     (dateout between @StartDate and @EndDate)
                                   or (datein  between @startdate and @enddate)
                                   or (dateout < @startDate and Datein > @startdate)
                                  )
                           )
 
         Group by C.ProductID
       ) as R
    on P.ProductID=R.productid
  Where P.ProductTypeID= @productType
    and r.productid is not null

Return
Go



i used the following to test...
/*
drop table rproduct
drop table rproductrental
drop table rrental
drop table rproducttype
drop view vwproductrental
drop procedure getrentalproductsavailable
go
set nocount on

create table rproducttype
( producttypeid int)

create table RProduct  (
ProductID int ,
ProductTypeID int)

create table RProductRental  (
RentalID int,
ProductID int)

create table RRental  (
RentalID int,
DateOut datetime,
DateIn datetime)
*/

insert into rproducttype values(1)
Insert into rproduct values(1,1)
Insert into rproduct values(2,1)
Insert into rproduct values(3,1)
Insert into rproduct values(4,1)
Insert into rproduct values(5,1)
Insert into rproduct values(6,1)
insert into rproductrental values(1,1)
insert into rproductrental values(1,2)
insert into rproductrental values(2,4)
insert into rproductrental values(2,5)
insert into rproductrental values(3,5)
insert into rproductrental values(4,6)
insert into rrental values(1,'20060424','20060425')
insert into rrental values(2,'20060419','20060420')
insert into rrental values(3,'20060427','20060520')
insert into rrental values(4,'20060427','20060428')

go
--select * from vwproductrental

exec getRentalProductsAvailable '20060422','20060426',1


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.