I have 3 tables Flight_schedule, Flights and third is Route I need a stored procedure in SQL which give the cheapest flight on a given date

Question

I have 3 tables Flight_schedule, Flights and third is Route I need a stored procedure in SQL which give the cheapest flight on a given date.

When the parameter date is passed to procedure suppose 2 February so the result would be the lowest fare flight on the 2 Feb.

Here is the code where I have joined the tables and passed the parameter to stored procedure but when I am confused in the condition part.

Create proc spCheapestFlight 
@FLIGHT_DATE DATE 
AS  
BEGIN  
SELECT Flight_schedule.FlightDate,Flight_schedule.Departure,route.source, route.destination, 
    Flight_schedule.Arrival 
    ,Flight_schedule.Fare,Flights.Flight_name 
    FROM Flight_schedule 
    inner join route ON Flight_schedule.Route_id=route.Route_id inner join  
Flights on Flights.Flight_id=Flight_schedule.Flight_id 
where Flight_schedule.FlightDate = @FLIGHT_DATE   
END 

Answer 1

Flight_schedule

F_Id	Flight_id	Total_Fare	FlightDate	Departure	Arrival	Route_Id	Fare
10000	1	1001	2022-02-01	09:30:30.0000000	11:20:45.0000000	100	7500.8
10001	2	1002	2022-02-02	09:45:30.0000000	11:55:45.0000000	101	7000.9
10002	3	1003	2022-02-03	10:30:30.0000000	12:20:45.0000000	102	5111.5
10003	4	1004	2022-02-04	11:30:30.0000000	14:20:45.0000000	103	5500.9
10004	5	1005	2022-02-05	12:30:30.0000000	15:20:45.0000000	104	9000.7
10005	1	1006	2022-02-06	13:30:30.0000000	16:20:45.0000000	105	8675.5
10006	2	1007	2022-02-07	14:30:30.0000000	17:20:45.0000000	106	4000.5
10007	3	1008	2022-02-08	15:30:30.0000000	18:20:45.0000000	107	4100.5
10008	4	1009	2022-02-09	16:30:30.0000000	19:20:45.0000000	101	4000.3
10009	2	1006	2022-02-10	06:30:30.0000000	08:20:45.0000000	108	4000.3

Flights

Flight_id	Flight_name	Capacity
1	Vistara	30
2	Indigo	30
3	SpiceJet	30
4	Go_First	30
5	Air India	30

Route

route_id	source	destination
100	Mumbai	Delhi
101	Delhi	Ahmedabad
102	Ahmedabad	Delhi
103	Ahmedabad	Mumbai
104	Chennai	Mumbai
105	Chennai	Goa
106	Chennai	Delhi
107	Goa	Delhi
108	Bangalore	Delhi
109	Hyderabad	Delhi

your data does not show more than 1 flight per day, in addition there is no need for table Flights and table Route to get result, you should join table FlightSchedule with itself in order to get the minimum fare. your problem can be solve with table-value function(TVF). you should change you PROCEDURE as follows:

CREATE PROCEDURE  spCheapest_Costliest_Flight (@FlightDate DATE) 
AS    
SELECT FS.FlightDate,FS.Departure,route.source, route.destination, 
FS.Arrival ,T.Fare,Flights.Flight_name 
FROM Flight_schedule FS
inner join route ON FS.Route_id=route.Route_id 
inner join Flights on Flights.Flight_id=FS.Flight_id 
inner join  (select FlightDate FlightDate,MIN(Fare) Fare
from Flight_schedule GROUP BY FlightDate) T
ON T.FlightDate=FS.FlightDate and T.FlightDate=@FlightDate and FS.FlightDate=@FlightDate
GO