SET @OriginStation='PWLLHELI', @DestStation='MACHYNLLETH', @SXFormat='M-F', @StartDate='2023-05-21', @EndDate='2023-12-11';

	CREATE TEMPORARY TABLE Trainset.TrainOrigins SELECT Tiploc AS Tiplocs FROM Trainset.StationDetails WHERE StationName = @OriginStation;
	CREATE TEMPORARY TABLE Trainset.TrainDests SELECT Tiploc AS Tiplocs FROM Trainset.StationDetails WHERE StationName = @DestStation;
	CREATE TEMPORARY TABLE Trainset.AZResults SELECT F1.CIFFileName, F1.TransactionType, F1.DateStamp, F1.UID, F1.TrainID, F1.STPIndicator, F1.StartDate, F1.EndDate,
		F2.Tiploc AS OrgStn, F1.TOCCode, F2.Depart, F2.DepDaysRun, F3.Tiploc AS DestStn, F3.Arrive, F3.ArrDaysRun, F1.Origin AS TrainOrigin, F1.Depart AS OriginDepart, F1.DaysRun AS OriginDays,
        F1.Destination as TrainDestination, F1.Arrive AS DestArrive, F4.ArrDaysRun AS DestDays, F2.VehicleClass, F2.Catering, F2.Reservations, F2.Sleepers, F2.Platform, 
	CASE
		WHEN F2.DepDaysRun > 4  AND @SXFormat = 'M-F' THEN 'M-F'
		WHEN F2.DepDaysRun >= 4 AND @SXFormat = 'M-S' THEN 'M-S'
		WHEN F2.DepDaysRun = 4 AND @SXFormat = 'M-S' THEN NULL
		WHEN F2.DepDaysRun = 4 AND @SXFormat = 'M-F' THEN 'Sat'
		WHEN F2.DepDaysRun = 2 THEN 'Sun'
	END AS Dayset
	FROM Trainset.MainUIDList F1
		INNER JOIN Trainset.LocationInfo F2 FORCE INDEX FOR JOIN (Locations) ON
		F1.UID = F2.UID AND F1.STPIndicator = 'P' AND F1.STPIndicator = F2.STPIndicator AND
		F1.TransactionType<>'D' AND F1.TransactionType = F2.TransactionType AND
		F1.StartDate <= @EndDate AND F1.EndDate >= @StartDate AND
		F1.StartDate = F2.StartDate AND F1.EndDate = F2.EndDate AND
		F1.DateStamp = F2.DateStamp AND
		F2.Tiploc IN (SELECT Tiplocs from Trainset.TrainOrigins) AND
		F2.OpChars NOT LIKE('%Q%') AND
		F2.Depart IS NOT NULL
			INNER JOIN Trainset.LocationInfo F3 FORCE INDEX FOR JOIN (Locations) ON
			F2.UID = F3.UID AND F2.STPindicator = F3.STPIndicator AND
			F2.TransactionType = F3.TransactionType AND
			F2.StartDate = F3.StartDate AND F2.EndDate = F3.EndDate AND
			F2.DateStamp = F3.DateStamp AND
			F3.OpChars NOT LIKE('%Q%') AND
			F3.Tiploc in (SELECT Tiplocs FROM Trainset.TrainDests) AND
			F2.CallingIndex < F3.CallingIndex AND
			F3.Arrive IS NOT NULL
				INNER JOIN Trainset.LocationInfo F4 ON
				F3.UID = F4.UID AND F3.STPIndicator = F4.STPIndicator AND
				F3.TransactionType = F4.TransactionType AND
				F3.StartDate = F4.StartDate AND F3.EndDate = F4.EndDate AND
				F3.DateStamp = F4.DateStamp AND
				F4.LocationType='T';
    SELECT * FROM AZResults WHERE Dayset IS NOT NULL ORDER BY Dayset, Depart, Arrive, StartDate;
	DROP TEMPORARY TABLE Trainset.TrainOrigins;
	DROP TEMPORARY TABLE Trainset.TrainDests;
    DROP TEMPORARY TABLE Trainset.AZResults;