How To Get Data From Sql Server Tocityid And Fromcityid Bases

I have below query I want to show data fromCityId AND ToCityId . Suppose passenger travel fromCity London toCity Manchester. How do I write query like this, when I use where cl

Solution 1:

Would the below resolve your problem:

withcte
(VoucherID,FromCity,ToCity,InDate)
as
(select
    vh.VoucharId
,   fCity.CityName as FromCity
,   tCity.CityName as ToCity
,   InDate
from        VoucharHotel    vh
inner join  City            fCity on    vh.City = fCity.CityId
inner join  City            tCity on    vh.City = tCity.CityId 
where       vh.InDate  between '11/15/2018 12:00:00 AM'and'11/16/2018 12:00:00 AM')

select
*
from    cte
where   City in (1,2)

Let me know if you need changes made.