Solved

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

Posted on 2013-11-05
4
217 Views
Last Modified: 2013-11-06
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
Comment
Question by:britpopfan74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Author Comment

by:britpopfan74
ID: 39625870
sample data
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39626206
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39626216
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
 

Author Closing Comment

by:britpopfan74
ID: 39627301
PortletPaul, thank you so much - that did the job!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question