June 6, 2012 at 9:17 am
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 specialty ref_date_Week_Comencing_Date ROTT revised_percent
1 ACS 2012-04-02 20 20.00
27 AE 2012-01-30 20 20.00
28 AE 2011-08-29 20 20.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
June 6, 2012 at 9:21 am
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.
June 6, 2012 at 10:34 am
Sql integer division: int / int =int, not the decimal you are expecting.
Change @rott /100.0 and it should work as expected.
Lowell
June 6, 2012 at 12:29 pm
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
FROM PS_TestForOnline
group by specialty, ref_date_Week_Comencing_Date
order by specialty, ref_date_Week_Comencing_Date
drop table PS_TestForOnline
June 6, 2012 at 12:37 pm
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
June 6, 2012 at 12:39 pm
Try this:
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;
WITH BaseData AS (
select
COUNT (*) as count_of_referrals,
specialty,
ref_date_Week_Comencing_Date,
@rott as ROTT
FROM
PS_TestForOnline
group by
specialty,
ref_date_Week_Comencing_Date
)
SELECT
count_of_referrals,
specialty,
ref_date_Week_Comencing_Date,
ROTT,
count_of_referrals * (1- (@rott/100.0) ) as THIS_SHOULD_SHOW_20_PERCENT_OF_COL_A_FOR_EACH_ROW
FROM
BaseData
order by specialty, ref_date_Week_Comencing_Date
GO
drop table PS_TestForOnline
June 7, 2012 at 2:14 am
Many Thanks, Works a treat.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply