Set amounts to zero in addition if no records found

How can I check for Null for the amounts if no records are returned in either select.  Basically it errors out if one or both of the Amounts return no records.  I need to do some sort of IF statement to set one of the amounts or both amounts to zero in those cases so it doesn't error out on me

   SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
   FROM    
     (

     SELECT pd.Amount as pd1_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdc pd ON pd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pd.Active = 1
                    AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
     AND pd.Entered <> '1900-01-01 00:00:00.000'
                    AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pd.Deposit IS NOT NULL    
                    --AND pd.OnHold IS NULL
                    AND c.customer <> '9999999'
          
          UNION

          SELECT   pdd.Amount as PD2_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
              AND pdd.Entered <> '1900-01-01 00:00:00.000'
                    AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pdd.Deposit IS NOT NULL    
                    --AND pdd.OnHold IS NULL
                    AND c.customer <> '9999999'
) as PDC_Main




Answer this question

Set amounts to zero in addition if no records found

  • deadfingers

    First check the number of rows returned (if exists ...) and if it is 0 manually create a record.

  • Ramesh Singh

    I fixed it:

    (
    SELECT SUM(PDC_All.Amount) as PDCs_IL
    FROM (
    SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
    FROM
    (

    SELECT pd.Amount as pd1_Amount, 0 as pd2_Amount
    FROM Master m (NOLOCK)
    LEFT JOIN dbo.pdc pd ON pd.number = m.number
    INNER JOIN dbo.Customer c ON c.Customer = m.Customer

    WHERE pd.Active = 1
    AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
    AND pd.Entered <> '1900-01-01 00:00:00.000'
    AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
    --AND pd.Deposit IS NOT NULL
    --AND pd.OnHold IS NULL
    AND c.customer <> '9999999'

    UNION

    SELECT 0 as pd1_Amount, pdd.Amount as PD2_Amount
    FROM Master m (NOLOCK)
    LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
    INNER JOIN dbo.Customer c ON c.Customer = m.Customer

    WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
    AND pdd.Entered <> '1900-01-01 00:00:00.000'
    AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
    --AND pdd.Deposit IS NOT NULL
    --AND pdd.OnHold IS NULL
    AND c.customer <> '9999999'
    ) as PDC_Main
    ) AS PDC_All



  • Set amounts to zero in addition if no records found