BruceyBandit BruceyBandit - 1 month ago 11
SQL Question

aggregate function and GROUP BY Clause error

I have a query below where it compares the number of

stagingCabincrew
and
StagingCockpitCrew
columns from the staging schema and compares them to their data schema equivalent
'DataCabinCrew'
and
'DataCockpitCrew'
.

Below is the query and the results outputted:

WITH CTE AS
(SELECT cd.*,
c.*,
DataFlight,
l.ScheduledDepartureDate,
l.ScheduledDepartureAirport
FROM
(SELECT *,
ROW_NUMBER() OVER(PARTITION BY LegKey
ORDER BY UpdateID DESC) AS RowNumber
FROM Data.Crew) c
INNER JOIN Data.CrewDetail cd ON c.UpdateID = cd.CrewUpdateID
AND cd.IsPassive = 1
AND RowNumber = 1
INNER JOIN
(SELECT *,
Carrier + CAST(FlightNumber AS VARCHAR) + Suffix AS DataFlight
FROM Data.Leg) l ON c.LegKey = l.LegKey )
SELECT StagingFlight,
sac.DepartureDate,
sac.DepartureAirport,
cte.DataFlight,
cte.ScheduledDepartureDate,
cte.ScheduledDepartureAirport,
SUM(CASE
WHEN sac.CREWTYPE = 'F' THEN 1
ELSE 0
END) AS StagingCabinCrew,
SUM(CASE
WHEN sac.CREWTYPE = 'C' THEN 1
ELSE 0
END) AS StagingCockpitCrew,
SUM(CASE
WHEN cte.CrewType = 'F' THEN 1
ELSE 0
END) AS DataCabinCrew,
SUM(CASE
WHEN cte.CrewType = 'C' THEN 1
ELSE 0
END) AS DataCockpitCrew
FROM
(SELECT *,
Airline + CAST(FlightNumber AS VARCHAR) + Suffix AS StagingFlight,
ROW_NUMBER() OVER(PARTITION BY Airline + CAST(FlightNumber AS VARCHAR) + Suffix
ORDER BY UpdateId DESC) AS StageRowNumber
FROM Staging.SabreAssignedCrew) sac
LEFT JOIN CTE cte ON StagingFlight = DataFlight
AND sac.DepartureDate = cte.ScheduledDepartureDate
AND sac.DepartureAirport = cte.ScheduledDepartureAirport
AND sac.CREWTYPE = cte.CrewType
WHERE MONTH(sac.DepartureDate) + YEAR(sac.DepartureDate) = MONTH(GETDATE()) + YEAR(GETDATE())
AND StageRowNumber = 1 --AND cte.ScheduledDepartureDate IS NOT NULL
--AND cte.ScheduledDepartureAirport IS NOT NULL
GROUP BY StagingFlight,
sac.DepartureDate,
sac.DepartureAirport,
cte.DataFlight,
cte.ScheduledDepartureDate,
cte.ScheduledDepartureAirport


The results are correct, all I need to do is add a condition in the WHERE clause where
StagingCabinCrew <> DataCabinCrew AND StagingCockpitCrew <> DataCockpitCrew


enter image description here

If a row appears then we have found an error in the data, I just need helping adding this condition in the WHERE Clause because the columns in the WHERE Clause are referring to a SUM and CASE Function. I just need help manipulating the query so that I can add this WHERE Clause

Answer

I will guess you are trying to use an alias in the same query.

You CANT do this, because the alias wont be recognized in the WHERE.

SELECT  field1 + field2 as myField
FROM yourTable
WHERE myField > 3

You need to include it in a sub query

with cte2 as (

    SELECT  field1 + field2 as myField
    FROM yourTable
)
SELECT *
FROM cte2
WHERE myField > 3

or repeat the function

    SELECT  field1 + field2 as myField
    FROM yourTable
    WHERE field1 + field2 > 3
Comments