[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Rental System - Date trouble


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.
0
Fracture
Asked:
Fracture
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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))
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
iloyaCommented:
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 :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
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
0
 
FractureAuthor 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
0
 
LowfatspreadCommented:
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


0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now