----------два вспомогательных представления для вычисления типов авто-------------
IF OBJECT_ID (N'cars.FirstAutosRegistration', N'U') IS NOT NULL
    DROP VIEW cars.FirstAutosRegistration
GO

CREATE VIEW cars.FirstAutosRegistration
AS
SELECT AutoID, PostID, Direction, MIN(RecordTime) AS firstTime
FROM cars.RegistrationRecords
GROUP BY AutoID, PostID, Direction
GO

IF OBJECT_ID (N'cars.LastAutosRegistration', N'U') IS NOT NULL
    DROP VIEW cars.LastAutosRegistration
GO

CREATE VIEW cars.LastAutosRegistration
AS
SELECT AutoID, PostID, Direction, MAX(RecordTime) AS lastTime
FROM cars.RegistrationRecords
GROUP BY AutoID, PostID, Direction
GO

----Транзитные----
IF OBJECT_ID (N'cars.TransitionalAutos', N'U') IS NOT NULL
    DROP VIEW cars.TransitionalAutos
GO

CREATE VIEW cars.TransitionalAutos
AS
SELECT DISTINCT cars.Autos.AutoNumber AS 'Номер'
                , cars.Autos.RegionNumber AS 'Номер региона'
                , cars.Regions.RegionName AS 'Название региона'
                , far.firstTime AS 'Время первого въезда'
                , lar.lastTime AS 'Время последнего выезда'
FROM cars.Autos INNER JOIN
     cars.RegionNumbers ON cars.Autos.RegionNumber = cars.RegionNumbers.AutoRegionNumber INNER JOIN
     cars.Regions ON cars.RegionNumbers.ConstitutionNum = cars.Regions.ConstitutionNum INNER JOIN
     cars.FirstAutosRegistration AS far ON cars.Autos.AutoID = far.AutoID INNER JOIN
     cars.LastAutosRegistration AS lar ON cars.Autos.AutoID = lar.AutoID
WHERE firstTime < lastTime
      AND far.Direction = 1
      AND lar.Direction = 2
      AND far.PostID != lar.PostID
      AND cars.RegionNumbers.ConstitutionNum != 66
GO