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
69 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
string fuctions 4 26
Get the latest status 8 32
sql server query 6 9
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…
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…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 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