I'm trying to build a query that links two tables that has a counts a given column when their key values match, count the same column when they don't match, and count total in that column. Below is the simplified query.
Thank you for your help ahead of time.
SyntaxEditor Code Snippet
create MULTISET volatile table RDC_DOOR_PILOT_TRAILER as()WITH DATA UNIQUE PRIMARY INDEX ( RDC_DRPILT_BILL_NBR, RDC_NBR,STR_NBR,FSCL_YR_WK_KEY_VAL,FSCL_YR) ON COMMIT PRESERVE ROWS; select case when A.OUT_FRT_BILL_NBR in RDC_DOOR_PILOT_TRAILER.RDC_DRPILT_BILL_NBR then count(distinct [PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO].OUTB_FRT_BILL_NBR) end as DOOR_TRAILER_CNT,case when A.OUT_FRT_BILL_NBR <> RDC_DOOR_PILOT_TRAILER.RDC_DRPILT_BILL_NBR then count(distinct [PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO].OUTB_FRT_BILL_NBR) end as NONDOOR_TRAILER_CNT,count(distinct [PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO].OUTB_FRT_BILL_NBR) end as Total_TRAILER_CNT, from PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO A full outer join RDC_DOOR_PILOT_TRAILER on RDC_DOOR_PILOT_TRAILER.RDC_DRPILT_BILL_NBR =A.OUTB_FRT_BILL_NBR Group by 1,2,3,4,5;
Are you aiming for something closer to this?
select count(distinct CASE when A.OUT_FRT_BILL_NBR = B.RDC_DRPILT_BILL_NBR THEN A.OUTB_FRT_BILL_NBR end) as DOOR_TRAILER_CNT ,count(distinct CASE when A.OUT_FRT_BILL_NBR <> B.RDC_DRPILT_BILL_NBR THEN A.OUTB_FRT_BILL_NBR end) as NONDOOR_TRAILER_CNT ,count(distinct A.OUTB_FRT_BILL_NBR) end as Total_TRAILER_CNT from PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO A full outer join RDC_DOOR_PILOT_TRAILER B on B.RDC_DRPILT_BILL_NBR =A.OUTB_FRT_BILL_NBR
You seem to expect table A to contain all the values and table B to be a subset. If that's true, LEFT JOIN is sufficient and you don't need FULL JOIN.
If there potentially are many duplicate values, eliminate duplicates before the join (and use COUNT instead of COUNT DISTINCT); for example
... from (SELECT OUT_FRT_BILL_NBR FROM PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO GROUP BY 1) A ...
By the way, don't use SQL Server notation [square brackets].