WyMsMRA7

1-- Ex.7 a)
2go
3drop procedure getCategoryCars
4go
5
6CREATE PROCEDURE getCategoryCars(@category int, @location int, @startTime datetime, @endtime datetime)
7as
8
9declare @notUsedBefore int
10declare @isItFree int
11
12--find out if there are any  cars that did not have booking yet
13
14set @notUsedBefore = (SELECT COUNT(car.carId) FROM car 
15inner join location on location.locationId = car.locationId 
16WHERE  car.categoryId = @category and car.locationId = @location and not exists (select * from booking where booking.carId = car.carId))
17
18--find out if there any cars available right now
19
20set @isItFree = (SELECT COUNT(car.carId) FROM car 
21inner join location on location.locationId = car.locationId 
22inner join booking  on car.carId = booking.carId 
23where car.categoryId = @category and car.locationId = @location and 
24@startTime not between booking.starttime and booking.endtime and @endtime not between booking.starttime and booking.endtime and booking.carId = car.carId)
25
26
27select location.locationId,car.categoryId, car.carId,car.plateNumber, car.description,car.firstRegistration,location.address, car.odoMeter from car 
28inner join location on location.locationId = car.locationId 
29inner join booking  on car.carId = booking.carId 
30where car.categoryId = @category and car.locationId = @location and 
31@startTime not between booking.starttime and booking.endtime and @endtime not between booking.starttime and booking.endtime and booking.carId = car.carId
32
33-- data that is not in booking table yet.
34
35if @notUsedBefore > 0 
36begin
37select location.locationId,car.categoryId, car.carId,car.plateNumber, car.description,car.firstRegistration,location.address, car.odoMeter from car 
38inner join location on location.locationId = car.locationId 
39where  car.categoryId = @category and car.locationId = @location and not exists (select * from booking where booking.carId = car.carId)
40end
41
42-- if there is nothing to show
43
44if @notUsedBefore = 0 and @isItFree = 0
45begin
46raiserror('Sorry not available',16,1)
47end
48return
49go
50
51-- one that has not yet have booking
52
53exec getCategoryCars 1,1,'2018.12.30', '2019.01.23'
54go
55
56-- one that doesnt have possibility to be booked
57
58exec getCategoryCars 1,2,'2018.11.30', '2019.01.24'
59go
60
61
62--Ex.7 b)
63go
64drop procedure getCarIfAvailable
65go
66
67CREATE PROCEDURE getCarIfAvailable(@car int, @startTime datetime, @endtime datetime)
68as
69declare @notUsedBefore int
70declare @isItFree int
71
72--find out if there are any  cars that did not have booking yet
73
74set @notUsedBefore = (SELECT COUNT(car.carId) FROM car 
75inner join location on location.locationId = car.locationId 
76where car.carId = @car and not exists (select * from booking where booking.carId = car.carId))
77
78--find out if there any cars available right now
79
80set @isItFree = (SELECT COUNT(car.carId) FROM car 
81inner join location on location.locationId = car.locationId 
82inner join booking  on car.carId = booking.carId 
83where car.carId = @car and @startTime not between booking.starttime and booking.endtime and @endtime not between booking.starttime and booking.endtime and booking.carId = car.carId)
84
85select location.locationId,car.categoryId, car.carId,car.plateNumber, car.description,car.firstRegistration,location.address, car.odoMeter from car 
86inner join location on location.locationId = car.locationId 
87inner join booking  on car.carId = booking.carId 
88where car.carId = @car and @startTime not between booking.starttime and booking.endtime and @endtime not between booking.starttime and booking.endtime and booking.carId = car.carId
89
90-- data that is not in booking table yet.
91if @notUsedBefore > 0 
92begin
93select location.locationId,car.categoryId, car.carId,car.plateNumber, car.description,car.firstRegistration,location.address, car.odoMeter from car 
94inner join location on location.locationId = car.locationId 
95where car.carId = @car and not exists (select * from booking where booking.carId = car.carId)
96end
97--error if there is nothing available
98if @notUsedBefore = 0 and @isItFree = 0
99begin
100raiserror('Sorry not available',16,1)
101end
102
103return
104go
105--not available
106exec getCarIfAvailable 1,'2018.12.31', '2019.03.23'
107go
108--available
109exec getCarIfAvailable 1,'2019.12.31', '2020.03.23'
110go