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
71 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: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 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

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.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 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