ALTER PROCEDURE SPROC_SEARCH
( @csvResortIDs [varchar](63),
@csvTripTypes [varchar](10),
@ZipFrom [char](5),
@Distance [smallint],
@StartDate [smalldatetime],
@EndDate [smalldatetime],
@NeedsA [tinyint], --0=Driver|1=Passenger|2=Either
@ShowFull [bit]
)
AS
DECLARE @cSQL [nvarchar](4000)
DECLARE @cFilter_for_Resort nvarchar(100),
@cFilter_for_TripTypes nvarchar(100)
IF @csvResortIDs <> ''
SET @cFilter_for_Resort = 'AND Resort.ResortID IN(' + @csvResortIDs + ') '
ELSE
SET @cFilter_for_Resort = 'AND Resort.ResortID = 30 '
IF @csvTripTypes <> ''
SET @cFilter_for_TripTypes = 'AND TripTypes.TripTypeID IN(' + @csvTripTypes + ') '
ELSE
SET @cFilter_for_TripTypes = 'AND TripTypes.TripTypeID = 4 '
SET @cSQL =
'SELECT Trips.TripID,
Resort.Name AS Resort,
Trips.TripDate AS [Date],
Trips.Description AS [More Info],
CASE
WHEN TripUsersCount.Count_All >= Trips.Maxpassengers THEN ''FULL''
ELSE CAST( TripUsersCount.Count_All AS char(1) ) + ''/'' + CAST( Trips.MaxPassengers AS char(1) )
END AS [Ppl/Max],
CASE TripUsersCount.Count_IsDriver
WHEN 0 THEN ''NO''
WHEN 1 THEN ''YES''
END AS [Has Driver],
TripTypes.TripTypeName as [Who''s Welcome],
ZipDistances.Distance as Distance
,TripUsersCount.*
FROM Trips
INNER JOIN ( SELECT TripID, COUNT(*) AS Count_All, SUM( CASE WHEN IsDriver = 1 THEN 1 ELSE 0 END ) AS Count_IsDriver FROM TripsUsers GROUP BY TripID ) AS TripUsersCount ON TripUsersCount.TripID = Trips.TripID
INNER JOIN Resort ON Resort.ResortID = Trips.ResortID ' + @cFilter_for_Resort + '
INNER JOIN TripTypes ON Trips.TripTypeID = TripTypes.TripTypeID ' + @cFilter_for_TripTypes + '
INNER JOIN TripsUsers ON Trips.TripID = TripsUsers.TripID
INNER JOIN Users ON TripsUsers.UserID = Users.UserID
INNER JOIN ZipDistances ON Users.ZipCode = ZipDistances.ZipCodeTo AND ZipDistances.ZipCodeFrom = @ZipFrom AND ZipDistances.Distance < @Distance
WHERE ( ( TripUsersCount.Count_IsDriver > 0 AND TripsUsers.IsDriver = 1 ) -- there is driver ( or more drivers ? :-) ) in the trip
-- no driver in the trip, so take first user in that trip
OR ( TripUsersCount.Count_IsDriver = 0 AND Users.UserID = ( SELECT TOP 1 UserID
FROM TripsUsers AS WithoutDriver
WHERE WithoutDriver.TripID = Trips.TripID
)
)
)
-- filter conditions
AND Trips.Tripdate BETWEEN @StartDate AND @EndDate
AND ( @NeedsA = 0 AND TripUsersCount.Count_IsDriver = 0 OR --Needs a driver, but doesn''t have one
@NeedsA = 1 AND TripUsersCount.Count_All <= Trips.MaxPassengers - 2 OR --Definitely has room for a passenger because there''s at least 2 spots free
@NeedsA = 1 AND TripUsersCount.Count_IsDriver = 1 AND TripUsersCount.Count_All <= Trips.MaxPassengers - 1 OR --Needs a passenger, only 1 spot free in the car, but theres already a driver
@NeedsA = 2 AND TripUsersCount.Count_All < Trips.MaxPassengers OR --has an open spot in the car
@ShowFull = 1 AND TripUsersCount.Count_All = Trips.MaxPassengers
)
ORDER BY Trips.TripDate ASC'
EXECUTE sp_executesql
@cSQL,
N'@StartDate datetime,@EndDate datetime,@NeedsA [tinyint],@ShowFull [bit],@ZipFrom [char](5),@Distance [smallint]',
@StartDate,
@EndDate,
@NeedsA,
@ShowFull,
@ZipFrom,
@Distance
