September 9, 2015 at 9:23 am
I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this
case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO
September 9, 2015 at 9:38 am
Just add more conditions:
case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO
when inscomp.payor = 'Grant' then INSCOMP.BINNO
when inscomp.payor = 'Part D' then INSCOMP.BINNO END
September 9, 2015 at 9:44 am
Thanks...That's what I thought at 1st. But, it's pulling the BINNO for Grant instead of Commercial when I use that. I want Commercial 1st if they have it.
September 9, 2015 at 9:59 am
cory.bullard76 (9/9/2015)
Thanks...That's what I thought at 1st. But, it's pulling the BINNO for Grant instead of Commercial when I use that. I want Commercial 1st if they have it.
You need to use a different column when you want a different column. In the example that Luis posted it is always getting the same column.
case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO
when inscomp.payor = 'Grant' then INSCOMP.GrantColumn
when inscomp.payor = 'Part D' then INSCOMP.PartDColumn END
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 10:12 am
cory.bullard76 (9/9/2015)
Thanks...That's what I thought at 1st. But, it's pulling the BINNO for Grant instead of Commercial when I use that. I want Commercial 1st if they have it.
You might be wanting to group rows, but I can't be sure.
How about sharing some sample data and expected results with us? When posting sample data, please do it by using DDL and INSERT statements as explained in the article in my signature.
September 9, 2015 at 10:15 am
I'm not explaining it clear enough. I want it to populate the same column. If it's Commercial give me that BINNO.....if the patient doesn't have Commercial....populate that column with the BINNO for Grant...etc
September 9, 2015 at 10:18 am
For ex...patient 1234 has Commercial Insurance and BINNO 4321
patient 1234 has Part D Insurance and BINNO 789
patient 5678 only has Grant and BINNO 8765
I'd want the BINNO column to display
BINNO
4321
8765
September 9, 2015 at 10:26 am
why dont you post some example data....would make it easier I am sure
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 9, 2015 at 10:32 am
cory.bullard76 (9/9/2015)
I'm not explaining it clear enough. I want it to populate the same column. If it's Commercial give me that BINNO.....if the patient doesn't have Commercial....populate that column with the BINNO for Grant...etc
Did you look at my post? It puts those other values in a single column. If that isn't what you want then you should read and follow the instructions found at the link posted by JLS.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 10:36 am
Sean, I want the data in a single column
September 9, 2015 at 10:41 am
I'm thinking it's some kid of order logic. If the Payer is commercial then BINNO else if Payer = Grant then BINNO...etc
September 9, 2015 at 10:43 am
cory.bullard76 (9/9/2015)
Sean, I want the data in a single column
IT IS in a single column. That is what the case expression is used for. Look at the code I posted, try it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 10:51 am
Wouldn't your case statement error out do the columns in the Then portion containing columns that don't exist?
September 9, 2015 at 10:57 am
cory.bullard76 (9/9/2015)
Wouldn't your case statement error out do the columns in the Then portion containing columns that don't exist?
HUH??? How can columns not exist for some rows? I will ask again, DID YOU TRY IT?
You need to post ddl and sample data here. This is going round and round because I can't tell what you are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 11:09 am
My crystal ball is showing a query like this:
WITH CTE AS(
SELECT ROW_NUMBER() OVER( PARTITION BY Patient
ORDER BY CASE WHEN payor = 'COMMERCIAL' then 1
when payor = 'Grant' then 2
when payor = 'Part D' then 3 END) rn,
BINNO,
Patient,
Payor
FROM SomeTable
)
SELECT BINNO,
Patient,
Payor
FROM CTE
WHERE rn = 1;
I haven't calibrated it lately, so it might not be what you need. If you want better help, post what you've been asked several times.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply