Rolling up all providers under one check# - top 1 subquery?
Posted on 2013-11-05
Thank you in advance for all help!
Currently I have this query that is generating data like the attachment where there is a lot of redundancy of check numbers being paid to differing providers for varying dollar amounts.
What I want to do is to compact this data and have one row per check number assigned to a provider -- whichever is the first provider in the list (top 1?)
So in the example data, even though there are many providers receiving check #82405,
it would be represented as one row of:
CHECK_NUM PROVIDER# PLNAME PFNAME ZIPCODE TOTAL_AMT
82405 131246 LAB 12345 sum(82405 checks)
The query I am using:
SELECT DISTINCT MAXD.CHECK_NUM
, MCD.PROVIDER AS PROVIDER#
, ISNULL(P.LAST_NAME, V.LAST_NAME) AS PLNAME
, ISNULL(P.FIRST_NAME, '') AS PFNAME
, ISNULL(P.PRIMARY_BUSINESS_ZIP, V.BILLING_ZIP) AS ZIP
FROM MASTER_CLAIM#DETAIL mcD
(SELECT DISTINCT CHECK_NUM
, SUM([CHECK_AMT]) AS TOTAL_AMT
WHERE [PAY_DT] >= @FPAY
AND ((CHECK_NUM IS NOT NULL)
AND (CHECK_AMT <> 0))
GROUP BY [PAY_DT]
) AS MAXD
ON (MAXD.[DOCUMENT] = MCD.[DOCUMENT]) INNER JOIN dbo.MASTER_CLAIM#CHECK_INFO CK ON mcD.[DOCUMENT] = CK.[DOCUMENT]
INNER JOIN dbo.PHYSICIAN P ON MCD.PROVIDER = P.PROVIDER_ID
INNER JOIN dbo.VENDOR V ON MCD.VENDOR = V.VENDOR_NUM...