How to get Percentage - 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

General SQL Server Forums

New to SQL Server Programming

How to get Percentage

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-22 : 23:36:34

Guys, with this script it gives me the result of 100% all.
where us there hould be 99.25, 99.50 something like that.
can you help me guys.

select
convert(datetime,(STR(DATEPART(mm, ReturnDate)) + '/01/' + STR(DATEPART(yyyy, ReturnDate)))) as USReturnMonth,
count(returndate) as Total_US_Returns,
count(AsiaReceiptDate) as Total_Asia_Receipts,

(count(AsiaReceiptDate) / count(AsiaReceiptDate)) * 100.00 as percentage

from #Data
group by
convert(datetime,(STR(DATEPART(mm, ReturnDate)) + '/01/' + STR(DATEPART(yyyy, ReturnDate))))
order by
convert(datetime,(STR(DATEPART(mm, ReturnDate)) + '/01/' + STR(DATEPART(yyyy, ReturnDate))))

Thank you,

JOV

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-23 : 00:41:24

Hey Villanuev,

First, youc an replace your "USReturnMonth" column with this, a bit simpler:
DATEADD(MM, DATEDIFF(MM, 0, RETURNDATE),0) as USReturnMonth

Next, in your percentage, you're taking the total AsiaReceiptDate by the Total AsiaReceiptDate which will always return a value of 1 because you're dividing it by itself, then you multiply by 100, which gives you the 100%. You'd need different values between the numerator and denominator to get a different value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 00:55:34

rather than doing so many conversions like below
convert(datetime,(STR(DATEPART(mm, ReturnDate)) + '/01/' + STR(DATEPART(yyyy, ReturnDate))))
to group by month start date you can just use below

DATEADD(mm,DATEDIFF(mm,0,ReturnDate),0)
and as Flamblaster pointed out your fraction computation does not make any sense now as it will always be 100

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 01:00:09

Hi Flambalster and Visakh16,

I think im wrong, it should be like this.
Im not dividing by itself. sorry.

count(AsiaReceiptDate) / count(returndate) ) * 100.00 as percentage

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 01:01:24

in that case also make it like this


count(AsiaReceiptDate)* 100.00  / count(returndate) ) as percentage

and see the change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 01:02:36

the reason is this

http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 01:03:56

And one more thing
See below post to understand how you can get month date,year date etc without applying unnecessary casts and convertions

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-23 : 01:12:05

It seems to me this is prone to divide by zero errors. I'm guessing that the returndate will not always be filled in, in which case you'd be dividing by nulls. What type of percentage are you trying to achieve? Not from an SQL standpoint, but just mathematics.

Are you wanting to see the percentage of items from the US returned to Asia?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 01:14:57

from sql perspective this should avoid didvide by zero errors


(count(AsiaReceiptDate)* 100.00  / nullif(count(returndate),0) ) as percentage

however you need to give some thoughts to questions that flambaster has raised.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 01:19:43

Guys here is the result of my query.

count(AsiaReceiptDate)/ count(returndate) * 100.0 as percentage

MONTH-------TOTALASIARETURN--TOTALASIA RECEIPTS
----------------------------------------------
2011-01-01--3413-------------3410------ 0.0
2011-02-01--4498-------------4495------ 0.0
2011-03-01--4792-------------4787------ 0.0
2011-04-01--5367-------------5354------ 0.0
2011-05-01--5352-------------5335------ 0.0
2011-06-01--5087-------------5066------ 0.0
2011-07-01--6638-------------6387------ 0.0

The result should be like this..

Total US Returns--Total Asia Receipts-- Receipt %
1/1/2011 3413 3409 99.88%
2/1/2011 4498 4495 99.93%
3/1/2011 4792 4787 99.90%
4/1/2011 5367 5354 99.76%
5/1/2011 5352 5335 99.68%
6/1/2011 5087 5066 99.59%
7/1/2011 6638 6387 96.22%

BR.

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 01:23:17

as specified try like

(TOTALASIA *100.0)/NULLIF(TOTALASIARETURN,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 01:28:47

Hi Visakh16. its working now. Thank you very mucn guys.

anothing thing guys. i need your inputs about this one.
I made this scripts but i want to copy this to Datasets.
Its ok if i will use temp table. advice please. thanks.

IF OBJECT_ID('tempdb.dbo.#Data') IS NOT NULL --Run to terminate the temporary table store in database.
DROP TABLE [dbo].[#Data]
select
us.ESN,
us.sku,
us.shipReturnType,
us.returndate,
us.shipdate,
us.reshipdate,
min(rma.ReceiptDate) as AsiaReceiptDate
into #Data
from USProductRecovery us with (nolock)
left outer join ESNRMAs rma with (nolock)
on us.esn = rma.esn
and rma.ReceiptDate > ReturnDate --Most recent Asia receipt date
where returndate between('01/01/11 00:00:00') and ('07/31/2011 23:59:59')
and shipReturnType = 3 --RMAWarr only
group by
us.ESN,
us.sku,
us.shipReturnType,
us.returndate,
us.shipdate,
us.reshipdate

select
DATEADD(MM, DATEDIFF(MM, 0, RETURNDATE),0) as USReturnMonth,
count(returndate) as Total_US_Returns,
count(AsiaReceiptDate) as Total_Asia_Receipts,
Round(count(AsiaReceiptDate) * 100. / count(returndate),2) as percentage
from #Data
group by
DATEADD(MM, DATEDIFF(MM, 0, RETURNDATE),0)
order by
DATEADD(MM, DATEDIFF(MM, 0, RETURNDATE),0)

BR.

JOV

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 01:42:45

I notice also when i open the report dataset in SSRS, the fields are not display that i use for my query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 01:47:27

copy this to datasets? which datasets you're referring here? Reporting service datsets?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 02:02:48

Hi visakh16,

Yes, from Report Dataset of SSRS.

from Data Tab I create new dataset.

when i try to open the dataset from layout tab. where i have to drag the fields. i dont see any fields from my dataset that originate from my scripts.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 03:46:05

Or do i need to make or copy this script in Stored procdure?

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 04:06:56

Hi Guys,

I Place this command on the first line of my query and its working.

Set FMTONLY OFF

select
us.ESN,
us.sku,
us.shipReturnType,

BR.

JoV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 05:28:26

quote:
Originally posted by Villanuev

Hi visakh16,

Yes, from Report Dataset of SSRS.

from Data Tab I create new dataset.

when i try to open the dataset from layout tab. where i have to drag the fields. i dont see any fields from my dataset that originate from my scripts.

probably you didnt refresh dataset to get metadata information populated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-09-23 : 05:51:04

Hi visakh16..

Its working already.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 05:55:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources