asked on

Rolling up all providers under one check# - top 1 subquery?

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
, MAXD.TOTAL_AMT
INTO #temp_CLAIMS
FROM MASTER_CLAIM#DETAIL mcD
INNER JOIN
(SELECT DISTINCT CHECK_NUM
, [DOCUMENT]
, SUM([CHECK_AMT]) AS TOTAL_AMT
FROM [dbo].[MASTER_CLAIM#CHECK_INFO]
WHERE [PAY_DT] >= @FPAY
AND ((CHECK_NUM IS NOT NULL)
AND (CHECK_AMT <> 0))
GROUP BY [PAY_DT]
, CHECK_NUM
, [DOCUMENT]
) 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...

britpopfan74

ASKER

sample data

PortletPaul

Please note, there is absolutely nothing achieved by using "select distinct" with a query or subquery that is using a "group by" because "group by" already produces unique rows

                SELECT DISTINCT --<< distinct is not at all  useful here
                        CHECK_NUM
                      , [DOCUMENT]
                      , SUM([CHECK_AMT]) AS TOTAL_AMT
                FROM [dbo].[MASTER_CLAIM#CHECK_INFO]
                WHERE [PAY_DT] >= @FPAY
                        AND ((CHECK_NUM IS NOT NULL)
                        AND (CHECK_AMT <> 0))
                GROUP BY
                        [PAY_DT]
                      , CHECK_NUM
                      , [DOCUMENT]

Open in new window

In your question the query ends with an ellipse indicating there is more to it - but that may be important to a revised query. Can we see the rest?

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

britpopfan74

ASKER

PortletPaul, thank you so much - that did the job!