create view Fact_Op 

as 

with t as ( 

select  

case 

when Customers.Type = 'I' then 'Individual' 

when Customers.Type = 'O' then 'Organisation' 

end as CustomerType,  

dCustomers.District as DistrictCustomer,  

dCustomers.City as CityCustomer, dCustomers.Country as CountryCustomer, CarNumber,  

convert(Date, BookingDatetime) as BookingDate,  

convert(Date, PickupDatetime) as PickupDate, 

pickupDistrict.District as pickupDistrict, pickupDistrict.City as pickupCity, 

pickupDistrict.Country as pickupCountry, 

returnDistrict.District as returnDistrict, returnDistrict.City as returnCity, 

returnDistrict.Country as returnCountry, 

Cartypes.Name as CartypeName, SeatCapacity, Manufacturers.Name as ManufacturerName, 

Model, YearOfMake,  

case  

when BookingCars.RentalPricePerDay < 100 then 'Price < 100' 

when BookingCars.RentalPricePerDay >= 100 and BookingCars.RentalPricePerDay < 150 then '100 <= Price < 150' 

when BookingCars.RentalPricePerDay >= 150 and BookingCars.RentalPricePerDay < 200 then '150 <= Price < 200' 

when BookingCars.RentalPricePerDay >= 200 then 'Price >= 200' 

end as PriceRange,  

Colors.Name as Color 

from Bookings join BookingCars on Bookings.ID = BookingCars.BookingID 

join Customers on Customers.ID = Bookings.CustomerID 

join Districts dCustomers on dCustomers.ID = Customers.DistrictID 

join Locations pickupLocation on pickupLocation.ID = Bookings.PickupLocationID 

join Districts pickupDistrict on pickupDistrict.ID = pickupLocation.DistrictID 

join Locations returnLocation on returnLocation.ID = Bookings.ReturnLocationID 

join Districts returnDistrict on returnLocation.DistrictID = returnDistrict.ID 

join Cars on Cars.Number = BookingCars.CarNumber 

join Colors on Cars.ColorID = Colors.ID 

join CarSubtypes on Cars.SubtypeID = CarSubtypes.ID 

join Manufacturers on Manufacturers.ID = CarSubtypes.ManufacturerID 

join Cartypes on Cartypes.ID = CarSubtypes.CartypeID 

) 

select CustomerType, DistrictCustomer, CityCustomer, CountryCustomer,  

CartypeName, SeatCapacity, ManufacturerName, Model, YearOfMake, PriceRange, Color, 

BookingDate, PickupDate, 

pickupDistrict, pickupCity, pickupCountry, 

returnDistrict, returnCity, returnCountry, 

count(CarNumber) as Quantity 

from t 

group by CustomerType, DistrictCustomer, CityCustomer, CountryCustomer,  

CartypeName, SeatCapacity, ManufacturerName, Model, YearOfMake, PriceRange, Color, 

BookingDate, PickupDate, 

pickupDistrict, pickupCity, pickupCountry, 

returnDistrict, returnCity, returnCountry