Can this Full Outer Join query be simplified?

Question

I have bill data from two sources. I want to join them to be able to see where there is a record in one set that is not in the other and vice versa. This query does what I want but I feel like it could be written more elegantly? I only want one field for the account number and bill date (M and Y), and then separate fields for the the charges in each source.

DECLARE @BillDate datetime ='2/1/2016'

SELECT  ACCT_NO     =   COALESCE(BPFACCT,CSTACCT)               ,
        BillDate    =   COALESCE(BPFBillDate,CSTBillDate)       ,
        BPFCharge                                               ,
        CSTCharge                                               ,
        Delta       =   ROUND(COALESCE(BPFBill.BPFCharge,0)-COALESCE(CSTBill.CSTCharge,0),2)  
FROM
(
    SELECT  
            BPFACCT     =   acct_no                             , 
            BPFBillDate =   cast(billdate as date)              , 
            BPFCharge   =   SUM(charge)  
    FROM 
            cisbill b
        JOIN 
            cisbilldetail bd ON b.billid=bd.billid
    WHERE   
            billdate>=@BillDate
        AND 
            billdate<DATEADD(MONTH, 1, @BillDate)
    GROUP BY 
            acct_no, billdate
) BPFBill
FULL OUTER JOIN 
(
    SELECT  CSTACCT     =   acct_no                 , 
            CSTBillDate =   cast(bill_date as date) ,               
            CSTCharge   =   SUM(new_charges) 
    FROM 
            cst_bill
    WHERE 
            bill_date>=@BillDate
        AND 
            bill_date<DATEADD(MONTH, 1, @BillDate)
    GROUP BY
            acct_no, bill_date

) CSTBill 
    ON      BPFBill.BPFACCT=CSTBill.CSTACCT 
        AND 
            BPFBill.BPFBillDate=CSTBill.CSTBillDate

Appreciate any feedback!

You shouldn't use functions in search criteria, SQL Server can't use indexes for that. Use a date variable with value 20160201 and compare `bill_date >= @billmonth and bill_date < dateadd(month, 1, @billmonth)` — James Z, May 06 '16 at 18:12
@JamesZ - Thanks, updated the question, one of the dates had a time part which Is why I had that in there, casting to date now..@JamieD77 - Yes, its possible to have two records for the month in cst_bill — neuralgroove, May 06 '16 at 18:26
My question is more around the JOIN and the COALESCE statements in the outer query, is that the best way to do this? — neuralgroove, May 06 '16 at 18:28
@neuralgroove you changed your example code up quite a bit.. are you wanting to show totals by billdate now? — JamieD77, May 06 '16 at 20:19
anyway.. you can use the query you have if you want.. i would just suggest that you group by `cast(billdate as date)` or you will see duplicate records when the times do not match. — JamieD77, May 06 '16 at 20:25

score -1 · Answer 1 · answered May 06 '16 at 18:28

I feel like this would give you a more accurate result..

DECLARE @StartDate DATETIME = '2/1/2016',
        @EndDate DATETIME
SET     @EndDate = DATEADD(MONTH, 1, @BillDate)

SELECT  ACCT_NO,
        MM,
        YY,
        BPFCharge = SUM(BPFCharge),
        CSTCharge = SUM(CSTCharge)
FROM 
(
    SELECT  acct_no,
            MM = MONTH(billdate),
            YY = YEAR(billdate),
            BPFCharge = charge
            CSTCharge = 0.00
    FROM    cisbill b
            JOIN cisbilldetail bd ON b.billid = bd.billid
    WHERE   billdate >= @StartDate
            AND billdate < @EndDate
    UNION ALL
    SELECT  acct_no,
            MONTH(bill_date),
            YEAR(Bill_date),
            0.00,
            new_charges
    FROM    cst_bill
    WHERE   bill_date >= @StartDate
            AND bill_date < @EndDate
) t
GROUP BY ACCT_NO,
        MM,
        YY

Can this Full Outer Join query be simplified?

1 Answers1