SQL % in SP

Cans omeone please tell me why the results of this do not display SDS_PCT as a decimal? While I', asking, how do I convert it to a percentage? 0.725 = 72.5%? Right now the results show SDS_PCT as zero.

adria

CREATE TABLE #SDS5 (DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4), SDS_QTY int,EXCH_QTY int,SDS_PCT decimal)
go
INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
[SDS_QTY]/[EXCH_QTY] AS SDS_PCT
FROM #SDS2 s2
go
select * from #SDS5
LVL 13
adraughnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dreadyCommented:
In sql, if you divide two ints, the result is an int, so the part after the , is cut off.
You will get results by casting or by multiplication with 1.0

The easiest is:
INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
[SDS_QTY]*1.0/[EXCH_QTY] AS SDS_PCT
FROM #SDS2 s2

0
dreadyCommented:
OH, and if you want to convert it to a percentage (i asume a precistion of 2 after the comma)

INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
Cast([SDS_QTY]*1.0/[EXCH_QTY] as decimal(5,2)) AS SDS_PCT
FROM #SDS2 s2
0
puppydogbuddyCommented:
change this:
           .......SDS_PCT decimal)
To:
                   Format(CDec(SDS_PCT decimal), "Percent")
__________________________________________
change:
       [SDS_QTY]/[EXCH_QTY] AS SDS_PCT
to:
       [SDS_QTY]/[EXCH_QTY] AS Format(CDec(SDS_PCT), "Percent")


0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

adraughnAuthor Commented:
dready, i used:

INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
Cast([SDS_QTY]*1.0/[EXCH_QTY] as decimal(5,2)) AS SDS_PCT
FROM #SDS2 s2

and now the SDS_PCT column has all of them as 1
0
adraughnAuthor Commented:
pup,

i tried this:

INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
 [SDS_QTY]/[EXCH_QTY] AS Format(CDec(SDS_PCT), "Percent")
FROM #SDS2 s2

and it returned an error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CDec'.
0
dreadyCommented:
Please first try what happens if we do not cast it to decimal, so:

INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
[SDS_QTY]*1.0/[EXCH_QTY] AS SDS_PCT
FROM #SDS2 s2

at least you can tell if it is the cast that causes the problem, or sth else.
Is it still 1?
0
adraughnAuthor Commented:
it's still 1
0
adraughnAuthor Commented:
do you want me to post the entire sp?
0
adraughnAuthor Commented:
maybe i need to format SDS_QTY and EXCH_QTY as decimal also? I changed the temp tables to create the tables with the data type as decimal instead of int for both of those fields, but it didn't help.

0
puppydogbuddyCommented:
If you have the fields typed as decimals now, try:

        Format(SDS_PCT decimal, "Percent")
0
dreadyCommented:
yeah, why not, post the whole proc.
0
adraughnAuthor Commented:
that gave me incorrect syntax. hang on, i'll post the whole thing.

-a
0
adraughnAuthor Commented:
you asked for it:

use spindle
go
-- =============================================
-- Author:            <Adria Draughn>
-- Create date: <Apr 11,2007>
-- Description:      <Spindle database.Calculate Same Day Shipping %>
-- =============================================
CREATE PROCEDURE upOrders_SDS as
BEGIN
-- Create temp table that includes date range as mmm-yy
CREATE TABLE #MONTHREF (OrderDate datetime, [MonthName] varchar(10),
DateRng varchar(20),OrderNum float, ODYear varchar(4))
go
-- Insert records from orders view
INSERT INTO #MONTHREF
SELECT ord.OrderDate, tmr.MonthName,
[MonthName] + '-' + Right((Year([orderdate])),2) AS DateRng,
ord.OrderNum, Year([orderdate]) AS ODYear
FROM vOrders ord LEFT JOIN tblLU_MonthRef tmr ON
ord.ODMonth = tmr.ID
go
-- Create temp table that includes SDS field
CREATE TABLE #SDST (Orderid int, DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4),OrderDate datetime, ShipDt datetime,
ServiceType varchar(30),loc varchar(20),SDS varchar(20))
go
-- Insert records, filter out repairs
INSERT INTO #SDST
SELECT ord.OrderID, tt.DateRng, tt.MonthName, tt.ODYear,
ord.OrderDate, tsd.ShipDt, ord.ServiceType, loc.Loc,
CASE WHEN ord.[orderdate] = tsd.[shipdt] THEN
'SD' ELSE 'NSD' END AS [SDS]
FROM ((tblOrders ord INNER JOIN tblOrders_ShipDt tsd ON
ord.OrderID = tsd.OrderID) INNER JOIN
#MonthRef tt ON ord.OrderNum = tt.OrderNum) LEFT JOIN
tblOrders_Loc loc ON ord.OrderID = loc.OrderID
WHERE (((tt.ODYear)>2005) AND ((ord.ServiceType)='exchange'))
ORDER BY SDS
go
-- Create temp table that includes SDS as field
CREATE TABLE #SDS1 (Orderid int, DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4),OrderDate datetime, ShipDt datetime,
ServiceType varchar(30),loc varchar(20),SDS varchar(20))
go
-- Insert records, filtering SDS to SD only
INSERT INTO #SDS1
SELECT * FROM #SDST WHERE SDS = 'SD'
go
-- Drop temp table
DROP TABLE #SDST
go
-- Create temp table that includes Filter
CREATE TABLE #SDST (Orderid int, DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4),ServiceType varchar(30), SDS varchar(20),
Loc varchar(20),UnitType varchar(30),Filter varchar(30))
go
-- Insert records including SDS and Filter calc
INSERT INTO #SDST
SELECT ord.OrderID, tt.DateRng, tt.MonthName, tt.ODYear,
ord.ServiceType,loc.Loc, ord.UnitType,
CASE WHEN ord.[orderdate] = tsd.[shipdt] THEN
'SD' ELSE 'NSD' END AS [SDS],
CASE WHEN ord.[orderdate] = tsd.[shipdt] THEN
'SD' ELSE 'NSD' END + CASE WHEN loc.[Loc] is null THEN 'NL' ELSE 'HL' END AS [Filter]
FROM (#SDS1 ts1 RIGHT JOIN ((tblOrders ord INNER JOIN tblOrders_ShipDt tsd ON
ord.OrderID = tsd.OrderID) INNER JOIN #MonthRef tt ON
ord.OrderNum = tt.OrderNum) ON ts1.OrderID = ord.OrderID) LEFT JOIN
tblOrders_Loc loc ON ord.OrderID = loc.OrderID
WHERE (((tt.ODYear)>2005) AND ((ord.ServiceType)='exchange') AND
((ord.UnitType)='spindle'))
ORDER BY ord.OrderID
go
-- Create temp table that includes filter
CREATE TABLE #SDS3 (Orderid int, DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4),ServiceType varchar(30), SDS varchar(20),
Loc varchar(20),UnitType varchar(30),Filter varchar(30))
go
-- Insert records filtering out Not Same Day Has Location
INSERT INTO #SDS3
select * from #SDST where [Filter] <> 'NSDHL'
go
-- Drop temp table
DROP TABLE #SDST
go
-- Create temp table that includes EXCH_QTY
CREATE TABLE #SDS4 (DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4),ServiceType varchar(30), EXCH_QTY decimal)
go
-- Insert records, calculating EXCH_QTY
INSERT INTO #SDS4
SELECT s3.DateRng, s3.MonthName, s3.ODYear, s3.ServiceType,
Count(s3.OrderID) AS EXCH_QTY
FROM #SDS3 s3
GROUP BY s3.DateRng, s3.MonthName, s3.ODYear, s3.ServiceType
go
-- Create temp table including SDS_QTY
CREATE TABLE #SDS2 (DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4), SDS_QTY decimal,EXCH_QTY decimal,
Report_M_Date datetime)
go
-- Insert records, calculating SDS_QTY
INSERT INTO #SDS2
SELECT s1.DateRng, s1.MonthName, s1.ODYear, Count(s1.ServiceType) AS SDS_QTY,
s4.EXCH_QTY, rmd.Report_M_Date
FROM (#SDS1 s1 LEFT JOIN tblLU_RptMDate rmd ON
s1.DateRng = rmd.ReportMonth) LEFT JOIN
#SDS4 s4 ON s1.DateRng = s4.DateRng
GROUP BY s1.DateRng, s1.MonthName,
s1.ODYear, s4.EXCH_QTY, rmd.Report_M_Date
ORDER BY rmd.Report_M_Date
go
-- Create temp table including SDS_PCT
CREATE TABLE #SDS5 (DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4), SDS_QTY decimal,EXCH_QTY decimal,SDS_PCT decimal)
go
-- Insert records and calculate SDS_PCT as percentage
INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
[SDS_QTY]*1.0/[EXCH_QTY] AS SDS_PCT
FROM #SDS2 s2
go
-- Test SDS5
select * from #SDS5
go
-- Drop tables to run again
DROP TABLE #MONTHREF
DROP TABLE #SDS1
DROP TABLE #sds3
DROP TABLE #SDST
DROP TABLE #SDS4
DROP TABLE #SDS2
DROP TABLE #SDS5
0
puppydogbuddyCommented:
adraughn,
this link shows you how to do it.
            http://forums.devarticles.com/microsoft-sql-server-5/percent-format-2269.html
0
dreadyCommented:
Maybe the values of s2.EXCH_QTY and s2.SDS_QTY are really always the same and that is the reason you always get a one.
To check that, add the following under --testSDS5
select * from #SDS2 where SDS_QTY<> EXCH_QTY

if this doesnt return anything, that is the case. If so, also check what is in there by adding
select * from #SDS2
Maybe the columns are not holding the values that you expect to be there.
0
adraughnAuthor Commented:
the SDS_QTY and EXCH_QTY are in the #SDS5 table. when i do they SELECT * from #SDS5, it shows the correct values for all except the percentage.

results:
DateRng   MonthName ODYear SDS_QTY EXCH_QTY SDS_PCT
Jan-06      Jan      2006      34      40      1
Feb-06      Feb      2006      27      32      1
Mar-06      Mar      2006      43      52      1
Apr-06      Apr      2006      26      34      1
May-06      May      2006      36      48      1
Jun-06      Jun      2006      37      53      1
Jul-06      Jul      2006      29      40      1
Aug-06      Aug      2006      40      51      1
Sep-06      Sep      2006      47      56      1
Oct-06      Oct      2006      46      65      1
Nov-06      Nov      2006      29      34      1
Dec-06      Dec      2006      37      48      1
Jan-07      Jan      2007      44      61      1
Feb-07      Feb      2007      32      51      1
Mar-07      Mar      2007      49      69      1

0
dreadyCommented:
Another try:

INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
cast([SDS_QTY] as float)/cast([EXCH_QTY] as float) AS SDS_PCT
FROM #SDS2 s2
0
adraughnAuthor Commented:
still shows 1 as percent. any other ideas? i hope you don't give up on me because I am stumped!

adria
0
puppydogbuddyCommented:
adraughn,
didn't you see the link I posted????
this link shows you how to do it.
            http://forums.devarticles.com/microsoft-sql-server-5/percent-format-2269.html
0
dreadyCommented:
i am very surprised as well.
Please execute this in your query analyser and tell me the result

declare @1 as int
declare @2 as int

set @1 = 4
set @2 = 3
select @1/@2
select @1*1.0/@2
0
adraughnAuthor Commented:
puppy, i tried this:

INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
[SDS_QTY]/[EXCH_QTY] AS CAST(CAST(SDS_PCT*100 AS numeric(10,2)) AS varchar(5)) + '%'
FROM #SDS2 s2

and got this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CAST'.
0
adraughnAuthor Commented:
puppy,

i also tried this:

select CAST(CAST(SDS_PCT*100 AS numeric(10,2)) AS varchar(5)) + '%' from #SDS5

returned no results
0
adraughnAuthor Commented:
dready,

first result set: 1

second result set: 1.3333....
0
dreadyCommented:
That shows that multipliying with 1.0 does the job.

try this (starting to guess now)

INSERT INTO #SDS5
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
s2.SDS_QTY*1.0/s2.EXCH_QTY AS SDS_PCT
FROM #SDS2 s2
0
adraughnAuthor Commented:
i am a big fan of guessing. :)

But unfortunately, the results set still shows 1

-a
0
dreadyCommented:
On the postion all the way in the end, where you do
-- Test SDS5
select * from #SDS5

can you add the following and post the results?

SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
s2.SDS_QTY*1.0/s2.EXCH_QTY AS SDS_PCT
FROM #SDS2 s2


0
adraughnAuthor Commented:
that's pretty: :)

Jan-06      Jan      2006      34      40      0.850000000000000000
Feb-06      Feb      2006      27      32      0.843750000000000000
Mar-06      Mar      2006      43      52      0.826923076923076923
Apr-06      Apr      2006      26      34      0.764705882352941176
May-06      May      2006      36      48      0.750000000000000000
Jun-06      Jun      2006      37      53      0.698113207547169811
Jul-06      Jul      2006      29      40      0.725000000000000000
Aug-06      Aug      2006      40      51      0.784313725490196078
Sep-06      Sep      2006      47      56      0.839285714285714285
Oct-06      Oct      2006      46      65      0.707692307692307692
Nov-06      Nov      2006      29      34      0.852941176470588235
Dec-06      Dec      2006      37      48      0.770833333333333333
Jan-07      Jan      2007      44      61      0.721311475409836065
Feb-07      Feb      2007      32      51      0.627450980392156862
Mar-07      Mar      2007      49      69      0.710144927536231884
0
adraughnAuthor Commented:
how would the insert mess it up? I have it set to decimal...????

CREATE TABLE #SDS5 (DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4), SDS_QTY decimal,EXCH_QTY decimal,SDS_PCT decimal)
0
dreadyCommented:
so, we dont have to concentrate on the select statement anymore. The problem is somewhere else.
Wont be the real solution, but try what happens when you create table 5 like this:

-- Create temp table including SDS_PCT
CREATE TABLE #SDS5 (DateRng varchar(20),[MonthName] varchar(10),
ODYear varchar(4), SDS_QTY decimal,EXCH_QTY decimal,SDS_PCT float)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adraughnAuthor Commented:
holy sh*t, it worked!

Jan-06      Jan      2006      34      40      0.85
Feb-06      Feb      2006      27      32      0.84375
Mar-06      Mar      2006      43      52      0.826923076923077
Apr-06      Apr      2006      26      34      0.764705882352941
May-06      May      2006      36      48      0.75
Jun-06      Jun      2006      37      53      0.69811320754717
Jul-06      Jul      2006      29      40      0.725
Aug-06      Aug      2006      40      51      0.784313725490196
Sep-06      Sep      2006      47      56      0.839285714285714
Oct-06      Oct      2006      46      65      0.707692307692308
Nov-06      Nov      2006      29      34      0.852941176470588
Dec-06      Dec      2006      37      48      0.770833333333333
Jan-07      Jan      2007      44      61      0.721311475409836
Feb-07      Feb      2007      32      51      0.627450980392157
Mar-07      Mar      2007      49      69      0.710144927536232
0
adraughnAuthor Commented:
now how can i change the format from

0.7101 to forever

to

71.01 %

?
0
dreadyCommented:
Great!!!

now you have to cast it to numeric or decimal
As described in the link from puppy:

http://forums.devarticles.com/microsoft-sql-server-5/percent-format-2269.html

Sorry, but have to leave you now, have work to do.

Should be easier now!
0
dreadyCommented:
oh, one last comment:

if you want the outcome to have the % sign behind it, try making the field in table 5 of type varchar(10)
And in the insert, put
SELECT s2.DateRng, s2.MonthName, s2.ODYear, s2.SDS_QTY, s2.EXCH_QTY,
cast(cast (s2.SDS_QTY*100.0/s2.EXCH_QTY AS numeric(10,2)) as varchar(5)) + '%' as SDS_PCT
FROM #SDS2 s2

(Hope i put al the ()'s on the right spot)
0
adraughnAuthor Commented:
works perfectly. thanks dready, i owe you one...

-a
0
dreadyCommented:
you're welcome (-:


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.