February 26, 2015 at 1:49 pm
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
from Allergy
cross apply (
values( 'PMPM',PMPM,[PMPM Percentile Rank])
,('cave',Cave,[Cave Percentile Rank])
,('Many Allergy Tests',[Many Allergy Tests],[Many Allergy Tests Percentile Rank])
,('Asthma Spirometry',[Asthma Spirometry],[Asthma Spirometry Percentile Rank])
,('Allergy Asthma IP ED',[Allergy Asthma IP ED],[Allergy Asthma IP ED Percentile Rank])
,('ASthma Medication Ratio',[Asthma Medication Ratio],[Asthma Medication Ratio Percentile Rank])
,('Allergy IP ED Followup',[Allergy IP ED Followup],[Allergy IP ED Followup Percentile Rank])
,('Immunotherapy Routine Care',[Immunotherapy Routine Care],[Immunotherapy Routine Care Percentile Rank])
,('Immunotherapy Cost PUMPM',[Immunotherapy cost PUMPM],[Immunotherapy cost PUMPM Percentile Rank])
,('Proportion Intradermal Tests',[Proportion Intradermal Tests],[Proportion Intradermal Tests Percentile Rank])
) x(metricname,MetricValue, metricpercentilerank)
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
PMPM Percentile Rank (nvarchar(255), null)
PMPM PGS (nvarchar(255), null)
PMPM Rank Number (nvarchar(255), null)
RA PMPM (nvarchar(255), null)
RA PMPM Percentile Rank (nvarchar(255), null)
RA PMPM PGS (nvarchar(255), null)
RA PMPM Rank Number (nvarchar(255), null)
RA HEDIS (nvarchar(255), null)
RA HEDIS Percentile Rank (nvarchar(255), null)
RA HEDIS PGS (nvarchar(255), null)
RA HEDIS Rank Number (nvarchar(255), null)
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
Thank you in advance.
February 28, 2015 at 11:21 am
I'm not sure if the question I posted a couple of days ago is asking about something that's not possible, too much extraneous detail to wade through to have time to answer, or something else. I'm going to do some pruning on it to see if it makes it faster to review. I know it would be funny if everyone who looked at it posted "dunno", "no can do", "code is so wrong I wouldn't know where to begin", ...
😉
February 28, 2015 at 4:01 pm
I think it might help if you took a step back and explained what you are trying to achieve overall.
Sounds like you like your query fine but want the same query with other tables. Why?
Are you trying to avoid having to rewrite the query 30 times?
Is the query for a procedure that takes table name as a parameter?
Do all the tables need to be unpivoted in one query together? And if so, can the list of tables change dynamically?
February 28, 2015 at 5:57 pm
Thanks for the reply Nevyn.
I think it might help if you took a step back and explained what you are trying to achieve overall.
Sounds like you like your query fine but want the same query with other tables. Why?
I had to write basically the same script 46 times and change the columns that are used because each specialty (like Allergy and Cardiology in the example) has its own set of measures that I need to unpivot using CROSS APPLY. Not only would that take a long time but it's error prone. The measures (column names) are often long, embedded with spaces and full of arcane text. I know there should be a better way to make this dynamic but I don't know how. And the table names could change next month but what's far more likely is that the measure names (columns) will change. An example of a measure name is 'Allergy IP ED Routine Care'.
Are you trying to avoid having to rewrite the query 30 times?
Yes and to reduce error from me miskeying or missing one.
Is the query for a procedure that takes table name as a parameter?
Not yet. Should it? And can I also have a way to read the names of the columns from the table?
Do all the tables need to be unpivoted in one query together? And if so, can the list of tables change dynamically?
The way I've done it is to have 46 separate queries that I can turn into stored procedures, each one of which will be the dataset for an SSRS report. It is the first time I have made separate reports for each but if I made one report with different specialties the columns would be different for each. Is there a better way. i feel like what I'm doing now with work but that I've written an inelegant kludge that will be a nightmare for me to manage and even worse if someone tries to use it in the future. As to whether the table names change : maybe not but possible. Will the column names change next month? Very likely. I import from Excel workbook that another company provides. I import each sheet to a separate table (all at once with SSIS import wizard in SSMS). Even if they put another space in a column name, it will be "different".
Thanks again for any help or advice you can provide!
February 28, 2015 at 6:43 pm
Ok, now we are getting closer. Having some background is very helpful.
I am still a bit unclear from your comment as to whether you could use one report or multiples.
Also unclear is how you can tell when looking at any of these tables which columns are the ones to be unpivoted? You gave us a static example of columns to unpivot, but if this was going to be done dynamically you would need some sort of profile of what columns to do this with.
Also if the column names can suddenly change on you, does that include the ones that are not being unpivoted? Or are there a list of columns always the same for every table (with more static names ) and everything else needs to be unpivoted. Do you use these tables for anything but the SSRS report(s)
Assuming you are stuck with this importing method, and could use a single report, I'd probably suggest a stored procedure with a table name parameter as the source for the one report. That report would try to build your query with dynamic SQL. I might try to throw together a sample.
Obviously even with that things would not be ideal. In a perfect world you'd have a SSIS package doing the import each time into pre-existing tables, and not putting measures in strings but in data types that made sense for the information they contained. But if the data is constantly shifting I recognize that could be a challenge.
Another possibility would be to look at doing the unpivoting programmatically (vba) when you get the excel sheet so that you can get it into a consistent format that a pre-made SSIS package could then import. Then you could have one table (with a field for SpecialtyName) and your report could query it where specialtyname matched a parameter. That would at least be more automated.
February 28, 2015 at 6:49 pm
I've been trying to figure out how to answer this for a couple of days at least...
I faced a situation like this once, but it was in Access (yup, SQL Server's red haired stepchild). You could maybe use Access to grab the column names (which contain information), and somewhat untwist the data, and then once you clean that up (maybe writing to a local Access table), write the whole thing to SQL Server.
Then handy thing about Access is that you can use VBA, which lets you deal with the columns... maybe it's just that I find VBA easier to deal with than dynamic SQL and the sys.Columns table...
If you're interested, let me know and I'll see if I can come up with a way to "preprocess" this stuff in Access.
Is there any way you could fix the data before getting it into SQL Server? So that your table might look something like this:
TestID,
Reading, -- the value
PercentileRank,
PGS,
RankNumber
The problem I had was that people were storing information in column names, and that makes a huge mess. Especially if you have to filter and group values.
February 28, 2015 at 6:50 pm
Looking at your example more closely, we would also need a way to tell which columns we are unpivoting together (what is a value vs rank, what value belongs with what rank, what name to give, and how to reliably tell them apart).
February 28, 2015 at 9:08 pm
Nevyn (2/28/2015)
Looking at your example more closely, we would also need a way to tell which columns we are unpivoting together (what is a value vs rank, what value belongs with what rank, what name to give, and how to reliably tell them apart).
Thanks for continuing to work with me on this one. If I understand what you're asking, the columns that will stay the same (the same in meaning but may differ from the way they're specified by person putting Excel workbook together at the other company who I cannot communicate with). But, let's say they will be the same and move on to the ones that should be unpivoted. I have been asked to get the first two of the four piece group of columns (example: PMPM and PMPM Percentile Rank. Another example: Cave and Cave Percentile Rank).
Does this explain enough? I'm not sure if I am 100% certain about what you're asking. Again, Thanks!
February 28, 2015 at 9:17 pm
Nevyn (2/28/2015)
Ok, now we are getting closer. Having some background is very helpful.I am still a bit unclear from your comment as to whether you could use one report or multiples.
Also unclear is how you can tell when looking at any of these tables which columns are the ones to be unpivoted? You gave us a static example of columns to unpivot, but if this was going to be done dynamically you would need some sort of profile of what columns to do this with.
Also if the column names can suddenly change on you, does that include the ones that are not being unpivoted? Or are there a list of columns always the same for every table (with more static names ) and everything else needs to be unpivoted. Do you use these tables for anything but the SSRS report(s)
Assuming you are stuck with this importing method, and could use a single report, I'd probably suggest a stored procedure with a table name parameter as the source for the one report. That report would try to build your query with dynamic SQL. I might try to throw together a sample.
Obviously even with that things would not be ideal. In a perfect world you'd have a SSIS package doing the import each time into pre-existing tables, and not putting measures in strings but in data types that made sense for the information they contained. But if the data is constantly shifting I recognize that could be a challenge.
Another possibility would be to look at doing the unpivoting programmatically (vba) when you get the excel sheet so that you can get it into a consistent format that a pre-made SSIS package could then import. Then you could have one table (with a field for SpecialtyName) and your report could query it where specialtyname matched a parameter. That would at least be more automated.
Thanks Nevyn. I also responded just now to your later response. It is unfortunate that most of the dat I get is heavily pivoted. Some good news: the columns at the beginning of the tables are the same for all 46. Some I need for report, some I ignore. My original example showed a cross apply script for the Allergy table. The table description showed the Cardiology table to show the variation. Some tables not shown have about 10 4 column quartet (need first 2, ignore second 2). So use PMPM and PMPM Percentile Rank, ignore the others.
I have other scripts to find the min and max for each specialty (table) and within each measure (column) within a condition. I also have a weights table, etc. This project is a kludge and I had/have 4 days to do it. I can move forward with the manual work I've done to get the data in a workable format to use with SSRS but I sure hope I can get this partially automated before next month if not in the next two days.
Yes, I would like to use the data for other things besides just SSRS but the SSRS reports are my current requirement.
Thanks again.
February 28, 2015 at 9:25 pm
pietlinden (2/28/2015)
I've been trying to figure out how to answer this for a couple of days at least...I faced a situation like this once, but it was in Access (yup, SQL Server's red haired stepchild). You could maybe use Access to grab the column names (which contain information), and somewhat untwist the data, and then once you clean that up (maybe writing to a local Access table), write the whole thing to SQL Server.
Then handy thing about Access is that you can use VBA, which lets you deal with the columns... maybe it's just that I find VBA easier to deal with than dynamic SQL and the sys.Columns table...
If you're interested, let me know and I'll see if I can come up with a way to "preprocess" this stuff in Access.
Is there any way you could fix the data before getting it into SQL Server? So that your table might look something like this:
TestID,
Reading, -- the value
PercentileRank,
PGS,
RankNumber
The problem I had was that people were storing information in column names, and that makes a huge mess. Especially if you have to filter and group values.
Thanks pietlinden, I appreciate your reply. I don't know much VBA (just a little for SSRS). Would this automate the process?
It sounds like it might but I have a worker who doesn't know VBA so he spends endless days processing data everyday to massage the data with Access. It takes him a week to do one monthly report and it's a disaster when the boss (he's non technical) said to him last week: You have to regenerate the reports because we have some new data that just came in. I feel so sorry for the guy. But I digress. You know VBA and he does not. It would probably cheer him up a lot to see an automated VBA example even if his tasks are different from mine.
If you have the time and interest, I would very much appreciate an example.
February 28, 2015 at 10:30 pm
Ok, I have thrown together a quick sample for you.
First, an explanation.
You need to do this with dynamic sql. One query builds a string that contains your actual query, and then you execute that string with sp_executeSQL.
In order to build the string with the first query we need to query sys.syscolumns to get the column names from your table, find the ones grouped together (first 2 in groups of 4), and concatenate them for each pair in your VALUES collection). Then we concatenate all the pairs into the larger query.
For this example, I built a sample table called 'Rheumatology'. I assumed that the columns that did not get unpivoted from your initial query were first in the table, and the only ones to be skipped (so I started at column 8). I threw in a single demonstration row.
Note, this type of solution can still be messed up if any of those 'static' columns change, which you seem to have indicated may be possible.
Anyway, here is my testing table (fyi, it is considered good etiquette to provide test table definitions and data when asking a question, and if you had done it my solution would be far closer to what you need)
CREATE TABLE Rheumatology (
[uplift specialty] nvarchar(255) null
, [member po] nvarchar(255) null
,[practice unit name] nvarchar(255) null
, [final nomination status] nvarchar(255) null
,[final uplift status] nvarchar(255) null
, [final rank] nvarchar(255) null
, [final uplift percentage] nvarchar(255) null
,[GDR] nvarchar(255) null
,[GDR Percentile Rank] nvarchar(255) null
,[GDR PGS] nvarchar(255) null
,[GDR Rank Number] nvarchar(255) null
,[PMPM] nvarchar(255) null
,[PMPM Percentile Rank] nvarchar(255) null
,[PMPM PGS] nvarchar(255) null
,[PMPM Rank Number] nvarchar(255) null
,[RA PMPM] nvarchar(255) null
,[RA PMPM Percentile Rank] nvarchar(255) null
,[RA PMPM PGS] nvarchar(255) null
,[RA PMPM Rank Number] nvarchar(255) null
,[RA HEDIS] nvarchar(255) null
,[RA HEDIS Percentile Rank] nvarchar(255) null
,[RA HEDIS PGS] nvarchar(255) null
,[RA HEDIS Rank Number] nvarchar(255) null
)
Next, I add a simple, not very good testing column to make sure I really unpivot
INSERT [dbo].[Rheumatology]
VALUES (null,null,null,null,null,null,null,N'this holds GDR',N'this holds GDR Percentile Rank',null,null
,N'this holds PMPM',N'this holds PMPM Percentile Rank',null,null
,N'this holds RA PMPM',N'this holds RA PMPM Percentile Rank',null,null
,N'this holds RA HEDIS',N'this holds RA PMPM Percentile Rank',null,null)
And finally, the script, which could be put in a procedure with the table name as a parameter
DECLARE @Query nvarchar(max),
@tablename nvarchar(50)=N'Rheumatology'
;
-- First we build a small inline tally table
WITH E(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #8
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+4
FROM E, E x --Enough for 12 years
),getpairs AS ( -- Now we have to figure out the columns that go together in your unpivoted table, and concatenate them
select n,STUFF ((
SELECT ',' + CASE WHEN c.colid = t.N THEN '''' + c.name + ''',' + '['+c.name+']' ELSE '['+c.name+']' END
FROM sys.syscolumns c
JOIN sys.sysobjects o
ON o.id=c.id
WHERE o.xtype='u'
AND o.name=@tablename
AND c.colid IN (t.N,t.N+1)
FOR XML PATH('')
),1,1,'') pairs
FROM cteTally t
GROUP BY n
) -- Now we concatenate the groups of pairs into a larger string with the rest of the query
SELECT @query=N'select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
from ' + @tablename + '
cross apply (
values ' + STUFF ((
SELECT N'),(' + pairs
FROM getpairs
WHERE pairs IS NOT NULL
FOR XML PATH ('')),1,2,'')+')) x(metricname,MetricValue, metricpercentilerank)'
EXEC SP_EXECUTESQL @Query
All could be prettied up some more, but this is a sample to get you started.
Please let me know if you need clarification or help adapting it. This should at least tell you why I was asking the questions I was, and the various challenges and assumptions of this approach.
EDIT:Assuming I understood your comments correctly, the main thing you will need to change is the offset (here, the +4), so that you start with the correct column from syscolumns
February 28, 2015 at 10:51 pm
Nevyn (2/28/2015)
Ok, I have thrown together a quick sample for you.First, an explanation.
You need to do this with dynamic sql. One query builds a string that contains your actual query, and then you execute that string with sp_executeSQL.
In order to build the string with the first query we need to query sys.syscolumns to get the column names from your table, find the ones grouped together (first 2 in groups of 4), and concatenate them for each pair in your VALUES collection). Then we concatenate all the pairs into the larger query.
For this example, I built a sample table called 'Rheumatology'. I assumed that the columns that did not get unpivoted from your initial query were first in the table, and the only ones to be skipped (so I started at column 8). I threw in a single demonstration row.
Note, this type of solution can still be messed up if any of those 'static' columns change, which you seem to have indicated may be possible.
Anyway, here is my testing table (fyi, it is considered good etiquette to provide test table definitions and data when asking a question, and if you had done it my solution would be far closer to what you need)
CREATE TABLE Rheumatology (
[uplift specialty] nvarchar(255) null
, [member po] nvarchar(255) null
,[practice unit name] nvarchar(255) null
, [final nomination status] nvarchar(255) null
,[final uplift status] nvarchar(255) null
, [final rank] nvarchar(255) null
, [final uplift percentage] nvarchar(255) null
,[GDR] nvarchar(255) null
,[GDR Percentile Rank] nvarchar(255) null
,[GDR PGS] nvarchar(255) null
,[GDR Rank Number] nvarchar(255) null
,[PMPM] nvarchar(255) null
,[PMPM Percentile Rank] nvarchar(255) null
,[PMPM PGS] nvarchar(255) null
,[PMPM Rank Number] nvarchar(255) null
,[RA PMPM] nvarchar(255) null
,[RA PMPM Percentile Rank] nvarchar(255) null
,[RA PMPM PGS] nvarchar(255) null
,[RA PMPM Rank Number] nvarchar(255) null
,[RA HEDIS] nvarchar(255) null
,[RA HEDIS Percentile Rank] nvarchar(255) null
,[RA HEDIS PGS] nvarchar(255) null
,[RA HEDIS Rank Number] nvarchar(255) null
)
Next, I add a simple, not very good testing column to make sure I really unpivot
INSERT [dbo].[Rheumatology]
VALUES (null,null,null,null,null,null,null,N'this holds GDR',N'this holds GDR Percentile Rank',null,null
,N'this holds PMPM',N'this holds PMPM Percentile Rank',null,null
,N'this holds RA PMPM',N'this holds RA PMPM Percentile Rank',null,null
,N'this holds RA HEDIS',N'this holds RA PMPM Percentile Rank',null,null)
And finally, the script, which could be put in a procedure with the table name as a parameter
DECLARE @Query nvarchar(max),
@tablename nvarchar(50)=N'Rheumatology'
;
-- First we build a small inline tally table
WITH E(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #8
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+4
FROM E, E x --Enough for 12 years
),getpairs AS ( -- Now we have to figure out the columns that go together in your unpivoted table, and concatenate them
select n,STUFF ((
SELECT ',' + CASE WHEN c.colid = t.N THEN '''' + c.name + ''',' + '['+c.name+']' ELSE '['+c.name+']' END
FROM sys.syscolumns c
JOIN sys.sysobjects o
ON o.id=c.id
WHERE o.xtype='u'
AND o.name=@tablename
AND c.colid IN (t.N,t.N+1)
FOR XML PATH('')
),1,1,'') pairs
FROM cteTally t
GROUP BY n
) -- Now we concatenate the groups of pairs into a larger string with the rest of the query
SELECT @query=N'select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
from ' + @tablename + '
cross apply (
values ' + STUFF ((
SELECT N'),(' + pairs
FROM getpairs
WHERE pairs IS NOT NULL
FOR XML PATH ('')),1,2,'')+')) x(metricname,MetricValue, metricpercentilerank)'
EXEC SP_EXECUTESQL @Query
All could be prettied up some more, but this is a sample to get you started.
Please let me know if you need clarification or help adapting it. This should at least tell you why I was asking the questions I was, and the various challenges and assumptions of this approach.
EDIT:Assuming I understood your comments correctly, the main thing you will need to change is the offset (here, the +4), so that you start with the correct column from syscolumns
Wow. Thanks Nevyn! I will use this tomorrow and let you know how I'm doing. It's very late here and I am struggling to stay awake. I am sorry I didn't include test data, I will next time I post. I am so grateful for all your time and exercise in helping me. I never expected so much. I will post again.
March 1, 2015 at 9:16 am
Thank you Nevyn. Your script is just what I was hoping for. I do have to admit that I don't know how to successfully change the offset. The first column that starts the quartets of metrics for each metric is 21. When I ran your program as is the error (because I didn't provide you with data and the complete rheumatology table description to test with) was
[highlight=""]Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Total Specialists' to data type bit.[/highlight]
I tried changing the "+4" in your code
-- First we build a small inline tally table
WITH E(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #8
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+4
FROM E, E x --Enough for 12 years
{...snip...}
to 17 as the offset but then the message said:
The number of columns for each row in a table value constructor must be the same.
What part of the code should I adjust? The "select 1 union select 1..." part has 6 unioned together not 4 or 8 so is it there?
Here are a couple of rows of sample data (Is there a way to post a formatted table? I didn't want to post a screen shot because the data couldn't be used but here is a paste from Excel):
Uplift Specialty Member POMember Sub POPractice Unit NamePractice Unit IDPractice Unit AddressPrincipal Partner POTotal SpecialistsFinal Nomination StatusMember PO Nominated Member PO Agreement Principal Partner PO Nomination RequiredPrincipal Partner PO NominatedPrincipal Partner PO AgreementFinal Uplift Status Final Uplift PercentageFinal RankNumber of Ranked Practice UnitsPediatric Members PercentagePediatric FlagGDR GDR Percentile RankGDR PGSGDR Rank Number PMPMPMPM Percentile RankPMPM PGSPMPM Rank Number RA PMPMRA PMPM Percentile RankRA PMPM PGSRA PMPM Rank NumberRA HEDISRA HEDIS Percentile RankRA HEDIS PGSRA HEDIS Rank NumberComposite Score Eligible Composite ScoreComposite Score PGS Percentagecoremeasurepediatric
RheumatologySneeze and Wheeze,LLCThe Sneeze and Wheeze Undefined SubgroupDr. Feelgood's Rheumatology, PCB0000999999920000 Any Street ; Anytown XX 00002-3YesYesYesNoNoNoUplift5%28600.30%Not Pediatric88.40%18.30%No5028880.00%No13149041.70%No3688.90%68.30%No20Yes0.5570%00
RheumatologySneeze and Wheeze,LLCThe Sneeze and Wheeze Undefined SubgroupFlesh and Bones InternistsB0008888888811000 Nowhere Street; TheCity XX 00005 -2YesYesYesNoNoNoUplift5%18600.00%Not Pediatric92.20%63.30%No2328976.70%No15145268.30%No2087.90%25.00%No46Yes0.6160%00
In an unrelated question, what does " enough for 12 years mean please?
{snip}
,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #8
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+4
FROM E, E x --Enough for 12 years
),getpairs AS ( -- Now we have to figure out the columns that go together in your unpivoted table, and concatenate them
select n,STUFF ((
{snip}
Thanks for the great code. It's only because of my lack of knowledge and failure to provide a data sample that it doesn't work on the first try, I'm sure.
March 1, 2015 at 11:16 am
Lol, ignore the enough for 12 years. Instead of retyping the inline tally code, I stole it from another solution (one of Luis')
Missed that comment and left it in.
Please do the following. For the table generating the error, please go to SSMS, right click on the table and select "script table as" and create, and then post that script here.
17 should have been the right offset if you wanted the 21st column to be the first one shown.
The error message you cited indicates that it ran out of columns in an unexpected place.
March 1, 2015 at 2:23 pm
Thanks again Naveen and here the scripted out Rheumatology table:
USE [SpecialtyUplift]
GO
/****** Object: Table [dbo].[Rheumatology] Script Date: 3/1/2015 4:16:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rheumatology](
[Uplift Specialty ] [nvarchar](255) NULL,
[Member PO] [nvarchar](255) NULL,
[Member Sub PO] [nvarchar](255) NULL,
[Practice Unit Name] [nvarchar](255) NULL,
[Practice Unit ID] [nvarchar](255) NULL,
[Practice Unit Address] [nvarchar](255) NULL,
[Principal Partner PO] [nvarchar](255) NULL,
[Total Specialists] [nvarchar](255) NULL,
[Final Nomination Status] [nvarchar](255) NULL,
[Member PO Nominated ] [nvarchar](255) NULL,
[Member PO Agreement ] [nvarchar](255) NULL,
[Principal Partner PO Nomination Required] [nvarchar](255) NULL,
[Principal Partner PO Nominated] [nvarchar](255) NULL,
[Principal Partner PO Agreement] [nvarchar](255) NULL,
[Final Uplift Status ] [nvarchar](255) NULL,
[Final Uplift Percentage] [nvarchar](255) NULL,
[Final Rank] [int] NULL,
[Number of Ranked Practice Units] [nvarchar](255) NULL,
[Pediatric Members Percentage] [nvarchar](255) NULL,
[Pediatric Flag] [nvarchar](255) NULL,
[GDR ] [nvarchar](255) NULL,
[GDR Percentile Rank] [nvarchar](255) NULL,
[GDR PGS] [nvarchar](255) NULL,
[GDR Rank Number ] [nvarchar](255) NULL,
[PMPM] [nvarchar](255) NULL,
[PMPM Percentile Rank] [nvarchar](255) NULL,
[PMPM PGS] [nvarchar](255) NULL,
[PMPM Rank Number ] [nvarchar](255) NULL,
[RA PMPM] [nvarchar](255) NULL,
[RA PMPM Percentile Rank] [nvarchar](255) NULL,
[RA PMPM PGS] [nvarchar](255) NULL,
[RA PMPM Rank Number] [nvarchar](255) NULL,
[RA HEDIS] [nvarchar](255) NULL,
[RA HEDIS Percentile Rank] [nvarchar](255) NULL,
[RA HEDIS PGS] [nvarchar](255) NULL,
[RA HEDIS Rank Number] [nvarchar](255) NULL,
[Composite Score Eligible ] [nvarchar](255) NULL,
[Composite Score] [nvarchar](255) NULL,
[Composite Score PGS Percentage] [nvarchar](255) NULL,
[coremeasure] [bit] NULL,
[pediatric] [bit] NULL
) ON [PRIMARY]
GO
It probably doesn't matter but I added the last two columns so that i can distinguish whether a give table(specialty) is a 'core measure', a 'pediatric' or neither because all the core measures have the same columns, as do all the pediatric, but the ones that are neither each have varying structures.
Thanks.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply