Dcnvi7Md

1-- Ex.7 a)
2drop procedure getCategoryCars
3go
4
5CREATE PROCEDURE getCategoryCars(@category int, @location int, @startTime datetime, @endtime datetime)
6as
7
8declare @notUsedBefore int
9--Set temporary table to save important data from bookings
10Create Table #TempInfoTable (
11id int identity,
12carId int,
13starttime datetime,
14endtime datetime,
15);
16
17Insert Into #TempInfoTable select car.carId, booking.starttime, booking.endtime from car inner join booking on car.carId = booking.carId 
18inner join location on location.locationId = car.locationid inner join category on category.categoryId = car.categoryId where location.locationId = @location and category.categoryId = @category
19
20set @notUsedBefore = (SELECT car.carId FROM car 
21inner join location on location.locationId = car.locationId 
22WHERE  car.categoryId = @category and car.locationId = @location and not exists (select * from booking where booking.carId = car.carId))
23
24
25delete from #TempInfoTable where #TempInfoTable.carId  in (select t.carId from #TempInfoTable t inner join booking on t.carId = booking.carId where @endtime between booking.starttime and booking.endtime 
26 or @starttime between booking.starttime and booking.endtime or booking.starttime between @starttime and @endtime or booking.endtime between @starttime and @endtime) 
27
28-- if there are cars that never had been booked before put them to temporary table with results
29if @notUsedBefore > 0 
30begin
31Insert Into #TempInfoTable select car.carId, 0,0 FROM car 
32inner join location on location.locationId = car.locationId 
33WHERE  car.categoryId = @category and car.locationId = @location and not exists (select * from booking where booking.carId = car.carId)
34end
35--print all the results
36select * from #TempInfoTable