Calculating monthly percents - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculating monthly percents

Author

Topic

Efflixi
Starting Member

3 Posts

Posted - 2008-11-25 : 17:32:35

Say I have a table with rows like so:

Id    name     month     value     
1     blah     1/1/2007  2
1     blah2    1/1/2007  1
1     blah     2/1/2007  4
1     blah2    2/1/2007  4

Say I want the percent of “blah” as it relates to the total value for each month, so for 1/1/2007 I’d be returning 66.66% because the total for 1/1/2007 is 3 and “blah” has 2 for that month.

So my returned data would look like this:

1     blah     1/1/2007     66.66%
1     blah     2/1/2007     50.00%

I know this is fairly simple, but I’ve been staring at too much code and can’t see straight (ever get that way?) I don’t need the return value in actual percent, just dividing to get the 0.66 or whatever is all I need.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 17:39:18

[code]DECLARE @Sample TABLE
(
Id int,
name varchar(5),
month datetime,
value int
)

insert @sample
select 1, 'blah', '1/1/2007', 2 union all
select 1, 'blah2', '1/1/2007', 1 union all
select 1, 'blah', '2/1/2007', 4 union all
select 1, 'blah2', '2/1/2007', 4

SELECT id,
'blah',
month,
sum(case when name = 'blah' then 100.0E * value ELSE 0.0E END)
/ nullif(sum(1.0E * value), 0) AS n
FROM @sample
group by id,
month[/code]

E 12°55'05.63"
N 56°04'39.26"

Efflixi
Starting Member

3 Posts

Posted - 2008-11-25 : 18:10:03

I actually gave the wrong table format, but using your query i think i got it working anyhow, all i really needed was the "sum" line code. There's actually another field in the table and it all goes off that.

to input my code here's the result i came up with:

DECLARE	@Sample TABLE
	(
		fid		int,
		fname	varchar(max),
		[month] datetime,
		destination varchar(max),
		total	decimal
	)

insert
	@sample
Select
	d.externalfacilityid, d.externalfacility, d.[month], d.destination, d.total
From
	dischargedata d inner join reliant.dbo.facilities f on
		d.externalfacilityid = f.externalfacility_id
where
	f.facility_id = 97


SELECT
	fid, fname, [month],
	sum(case when destination = 'Private home/apartment with home health service' then 100.0E * total ELSE 0.0E END) / nullif(sum(1.0E * total), 0) AS n
FROM
	@sample
group by
	fid, fname, [month]

My only remaining issue is that as you can tell from my original select, sometimes the destination i am going off of has been misspelled and i don't have control of that data so i have to account for it in my queries. How do i add an "or" statement into the case statment?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 18:14:09

[code]SELECT
fid, fname, [month],
sum(case when destination in (select descriptopn from anothertable) then 100.0E * total ELSE 0.0E END) / nullif(sum(1.0E * total), 0) AS n
FROM
@sample
group by
fid, fname, [month]

SELECT
fid, fname, [month],
sum(case when destination in ('x', 'y', 'z') then 100.0E * total ELSE 0.0E END) / nullif(sum(1.0E * total), 0) AS n
FROM
@sample
group by
fid, fname, [month][/code]

E 12°55'05.63"
N 56°04'39.26"

Efflixi
Starting Member

3 Posts

Posted - 2008-11-25 : 18:18:35

Thanks so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources