I have a report application that i'm replicating. This report shows us 4 columns based on a date filter. Assembly Line, Product#, Enters and Exits. The Enters and Exits are time Stamps of when this product entered the production line and when it exited.

This is my code:

SELECT

'AssemblyLine' AS AssemblyLine,

COALESCE(Enters.ChassisNumber, Exits.ChassisNumber) AS ChassisNumber,

Enters.MinTime AS EnterTime,

Exits.MaxTime AS ExitTime

FROM

(

-- ENTERS

SELECT

    ChassisNumber,

    MIN(TimeStamp) AS MinTime

FROM DIM.ChassisMoveLog

WHERE TimeStamp \>= CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)

    AND TimeStamp \< CAST(CAST(GETDATE() AS DATE) AS DATETIME)

    AND ToAssemblyLineName = 'MAIN'

    AND FromAssemblyLineName \<\> 'MAIN'

    AND PhysicalLocation = 'Chillicothe'

    -- AND ChassisNumber = '248320'

GROUP BY ChassisNumber

) Enters

FULL OUTER JOIN

(

-- EXITS

SELECT

    ChassisNumber,

    MAX(TimeStamp) AS MaxTime

FROM DIM.ChassisMoveLog

WHERE TimeStamp \>= CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)

    AND TimeStamp \< CAST(CAST(GETDATE() AS DATE) AS DATETIME)

    AND FromAssemblyLineName = 'MAIN'

    AND ToAssemblyLineName \<\> 'MAIN'

    AND PhysicalLocation = 'Chillicothe'

    -- AND ChassisNumber = '248320'

GROUP BY ChassisNumber

) Exits

ON Enters.ChassisNumber = Exits.ChassisNumber

ORDER BY EnterTime ASC, ExitTime ASC;

Everything is perfect except i have multiple assembly lines and not just Main. To assembly line is entered and from assembly line is exited. How can i adjust the ToAssemblyLineName = 'main' and fromAssemblyLineName <> 'main' and the where filter in the exits query.