How do you count percentage?

Question

How do you count percentage?

sabercats

SSC Enthusiast

Points: 172
More actions
April 17, 2012 at 12:50 pm

#272869

Hi all,
This is just an example, I have a database from SSMS 2008 Express, i want to pull out info to get percentage of each animal run every week. I can get report of to view each animal, duration (Endtime-Starttime in seconds) and location (Location in color)
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT Animal
(DATEDIFF (SECOND, Starttime,Endtime) as Duration
,Place
FROM Testing
WHERE Endtime BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY Animal
Animal Duration Place
cat 420761 SanJose10White
cat 93332 SanJose10Black
cat 893543 SanJose10Yellow
dog 493567 Tampa10White
dog 331875 Tampa10Black
dog 418234 Tampa10Yellow
dog 1304357 Dallas10Black
rabbit 88341 Dallas10White
rabbit 82166 Dallas10Yellow
rabbit 76871 SanJose10White
rabbit 48969 SanJose10Black
rabbit 691479 SanJose10Yellow
rabbit 23667 Tampa10White
rabbit 61288 Tampa10Black
rabbit 85691 Tampa10White
zebra 336867 Dallas10Black
zebra 336933 Dallas10White
zebra 682156 Dallas10Brown
zebra 394576 SanJose10Black
zebra 206556 Tampa10Black
zebra 778354 Tampa10Brown
Now, how to i make a query to get report weekly but it give me percentage of each animal run like:
cat % = total of duration for cat / ( (number of place) * 7*24*3600)) ;
--> like above result it should be cat % = ( (420761 + 93332+ 893543) / ( 1 * 7*24*3600)) = 2.32%
*** Treat Sanjose10 is one place no matter it is in SanJose10White, or SanJose10Black, or SanJose10Yellow)
dog % = total of duration for dog / ( (number of place) * 7*24*3600)) ; like above result it should be dog % = ( (493567 + 331875 + 418234 + 1304357) / ( 2 * 7*24*3600))
*** Treat Tampa10 is one place no matter it is in Tampa10White, or Tampa10Black, or Tampa10Yellow and Dallas10Black is one place ; 2 = (Dallas10 + Tampa10)
rabbit % = total of duration for rabbit / ( (number of place) * 7*24*3600)) ;
--> like above result it should be rabbit% = ( (88341 + 82166 + 76871 + 48969 + 691479 + 23667 + 61288 + 85691 ) / ( 3 * 7*24*3600))
*** 3 = (Dallas10 + SanJose10 + Tampa10)
zebra % = total of duration for zebra/ ( (number of place) * 7*24*3600)) ;
unknown % = ( ((total of places * 7 * 24 * 3600) - (total of all duration))* 100 / (total of places * 7 * 24 * 3600))
*** total of places is 3 = (sanJose10 + tampa10 + dallas10 )
Thanks for replying.
Cheers,

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

sabercats (4/17/2012)
*** Treat Sanjose10 is one place no matter it is in SanJose10White, or SanJose10Black, or SanJose10Yellow)

How? Is there a master list where we can bring up SanJose10 from to be able to strip off the color names? Otherwise, because the formatting is inconsistent and we can't strip those out, this is incredibly harder then it sounds. That field should have been split into two columns, it's overloaded. One field should be SanJose10, the second field Yellow.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sabercats SSC Enthusiast Points: 172 More actions · Answer 2

I tried to rename all of them and if we have the list like this

Animal Duration Place

cat 420761 SanJose

cat 93332 SanJose

cat 893543 SanJose

dog 493567 Tampa

dog 331875 Tampa

dog 418234 Tampa

dog 1304357 Dallas

rabbit 88341 Dallas

rabbit 82166 Dallas

rabbit 76871 SanJose

rabbit 48969 SanJose

rabbit 691479 SanJose

rabbit 23667 Tampa

rabbit 61288 Tampa

rabbit 85691 Tampa

zebra 336867 Dallas

zebra 336933 Dallas

zebra 682156 Dallas

zebra 394576 SanJose

zebra 206556 Tampa

zebra 778354 Tampa

How do i have the query to have percentage? Thanks

cat % = total of duration for cat / ( (number of place) * 7*24*3600)) ;

--> like above result it should be cat % = ( (420761 + 93332+ 893543) / ( 1 * 7*24*3600)) = 2.32%

*** Treat Sanjose is one place

dog % = total of duration for dog / ( (number of place) * 7*24*3600)) ; like above result it should be dog % = ( (493567 + 331875 + 418234 + 1304357) / ( 2 * 7*24*3600))

*** Treat Tampa is one place and Tampa is one place ; 2 = (Dallas + Tampa)

rabbit % = total of duration for rabbit / ( (number of place) * 7*24*3600)) ;

--> like above result it should be rabbit% = ( (88341 + 82166 + 76871 + 48969 + 691479 + 23667 + 61288 + 85691 ) / ( 3 * 7*24*3600))

*** 3 = (Dallas + SanJose + Tampa)

zebra % = total of duration for zebra/ ( (number of place) * 7*24*3600)) ;

unknown % = ( ((total of places * 7 * 24 * 3600) - (total of all duration))* 100 / (total of places * 7 * 24 * 3600))

*** total of places is 3 = (sanJose10 + tampa10 + dallas10 )

John.Liu SSCommitted Points: 1606 More actions · Answer 3

Try this script. You need to look at your duration figure as it appears to be more than a week. I also assumed that you use '10' to seperate the place and color.

;WITH CTE AS (

SELECT Animal

(DATEDIFF (SECOND, Starttime,Endtime) as Duration

,Place = SUBSTRING(PLACE,1,CHARINDEX('10',PLACE)-1)

FROM Testing

WHERE Endtime BETWEEN @2SundaysAgo AND @lastSaturday

)

SELECT

Animal

,TotalDuration = SUM(Duration)

,NumOfPlace = COUNT(DISTINCT Place)

,Percentage = SUM(Duration)/(COUNT(DISTINCT Place) * 7 * 24 *3600 * 1.0)

FROM CTE

GROUP BY Animal

UNION

SELECT

'Unknown'

,SUM(Duration)

,COUNT(DISTINCT Place)

,(((COUNT(DISTINCT Place) * 7 * 24 * 3600) - SUM(Duration))* 100 / (COUNT(DISTINCT Place) * 7 * 24 * 3600))

FROM CTE

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

If you'd like a tested answer, put the sample data in a format like the one identified in the article at the first link in my signature line below. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sabercats SSC Enthusiast Points: 172 More actions · Answer 5

Thanks for helping me out. It works with COUNT(DISTINCT Place).