Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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...
0
britpopfan74
Asked:
britpopfan74
  • 2
  • 2
1 Solution
 
britpopfan74Author Commented:
sample data
0
 
PaulCommented:
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?
0
 
PaulCommented:
here's what I'm thinking you might be able to do (without knowing what comes after the join to vendor):
SELECT
        CK.CHECK_NUM
      , MAX(MCD.PROVIDER)                                  AS PROVIDER#
      , MAX(ISNULL(P.LAST_NAME, V.LAST_NAME))              AS PLNAME
      , MAX(ISNULL(P.FIRST_NAME, ''))                      AS PFNAME
      , MAX(ISNULL(P.PRIMARY_BUSINESS_ZIP, V.BILLING_ZIP)) AS ZIP
      , SUM([CK.CHECK_AMT])                                AS TOTAL_AMT
FROM [dbo].[MASTER_CLAIM#CHECK_INFO] CK
INNER JOIN MASTER_CLAIM#DETAIL mcD
                ON CK.[DOCUMENT] = mcD.[DOCUMENT]
        INNER JOIN dbo.PHYSICIAN P
                ON MCD.PROVIDER = P.PROVIDER_ID
        INNER JOIN dbo.VENDOR V
                ON MCD.VENDOR = V.VENDOR_NUM
WHERE [CK.PAY_DT] >= @FPAY
        AND ((CK.CHECK_NUM IS NOT NULL)
        AND (CK.CHECK_AMT <> 0))
GROUP BY
        CK.CHECK_NUM

Open in new window

0
 
britpopfan74Author Commented:
PortletPaul, thank you so much - that did the job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now