Solved

# SQL % in SP

Posted on 2007-04-10
305 Views
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.

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
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 18
• 13
• 4

LVL 11

Expert Comment

ID: 18885992
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

LVL 11

Expert Comment

ID: 18886032
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

LVL 38

Expert Comment

ID: 18886062
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

LVL 13

Author Comment

ID: 18890417

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

LVL 13

Author Comment

ID: 18890452
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

LVL 11

Expert Comment

ID: 18890458
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

LVL 13

Author Comment

ID: 18890581
it's still 1
0

LVL 13

Author Comment

ID: 18890582
do you want me to post the entire sp?
0

LVL 13

Author Comment

ID: 18890613
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

LVL 38

Expert Comment

ID: 18890686
If you have the fields typed as decimals now, try:

Format(SDS_PCT decimal, "Percent")
0

LVL 11

Expert Comment

ID: 18890690
yeah, why not, post the whole proc.
0

LVL 13

Author Comment

ID: 18890748
that gave me incorrect syntax. hang on, i'll post the whole thing.

-a
0

LVL 13

Author Comment

ID: 18890760

use spindle
go
-- =============================================
-- 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

LVL 38

Assisted Solution

puppydogbuddy earned 50 total points
ID: 18890956
this link shows you how to do it.
http://forums.devarticles.com/microsoft-sql-server-5/percent-format-2269.html
0

LVL 11

Expert Comment

ID: 18890968
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

LVL 13

Author Comment

ID: 18891102
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

LVL 11

Expert Comment

ID: 18891262
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

LVL 13

Author Comment

ID: 18891279
still shows 1 as percent. any other ideas? i hope you don't give up on me because I am stumped!

0

LVL 38

Expert Comment

ID: 18891314
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

LVL 11

Expert Comment

ID: 18891320
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

LVL 13

Author Comment

ID: 18891437
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

LVL 13

Author Comment

ID: 18891449
puppy,

i also tried this:

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

0

LVL 13

Author Comment

ID: 18891462

first result set: 1

second result set: 1.3333....
0

LVL 11

Expert Comment

ID: 18891545
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

LVL 13

Author Comment

ID: 18891581
i am a big fan of guessing. :)

But unfortunately, the results set still shows 1

-a
0

LVL 11

Expert Comment

ID: 18891604
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

LVL 13

Author Comment

ID: 18891625
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

LVL 13

Author Comment

ID: 18891647
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

LVL 11

Accepted Solution

ID: 18891662
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

LVL 13

Author Comment

ID: 18891687
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

LVL 13

Author Comment

ID: 18891693
now how can i change the format from

0.7101 to forever

to

71.01 %

?
0

LVL 11

Expert Comment

ID: 18891713
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

LVL 11

Expert Comment

ID: 18891803
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

LVL 13

Author Comment

ID: 18891844
works perfectly. thanks dready, i owe you one...

-a
0

LVL 11

Expert Comment

ID: 18891874
you're welcome (-:

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Accessâ€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦
###### Suggested Courses
Course of the Month1 day, 21 hours left to enroll