I need help please with writing a sproc, it takes a table-valued parameter @Locations, whose Type is defined as follows:
CREATE TYPE [dbo].[tvpLocation] AS TABLE(
[CountryId] [int] NULL,
[ResortName] [nvarchar](100) NULL,
[Ordinal] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Ordinal] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
@Locations will contain at least 1 row. Each row WILL have a non-null CountryId, and MAY have a non-null ResortName.
Each row will have a unique Ordinal, the first being 0. The combinations of CountryId and ResortName in @Locations will be unique.
The sproc needs to search against the following table structure.
The image can be seen better by right-clicking it and View Image, or similar depending on your browser.
Now this is where I'm stuck, the sproc should be able to find Tours where:
The Tour's 1st TourHotel (Ordinal 0)
has the same CountryId (and ResortName
if specified) of the 1st row of
@Locations (Ordinal 0).
And also if @Locations has 1 row,
the Tour must have additional
TourHotels, ALL of which must be in the
remaining CountryIds (and ResortNames if specified) of these remaining @Locations rows.
Edit This is the code I finally used, based on Anthony Faull's suggestion. Thank you so much Anthony:
select distinct T.Id
from tblTour T
join tblTourHotel TH on TH.TourId = T.Id
join tblHotel H ON H.Id = TH.HotelId
JOIN @Locations L ON
(
(
L.Ordinal = 0
AND TH.Ordinal = 0
)
OR
(
L.Ordinal > 0
AND TH.Ordinal > 0
)
)
AND L.CountryId = H.CountryId
AND
(
L.ResortName = H.ResortName
OR L.ResortName IS NULL
)
cross apply( select COUNT(TH2.Id) AS [Count] FROM tblTourHotel TH2 where TH2.TourId = TH.TourId ) TourHotelCount
where
TourHotelCount.[Count] = @LocationCount
group by T.Id, T.TourRef, T.Description, T.DepartureDate, T.NumNights, T.DepartureAirportId, T.DestinationAirportId, T.AirlineId, T.FEPrice
having COUNT(distinct TH.Id) = @LocationCount
