I have an SSRS report that calls a simple query. The query is trying to sum line items for A.INVC_ID which is does properly. My goal is to show, on the report, the sum of these per distinct A.CHK_NO.
Right now A.CHK_NO is repeating. What is the best way to accomplish this? The query is below. Your help is always appreciated
FROM ACH_TEMP_VENDORPAYMENTS A
INNER JOIN WEBAPP_CP.DELTEK.V_VEND V
ON V.VEND_ID = A.PAY_VEND_ID
WHERE A.PAY_VEND_ID = '1020020'
GROUP BY A.PAY_VEND_ID
,A.VCHR_NO
,A.INVC_ID
,A.CHK_NO
,A.CHK_DT
,A.CHK_AMT
,A.INVC_DT
,V.VEND_ID
,V.VEND_NAME
The rows are repeating because for a given CHK_NO, there is more than one distinct value of some of the other columns. If your query is like shown below, it would give you one row per CHK_NO
SELECT
A.CHK_NO ,
SUM(LN_CHG_CST_AMT) AS INVC_AMT
FROM
ACH_TEMP_VENDORPAYMENTS A
INNER JOIN WEBAPP_CP.DELTEK.V_VEND V
ON V.VEND_ID = A.PAY_VEND_ID
WHERE
A.PAY_VEND_ID = '1020020'
GROUP BY
A.CHK_NO
Now if you need to show the other columns, you have to decide which one of the possible multiple values you want to show. For example, you could do the following for PAY_VEND_ID.
SELECT
A.CHK_NO ,
MAX(A.PAY_VEND_ID) AS PAY_VEND_ID ,
SUM(LN_CHG_CST_AMT) AS INVC_AMT
FROM
ACH_TEMP_VENDORPAYMENTS A
INNER JOIN WEBAPP_CP.DELTEK.V_VEND V
ON V.VEND_ID = A.PAY_VEND_ID
WHERE
A.PAY_VEND_ID = '1020020'
GROUP BY
A.CHK_NO
But that may not necessarily be the logic you want to use. So the task is to determine how you pick one value out of many for the other columns.
