2 Selects (summing) within same table

Transact-SQL
1

I'm looking to do 2 queries off the same table. I need separate output columns and I am a bit at a loss how to accomplish this. I need 4 columns: PETS_LOAD, BROKER_LOAD, GROSS_REVENUE, EXPENSES. I have tried a few things to include trying a UNION ALL statement but just not getting it. FYI - The DECLARE statements will become variable i pass once the SQL is worked out. My latest venture being as follows but errors with: 'SUM' is not a recognized built-in function name Guidance please?

DECLARE @start_date date
DECLARE @end_date date
SET @start_date =  '01-01-2020'
SET @end_date = '12-31-2020'

DECLARE @startLoadNumber int =  '2020' * 1000 + 1
      , @endLoadNumber int =  '2020' * 1000 + 999;

SELECT PETS_LOAD_NUMBER AS PETS_LOAD, 
       CARRIER_LOAD_NUMBER AS CARRIER_LOAD,
       (SELECT 
          SUM(IsNull(DETENTION_PAID, 0)
            + ISNULL(EXTRA_PAY_RECEIVED, 0)
            + ISNULL(EXTRA_STOP_PICKUP_PAY, 0)
            + ISNULL(EQUIP_WASH_REIMBURSE, 0)
            + ISNULL(FAX_REIMBURSEMENT, 0)
            + ISNULL(FUEL_REIMBURSE_TOTAL, 0)
            + ISNULL(LAYOVER_TONU, 0)
            + ISNULL(LOAD_LOCK_REIMBURSE, 0)
            + ISNULL(LUMPER_REIMBURSEMENT, 0)
            + IsNull(PALLET_EXCHANGE_REIMBURSE, 0)
            + ISNULL(REEFER_REIMBURSEMENT, 0)
            + ISNULL(REPAIR_CREDIT, 0)
            + ISNULL(RFER_FUEL_TAX_REFUND, 0)
            + ISNULL(SCALES_REIMBURMENT, 0)
            + ISNULL(TOLL_REIMBURMENT, 0)
            + ISNULL(TRACTOR_FUEL_TAX_REFUND, 0)
            + ISNULL(TRLR_SHUTTLE_PAY, 0)
            + LINEHAUL_PAID 
            + FUEL_SUR_PAID 
            + FUEL_SUR_ADJ_PAID 
            + RFR_FUEL_SUR_PAID
         FROM Load_Info_Table) AS GROSS_REVENUE,  
       (SELECT 
           SUM(IsNull(AGENT_FEE, 0)
             + IsNull(CLAIM_AMOUNT, 0)
             + IsNull(DEF_PURCHASED, 0)
             + IsNull(EQUIP_WASHING_COST, 0)
             + IsNull(FUEL_CARD_FEE_TOTAL, 0)
             + IsNull(FAX_COST, 0)
             + IsNull(FUEL_PURCHASED, 0)        
             + IsNull(LOAD_LOCKS, 0)
             + IsNull(LUMPER_FEES, 0)
             + IsNull(OUT_OF_POCKET_FUEL, 0)
             + IsNull(PALLET_EXCHANGE, 0)
             + IsNull(PARKING, 0)
             + IsNull(PU_DRP_TRLR_FEE, 0)
             + IsNull(REEFER_PURCHASED, 0)
             + IsNull(REPAIR_COSTS, 0)
             + IsNull(SCALES_COST, 0)
             + IsNull(TOLL_COST, 0)
             + IsNull(TRUCK_SUPPLIES, 0)
        FROM Load_Info_Table) AS EXPENSES
 FROM Load_Info_Table
 WHERE PETS_LOAD_NUMBER BETWEEN @startLoadNumber AND @endLoadNumber  
 GROUP BY PETS_LOAD_NUMBER
2

missing a closing ) for the SUM function after RFR_FUEL_SUR_PAID

3

Not a lot of details to go on, but something like this:

DECLARE @start_date date
DECLARE @end_date date
SET @start_date =  '01-01-2020'
SET @end_date = '12-31-2020'

DECLARE @startLoadNumber int =  '2020' * 1000 + 1
      , @endLoadNumber int =  '2020' * 1000 + 999;

SELECT PETS_LOAD_NUMBER AS PETS_LOAD, 
       CARRIER_LOAD_NUMBER AS CARRIER_LOAD,
          SUM(IsNull(DETENTION_PAID, 0)
            + ISNULL(EXTRA_PAY_RECEIVED, 0)
            + ISNULL(EXTRA_STOP_PICKUP_PAY, 0)
            + ISNULL(EQUIP_WASH_REIMBURSE, 0)
            + ISNULL(FAX_REIMBURSEMENT, 0)
            + ISNULL(FUEL_REIMBURSE_TOTAL, 0)
            + ISNULL(LAYOVER_TONU, 0)
            + ISNULL(LOAD_LOCK_REIMBURSE, 0)
            + ISNULL(LUMPER_REIMBURSEMENT, 0)
            + IsNull(PALLET_EXCHANGE_REIMBURSE, 0)
            + ISNULL(REEFER_REIMBURSEMENT, 0)
            + ISNULL(REPAIR_CREDIT, 0)
            + ISNULL(RFER_FUEL_TAX_REFUND, 0)
            + ISNULL(SCALES_REIMBURMENT, 0)
            + ISNULL(TOLL_REIMBURMENT, 0)
            + ISNULL(TRACTOR_FUEL_TAX_REFUND, 0)
            + ISNULL(TRLR_SHUTTLE_PAY, 0)
            + LINEHAUL_PAID
            + FUEL_SUR_PAID 
            + FUEL_SUR_ADJ_PAID 
            + RFR_FUEL_SUR_PAID) AS GROSS_REVENUE,
           SUM(IsNull(AGENT_FEE, 0)
             + IsNull(CLAIM_AMOUNT, 0)
             + IsNull(DEF_PURCHASED, 0)
             + IsNull(EQUIP_WASHING_COST, 0)
             + IsNull(FUEL_CARD_FEE_TOTAL, 0)
             + IsNull(FAX_COST, 0)
             + IsNull(FUEL_PURCHASED, 0)        
             + IsNull(LOAD_LOCKS, 0)
             + IsNull(LUMPER_FEES, 0)
             + IsNull(OUT_OF_POCKET_FUEL, 0)
             + IsNull(PALLET_EXCHANGE, 0)
             + IsNull(PARKING, 0)
             + IsNull(PU_DRP_TRLR_FEE, 0)
             + IsNull(REEFER_PURCHASED, 0)
             + IsNull(REPAIR_COSTS, 0)
             + IsNull(SCALES_COST, 0)
             + IsNull(TOLL_COST, 0)
             + IsNull(TRUCK_SUPPLIES, 0)) AS EXPENSES
 FROM Load_Info_Table
 WHERE PETS_LOAD_NUMBER BETWEEN @startLoadNumber AND @endLoadNumber  
 GROUP BY PETS_LOAD_NUMBER, CARRIER_LOAD_NUMBER
1 Like
4

Thanks, once again Scott!!

I see where I went wrong. Now I know what I did wrong so I should not make that mistake again!

5

Awesome!