Solved

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

739 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