Solved

Only apply a value from one table to a single row in another table where there could be multiple related records

Posted on 2015-02-05
6
72 Views
Last Modified: 2015-02-05
Experts,

I will simplify a bit for the example.
I have a detail table where I need 5 columns. I could have multiple records for the same member. I want to ensure the member is active that month and that is in a second table. I could do a join but that would put an active flag in each record. I only want the flag in on record.

Output should be:

Member          InvoiceNBR      YrMonth       Price    ActiveFlag
-----------------------------------------------------------------------------------
xyz                     789                  201409         55.25         1
xyz                     523                  201409           6.50         0


I don't want to show the member having an ActiveFlag on all records.  The reason is how the users will use this output. I just have to meet their requirements.

Member          InvoiceNBR      YrMonth       Price    ActiveFlag
-----------------------------------------------------------------------------------
xyz                     789                  201409         55.25         1
xyz                     523                  201409           6.50         1


any assistance is appreciated. I've tried CTE's, Row_number function. I just cant get it to do what I want.

Thanks in advance.
0
Comment
Question by:jvoconnell
[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
6 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40591978
Could you post some sample rows from the two tables please?

From what you've posted so far, I wouldn't know which of the multiple records (based on member, YrMonth) to update.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40591998
We'll need to make some reference to the other table, if that is active is determined.

What are the relevant columns for that table?

How do we know which row in the first table to mark as active?  The first row where the amount reaches what was owed to be active?  Or what?
0
 
LVL 1

Author Comment

by:jvoconnell
ID: 40592008
I mentioned I simplified the example, so the code doesnt represent the origainal question. Concept the same. I know some of the CTEs are redundant but it's more for sanity check at the moment.


 WITH CTE_CLAIMS
 (MemberDemographicID,		ClaimNBR,		StandardAdjustedCostAMT,	CurrentAdjustedCostAMT,		AgeNBR, YrMonth)
 AS
 (SELECT  MED.MemberDemographicid
		,MED.ClaimNBR
		,MED.StandardAdjustedCostAMT
		,MED.CurrentAdjustedCostAMT
		,AGE.AgeNBR
		,DD.DateOfServiceYearMonthNBR
					FROM Payer.HealthPlanDM.MedicalClaimFact AS MED
		INNER JOIN Payer.HealthPlanDM.MemberAgeDimension AS AGE
		ON MED.AgeID = AGE.AgeID
		INNER JOIN Payer.HealthPlanDM.DateOfServiceDimension DD
		ON MED.DateOfServiceID = DD.DateOfServiceID
		WHERE DD.DateOfServiceYearMonthNBR BETWEEN 201409 and 201410
		AND MED.MemberDemographicID = 11)
, CTE_MM
(MemberDemographicID, EligibilityYearMonthNBR, ActiveFlg)
AS
(
SELECT MEM.MemberDemographicID, MEM.EligibilityYearMonthNBR, MEM.ActiveFLG 
      FROM Payer.HealthPlanDM.MemberMonthDetailFact AS MEM
   WHERE MEM.MemberDemographicID = 11
   AND EligibilityYearMonthNBR BETWEEN 201409 AND 201410
   GROUP BY MEM.MemberDemographicID, MEM.EligibilityYearMonthNBR
)		
SELECT A.MemberDemographicNUM,		A.ClaimNBR,		A.StandardAdjustedCostAMT,	A.CurrentAdjustedCostAMT,		A.AgeNBR, A.YrMonth
, B.MemberDemographicID, B.EligibilityYearMonthNBR, B.MemberMonthsNBR
FROM CTE_CLAIMS A
FULL OUTER JOIN CTE_MM B
ON A.MemberDemographicID = B.MemberDemographicID
AND A.YrMonth = B.EligibilityYearMonthNBR

Open in new window


THis produces 2 records but both get an ActiveFLG = 1
I had tried ROw_number in one of these at one point but I counldt get that to help me.

We don't care which record gets the flag assigned. Only that its a single record based on the the MemberID and YrMonth (ELigibilityYrMonth)

Does this help?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40592143
Can you just use ROW_NUMBER() in a subquery, then use a case expression?

SELECT
      MemberDemographicNUM
    , ClaimNBR
    , StandardAdjustedCostAMT
    , CurrentAdjustedCostAMT
    , AgeNBR
    , YrMonth
    , MemberDemographicID
    , EligibilityYearMonthNBR
    , MemberMonthsNBR
    , CASE
            WHEN rn = 1 THEN 1
            ELSE 0
      END AS IS_ACTIVE
FROM (
            SELECT
                  A.MemberDemographicNUM
                , A.ClaimNBR
                , A.StandardAdjustedCostAMT
                , A.CurrentAdjustedCostAMT
                , A.AgeNBR
                , A.YrMonth
                , B.MemberDemographicID
                , B.EligibilityYearMonthNBR
                , B.MemberMonthsNBR
                , ROW_NUMBER() OVER (PARTITION BY A.MemberDemographicNUM
                  ORDER BY B.EligibilityYearMonthNBR DESC, B.MemberMonthsNBR DESC) AS RN
            FROM CTE_CLAIMS A
                  FULL OUTER JOIN CTE_MM B ON A.MemberDemographicID = B.MemberDemographicID
                  AND A.YrMonth = B.EligibilityYearMonthNBR
      ) AS DERIVED
;

Open in new window


If that suggestion doesn't help please supply sample data and the corresponding expected result.
0
 
LVL 1

Author Closing Comment

by:jvoconnell
ID: 40592243
Thank you.
This gets me to our desired solution.
I appreciate it.


This is a great site!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40592320
>>"This is a great site!"

:) thanks, we aim to please.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with another query 10 39
Data encryption options between SQL DBs 3 32
Running Total Using new MS SQL Function 21 52
Need SQL Update 2 8
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

726 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