Solved

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

Posted on 2013-11-05
4
208 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
  • 2
  • 2
4 Comments
 

Author Comment

by:britpopfan74
ID: 39625870
sample data
0
 
LVL 48

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 48

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I have put this article together as i needed to get all the information that might be available already into one general document that could be referenced once without searching the Internet for the different pieces. I have had a few issues where…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

832 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