September 9, 2015 at 11:27 am
Sean, I'm getting an invalid column error on 'GrantColumn' and 'PartDColumn'
September 9, 2015 at 11:28 am
Luis...putting together the data example now.
September 9, 2015 at 12:18 pm
cory.bullard76 (9/9/2015)
Sean, I'm getting an invalid column error on 'GrantColumn' and 'PartDColumn'
Ummm....that would be because I have no clue what columns value you want. I have no idea what your column names are because we still haven't seen them. This is starting to take on the same frustration as peeling M&M's in the hot sun. If you can provide details we can provide answers. It is up to you how this proceeds. I am not going to put any more effort into this until we get the details required for us to be able to answer.
_______________________________________________________________
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 1:07 pm
try this....using Luis code.....all I have done is put in sample data based on what you told us.
if this isnt right....please save yourself (and us trying to help you) some time and stress and post relevant sample data and expected results
in case you missed my original post on how to post some sample data here it is again...(click on the bold text below)
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
CREATE TABLE #INSCOMP(
patientID INTEGER NOT NULL
,payor VARCHAR(21) NOT NULL
,BINNO INTEGER NOT NULL
);
INSERT INTO #INSCOMP(patientID,payor,BINNO) VALUES (1234,'Commercial',4321);
INSERT INTO #INSCOMP(patientID,payor,BINNO) VALUES (1234,'Part D',789);
INSERT INTO #INSCOMP(patientID,payor,BINNO) VALUES (5678,'Grant',8765);
WITH CTE
AS (
SELECT
ROW_NUMBER() OVER( PARTITION BY PatientId
ORDER BY CASE
WHEN payor = 'Commercial' THEN 1
WHEN payor = 'Grant' THEN 2
WHEN payor = 'Part D' THEN 3
END) AS rn
, BINNO
, PatientId
, Payor
FROM #INSCOMP)
SELECT
PatientID
,Payor
,BINNO
FROM CTE
WHERE rn = 1;
DROP TABLE #INSCOMP
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply