Calculated Columns - decimal

Question

Calculated Columns - decimal

LoosinMaMind

Hall of Fame

Points: 3366
More actions
June 6, 2012 at 9:17 am

#275208

Hi, I have the following script and need to get the final row to show as a % reduction (ROTT) of the first column.
count_of_referrals specialtyref_date_Week_Comencing_DateROTTrevised_percent
1ACS2012-04-022020.00
27AE2012-01-302020.00
28AE2011-08-292020.00
Example Row 3 = 28 Count of reverals less a reduction of 20 % (ROTT), I would expect to see 22.4 in last.
declare @rott int;
set @rott = 20;
select COUNT (*) as count_of_referrals,
specialty,
CONVERT(VARCHAR(10), DATEADD(wk, DATEDIFF(wk, 0, referreddate_dte), 0), 23) AS ref_date_Week_Comencing_Date,
@rott as ROTT,
(@rott * CAST(1- (@rott/100) AS decimal(10, 2))) as revised_percent[/color]FROM inf.vw_IXP_PSNEW_OPWL
WHERE referreddate_dte >= GETDATE () -365-- and specialty in (@spec)
group by specialty, CONVERT(VARCHAR(10), DATEADD(wk, DATEDIFF(wk, 0, referreddate_dte), 0), 23)
order by specialty

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

We are going to need more information to really help. Please post the DDL (CREATE TABLE statement) for the table(s) used in the query, sample data (as a series of INSERT INTO statements), the expected results based on the sample data.

Lowell SSC Guru Points: 323518 More actions · Answer 2

Sql integer division: int / int =int, not the decimal you are expecting.

Change @rott /100.0 and it should work as expected.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 3

Hi

Thanks for the reply. It works but assigns the same value to every row. I think now i have my query structure wrong.

Below is a sample data table script and my query to run against it.

What I need to see it what ever is assigned to @ROTT (in this case 20 ie:20%) i need to calculated the following to show in the last column. ie: Count_of_refferals - 20%

Row 1 shows Count_of_refferals = 7, I would expect to see 5.6 in the last row. (20% reduction)

Row 2 shows Count_of_refferals = 5, I would expect to see 4.0 in the last row. (20% reduction)

Sample data and current script below.

Thanks in advance for your help.

--------------------------

CREATE TABLE PS_TestForOnline

(

specialty VARCHAR (50),

ref_date_Week_Comencing_Date VARCHAR (50)

);

INSERT INTO PS_TestForOnline

VALUES('AE','2011-06-06');

INSERT INTO PS_TestForOnline

VALUES('AE','2011-06-06');

INSERT INTO PS_TestForOnline

VALUES('AE','2011-06-06');

INSERT INTO PS_TestForOnline

VALUES('AE','2011-06-06');

INSERT INTO PS_TestForOnline

VALUES('AE','2011-06-06');

INSERT INTO PS_TestForOnline

VALUES('AE','2011-06-06');

INSERT INTO PS_TestForOnline

VALUES('AE','2011-06-06');

INSERT INTO PS_TestForOnline

VALUES('ENT','2011-06-13');

INSERT INTO PS_TestForOnline

VALUES('ENT','2011-06-13');

INSERT INTO PS_TestForOnline

VALUES('ENT','2011-06-13');

INSERT INTO PS_TestForOnline

VALUES('ENT','2011-06-13');

INSERT INTO PS_TestForOnline

VALUES('ENT','2011-06-13');

declare @rott int;

set @rott = 20;

select COUNT (*) as count_of_referrals,

specialty,

ref_date_Week_Comencing_Date,

@rott as ROTT,

(@rott * (1- (@rott/100.0) )) as THIS_SHOULD_SHOW_20_PERCENT_OF_COL_A_FOR_EACH_ROW

FROMPS_TestForOnline

group by specialty, ref_date_Week_Comencing_Date

order by specialty, ref_date_Week_Comencing_Date

drop table PS_TestForOnline

Lowell SSC Guru Points: 323518 More actions · Answer 4

you need to calcualte the counts, and apply the ROTT calculation to that;

by simply wrapping the grouping as a subquery, this gives you what you are after:

select @rott as ROTT,

(count_of_referrals * (1- (@rott/100.0) )) as THIS_SHOULD_SHOW_20_PERCENT_OF_COL_A_FOR_EACH_ROW,

*

FROM (

select COUNT (*) as count_of_referrals,

specialty,

ref_date_Week_Comencing_Date

FROM PS_TestForOnline

group by specialty, ref_date_Week_Comencing_Date

) myAlias

order by specialty, ref_date_Week_Comencing_Date

drop table PS_TestForOnline

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Try this:

CREATE TABLE PS_TestForOnline

(

specialty VARCHAR (50),

ref_date_Week_Comencing_Date VARCHAR (50)

);