CODEWITHSUNDEEP
sqlsql-servercountapply
user4912958
user4912958

Reputation:

New Column with query result in SQL Server

I need add new column with query result.

I have this Query:

    SELECT DISTINCT Arrival                              
           , Flight                                         
           , TotalPax.SumPassengers            
           , TotalPaxLocal.SumLocalPassengers  
           , STD                                         
           , STA                                         
           --, PassengerID                       
           --, Departure                     
           --, JourneyNumber
           --, SegmentNumber
           --, LegNumber           
           --, InventoryLegKey  
           --, RecordLocator
        FROM #TempLocalOrg tmp

    CROSS APPLY
    (
       SELECT COUNT(1) AS SumPassengers
            FROM #TempLocalOrg crossTemp
       WHERE tmp.Flight = crossTemp.Flight

    ) TotalPax 

    CROSS APPLY
    (
       SELECT COUNT(1) AS SumLocalPassengers
            FROM #TempLocalOrg crossTemp
       WHERE tmp.Flight        = crossTemp.Flight           
         AND tmp.Arrival       = crossTemp.Arrival
         AND tmp.SegmentNumber = 1
         AND tmp.LegNumber     = 1
    ) TotalPaxLocal

TotalPax is working, but, TotalPaxLocal not working, i need a Cross Apply or something like the query below:

    SELECT Count(*) 
        FROM #TempLocalOrg
     WHERE Flight        = 1500 
       AND Arrival       = 'FLN'
       AND SegmentNumber = 1
       AND LegNumber     = 1

So, i need replace the Cross Apply TotalPaxLocal for this query.

Wrong OutPut:

Arrival | Flight | TotalPax | TotalPaxLocal | ETD              | ETA
--------|--------|----------|---------------|------------------|----------------    
FLN     | 1500   | 144      | 144           | 05/22/2015 08:25 | 05/22/2015 09:35

Correct OutPut:

Arrival | Flight | TotalPax | TotalPaxLocal | ETD              | ETA
--------|--------|----------|---------------|------------------|----------------    
FLN     | 1500   | 144      | 52            | 05/22/2015 08:25 | 05/22/2015 09:35

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Just use window functions for these calculations:

SELECT DISTINCT tmp.Arrival, tmp.Flight,
       COUNT(*) OVER (PARTITION BY Flight) as NumPassengers, 
       SUM(CASE WHEN SegmentNumber = 1 AND LegNumber = 1 THEN 1 ELSE 0 END) OVER
           (PARTITION BY Flight, Arrival)
          ) as NumLocalPassengers,
       STD, STA   
FROM #TempLocalOrg tmp;

Upvotes: 1

Related Questions