Solved

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

Posted on 2013-11-05
4
199 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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 how to maximize accessibility options in an Excel workbook for users with accessibility issues.
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 …

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now