Solved

SQL % in SP

Posted on 2007-04-10
35
266 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
  • 18
  • 13
  • 4
35 Comments
 
LVL 11

Expert Comment

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

by:adraughn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
it's still 1
0
 
LVL 13

Author Comment

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

Author Comment

by:adraughn
Comment Utility
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
Comment Utility
If you have the fields typed as decimals now, try:

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

Expert Comment

by:dready
Comment Utility
yeah, why not, post the whole proc.
0
 
LVL 13

Author Comment

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

-a
0
 
LVL 13

Author Comment

by:adraughn
Comment Utility
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 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 13

Author Comment

by:adraughn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
dready,

first result set: 1

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

Expert Comment

by:dready
Comment Utility
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
Comment Utility
i am a big fan of guessing. :)

But unfortunately, the results set still shows 1

-a
0
 
LVL 11

Expert Comment

by:dready
Comment Utility
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
Comment Utility
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
Comment Utility
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 450 total points
Comment Utility
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
Comment Utility
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
Comment Utility
now how can i change the format from

0.7101 to forever

to

71.01 %

?
0
 
LVL 11

Expert Comment

by:dready
Comment Utility
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
Comment Utility
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
Comment Utility
works perfectly. thanks dready, i owe you one...

-a
0
 
LVL 11

Expert Comment

by:dready
Comment Utility
you're welcome (-:


0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now