Is it possible to only bring in values into a column if there is no data in another column?-CodePudding

I work for a transportation company. We have two types of drivers, our own company drivers and external Carriers. Carriers are a business to business transaction and what we pay them for each move is easily entered into our system as a "Payable." The payable is easily extracted. For our company drivers, we have to go through a process called "Settlements" which is an extra module for our system. Unfortunately, this extra module does not have a relationship with all other tables, it is segregated and hard to capture data. When we run "Settlements" each payable to the company driver is separated by a rate code. What I am trying to do is the following. We have a ratecode named "DDR" , short for Driver Deadrun. When we have a driver deadrun, we pay a fixed price of $40 to our company drivers. However, when there is a driver deadrun on the "Carrier" side, then that amount is not fixed. The amount will be whatever the carrier bills us, we enter this invoiced amount as a payable to the carrier. I have embedded the cost of a driver deadrun (DDR) in the query using a case statement. However, if there is a payable it is bringing in the embedded $40 plus the payable. This is providing me with inaccurate results.

Here is a sample of my query and a screenshot of query results.

    SELECT      ds_id AS TMP, ds_ship_date AS ShipDate, ds_ref1_text AS ContainerNumber,  

          (CASE ds_status WHEN 'A' THEN 'TEMPLATE'  

    WHEN 'C' THEN 'CANCELLED'  

    WHEN 'D' THEN 'DECLINED'  

    WHEN 'E' THEN 'QUOTED'  

    WHEN 'F' THEN 'OFFERED'   

    WHEN 'H' THEN 'PENDING'  

   WHEN 'K' THEN 'OPEN'  

   WHEN 'N' THEN 'AUTHORIZED'  

   WHEN 'Q' THEN 'AUDIT REQUIRED'  

   WHEN 'T' THEN 'AUDITED'  

   WHEN 'W' THEN 'BILLED'   

   END) AS 'TMPStatus',  

        b.co_name as "BillTo", o.co_name AS Origin, o.co_city AS OriginCity, o.co_state AS OriginState, 

        de_arrdate AS DeliveryDate, de_arrtime AS ArrivalTime, de_deptime AS DepartureTime,    

        dba.disp_items.di_qty AS QTY, dba.disp_items.ratecodename AS RateCode, 
     dba.disp_items.di_our_rate AS OURRATE, dba.disp_items.di_our_itemamt AS ITEMAMT, 
    dba.disp_items.amounttype AS AMTTYPE, dba.disp_items.di_pay_itemamt AS PAYITEMAMT,  

   case dba.disp_items.ratecodename
   WHEN 'SHUNTING' then '10.00' 
   WHEN 'LIFT OFF' THEN '10.00'
   WHEN 'LIFT ON' THEN '10.00'
   WHEN 'LIFT' THEN '10.00'
   WHEN 'DDR' THEN '40.00'
   WHEN 'DEADRUN' THEN '40.00'

   else (select SUM (amount) from dba.amountowed where string ( ds_id ) = amountowed.shipment 
   and dba.amountowed.startdate between '20220101' and today()) end case AS Total_Payable,  

        (CASE ds_ship_type   

                WHEN '2201' THEN 'TRAS&D.V.'   

                WHEN '2202' THEN 'TRAS&D.V.'   

                WHEN '2203' THEN 'SOLUTIONS'   

                WHEN '2204' THEN 'OLD BROKERAGE'   

                WHEN '2205' THEN 'LIFTING'   

                WHEN '2206' THEN 'WAREHOUSE'   

        END) AS Division 

    FROM        dba.disp_ship   

    JOIN        dba.disp_events ON de_shipment_id = ds_id   

    JOIN        dba.disp_items ON dba.disp_items.di_shipment_id = dba.disp_ship.ds_id 

    JOIN        dba.companies o ON o.co_id = ds_origin_id    

    JOIN        dba.companies b on b.co_id = ds_billto_id   

    WHERE       de_site = ds_findest_id   

    AND de_event_type IN ('D','R','N')

    and DeliveryDate between '20220101' and today()

    GROUP BY TMP, SHIPDATE, CONTAINERNUMBER, TMPSTATUS, BILLTO, ORIGIN, ORIGINCITY, 
    ORIGINSTATE, DELIVERYDATE, ARRIVALTIME, DEPARTURETIME, QTY, RATECODE, OURRATE, ITEMAMT, 
    AMTTYPE, PAYITEMAMT, TOTAL_PAYABLE, DIVISION 

    ORDER BY TOTAL_PAYABLE DESC

What I am basically looking for is if in the column "PAYITEMAMT" there is a value of "0.00' or null then in the column "Total_Payable" the value would be 40.00.

I do not want values in in both columns as this would provide inaccurate data.

CodePudding user response：

Yes, you can just use a CASE statement:

CASE
    WHEN dba.disp_items.di_pay_itemamt IS NULL THEN 40
    WHEN dba.disp_items.di_pay_itemamt = 0 THEN 40
    ELSE dba.disp_items.di_pay_itemamt
END AS PAYITEMAMT

If it can never be 0 (i.e. is populated or will be NULL) then you could just do

COALESCE(dba.disp_items.di_pay_itemamt,40) AS PAYITEMAMT

CodePudding user response：

I don't know if I have understood your question right but, why not just put a case ?

case when isnull(PAYITEMAMT, 0) = 0 then 40 
else 
    case dba.disp_items.ratecodename WHEN 'SHUNTING' then '10.00' 
    WHEN 'LIFT OFF' THEN '10.00'
    WHEN 'LIFT ON' THEN '10.00'
    WHEN 'LIFT' THEN '10.00'
    WHEN 'DDR' THEN '40.00'
    WHEN 'DEADRUN' THEN '40.00'
    else 
        (select SUM (amount) from dba.amountowed where string ( ds_id ) = amountowed.shipment 
   and dba.amountowed.startdate between '20220101' and today()) 
    end 
end AS Total_Payable