March 23, 2017 at 10:13 am
I attached an example of a problem that I'm having with my data. On the example you see that I have
Primary Policy # Primary Payer Secondary Policy # Secondary Payer
My problem is that the one Primary Payer (Med One Insurance in this example) Primary Payer needs to show in the 3 rows above it.
To populate those fields I'm using case statements:
case when claim.claimno = 1 then policy [Primary Policy #]
,case when claim.claimno = 1 then payer [Primary Payer]
,case when claim.claimno = 2 then policy [Secondary Policy #]
,case when claim.claimno = 2 then payer [Secondary Payer]
Is there a way to do what I'm thinking?
March 23, 2017 at 11:11 am
If you want help, you should post data in a CONSUMABLE format. An Excel file is not consumable.
This is consumable:DECLARE @Policies TABLE(MRN INT, [Primary Policy #] BIGINT, [Primary Payer] VARCHAR(50), [Secondary Policy #] BIGINT, [Secondary Payer] VARCHAR(50))
INSERT @Policies(MRN, [Primary Policy #], [Primary Payer], [Secondary Policy #], [Secondary Payer])
VALUES
(1111, NULL, NULL, 33333333333333, 'N COPAY CARD'),
(1111, NULL, NULL, 444444444444444, 'P COPAY CARD'),
(1111, NULL, NULL, 555555555555555, 'T COPAY CARD'),
(1111, 222222222222222, 'Med One Insurance', NULL, NULL)
The main reason for doing this is that people are much more likely to work on your problem if you make it easier for them to do so. But it also has the benefit of explicitly defining the data types for each of the columns, which may be important, particularly since the solution I have so far uses a MAX() and MAX() of character and numeric fields can produce different results for pseudo-numeric data.
SELECT MRN,
MAX([Primary Policy #]) OVER(PARTITION BY MRN) AS [Primary Policy #],
MAX([Primary Payer])OVER(PARTITION BY MRN) AS [Primary Payer],
[Secondary Policy #],
[Secondary Payer]
FROM @Policies
Drew
PS: You really shouldn't use spaces or symbols in your field names.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 11:15 am
it's hard to tell without knowing what the source tables structure is and the data in them look like. Is the MRN number what relates these records together?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply