Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL % in SP

Posted on 2007-04-10
35
Medium Priority
?
322 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:adraughn
[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
  • Learn & ask questions
  • 18
  • 13
  • 4
35 Comments
 
LVL 11

Expert Comment

by:dready
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

by:dready
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

by:puppydogbuddy
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Author Comment

by:adraughn
ID: 18890417
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
 
LVL 13

Author Comment

by:adraughn
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

by:dready
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

by:adraughn
ID: 18890581
it's still 1
0
 
LVL 13

Author Comment

by:adraughn
ID: 18890582
do you want me to post the entire sp?
0
 
LVL 13

Author Comment

by:adraughn
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

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

        Format(SDS_PCT decimal, "Percent")
0
 
LVL 11

Expert Comment

by:dready
ID: 18890690
yeah, why not, post the whole proc.
0
 
LVL 13

Author Comment

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

-a
0
 
LVL 13

Author Comment

by:adraughn
ID: 18890760
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
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 200 total points
ID: 18890956
adraughn,
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

by:dready
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

by:adraughn
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

by:dready
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

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

adria
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18891314
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
 
LVL 11

Expert Comment

by:dready
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

by:adraughn
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

by:adraughn
ID: 18891449
puppy,

i also tried this:

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

returned no results
0
 
LVL 13

Author Comment

by:adraughn
ID: 18891462
dready,

first result set: 1

second result set: 1.3333....
0
 
LVL 11

Expert Comment

by:dready
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

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

But unfortunately, the results set still shows 1

-a
0
 
LVL 11

Expert Comment

by:dready
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

by:adraughn
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

by:adraughn
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

by:
dready earned 1800 total points
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

by:adraughn
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

by:adraughn
ID: 18891693
now how can i change the format from

0.7101 to forever

to

71.01 %

?
0
 
LVL 11

Expert Comment

by:dready
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

by:dready
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

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

-a
0
 
LVL 11

Expert Comment

by:dready
ID: 18891874
you're welcome (-:


0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question