?
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
Medium Priority
?
79 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

Expert Comment

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

:) thanks, we aim to please.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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