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
64 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
6 Comments
 
LVL 18

Expert Comment

by:SimonAdept
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:ScottPletcher
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

19 Experts available now in Live!

Get 1:1 Help Now