Help getting percentages

[nc297]

How could I write a stored procedure to get the percentages from dynamic columns and insert all of that into a new table? This table would grow once a month so Dec would be added next and so on. I would want to have all of the percentages for each month listed. So that column could be called Oct 10 Perc, Nov 10 Perc and so on. Is this even possible?


Oct 10 Perc Oct10icnt Oct10ncnt Oct10tnct Nov 10 Perc Nov10icnt Nov10ncnt Nov 10 tnct
50 40 90 150 60 210




CREATE TABLE [dbo].[iClaims](
[Oct 10 iCnt] [int] NULL,
[Oct 20 nCnt] [int] NULL,
[Oct 10 tCnt] AS [int]
[Nov 10 iCnt] [int] NULL,
[Nov 20 nCnt] [int] NULL,
[Nov 10 tCnt] AS [int]

) ON [PRIMARY]

insert into iclaims
select '50', '40', '90', '150', '60', '210'

 

[MarkSweetland]

Best thing to do is normalize your data structure. Creating new columns every month to accommodate new data is far from efficient. Here is a link to a basic description of what to look for. It will help you in the long run.

http://databases.about.com/od/specificproducts/a/firstnormalform.htm

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[nc297]

Thanks but I don't know how that would help me in this situation as the columns are dynamic. Would a view work better to get the percentages and place that information into a view?

 

[nc297]

How could I create a view to get the percentage of these two numbers?

create view
SELECT icnt, ncnt,
cast((icnt + ncnt) as tcnt decimal(10, 2))/tcnt*100

 

[SimonSellick]

What you are asking is possible in a stored procedure using dynamic SQL (EXECUTE function), but as Mark says, is not recommended. Instead of adding columns - that is, restructuring your database - every month, it is considered better practice to build your database to hold all the data you anticipate putting into it. In your case, that would seem to incude a table with a structure along these lines:

CREATE TABLE iClaims (
  period int
, iCnt   int
, nCnt   int
)

This lets you add the new record each month (period). You can then get the data out as a dynamic set of columns using a PIVOT query, with the further benefit that you can restrict the results if you wish, to (say) 12 months rolling, or year-to-date. Your source table (iClaims above) can be a view if you need dynamic results. I'm not sure about your percentage calc - what is it meant to be a percentage of? If iCnt and nCnt as a percentage of iCnt + nCnt, then

SELECT
  iCnt
, iCnt * 100.0 / iCnt + nCnt iPercent
, nCnt
, nCnt * 100.0 / iCnt + nCnt nPercent
...

HTH
Simon

 

[nc297]

Actually I wanted to add icnt + ncnt as tcnt

Then take icnt/tcnt * 100 to get the percent.

Someone helped me with this so I'm okay thanks!

SELECT

SORT, REG , iCnt, nCnt,

ISNULL

(iCnt,0) + ISNULL(nCnt,0) AS tCnt,

cast((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal (18, 1)) as pCnt

FROM
iClaimsYear