Solved

rounding in a sproc

Posted on 2007-03-20
6
514 Views
Last Modified: 2012-08-14
I need to and I am in a pinch! to get the list price from my SQL tables.  My issue is that the list price isn't a stored number, but created as a temp table withing Dynamics Great Plains.

I have a tables (iv00107 and iv00108) and stored in that tables are the following columns:
for iv00107: itemnmbr, prclevel, rndgamnt, roundhow, roundto
for iv00108: uofmprice(markup percentage)
In another table (iv00101) i am picking two columns: currcost and itemnmbr

i am doing the join on the itemnmbr from each table.  
I need to round the currcost after doing a percentage of markup.
I wrote:
cast(round(currentcost/('100%'-uomprice),rndamt) as char(13)

my questions are:
1) I have 3 price levels how do I ensure output of each price level
2) I don't believe the "cast(round(currentcost/('100%'-uomprice),rndamt) as char(13)" is accurate.
3) I am losing my mind, so the entire sproc is listed below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



ALTER  PROCEDURE spWHITInvent
@File VARCHAR(255)
AS

DECLARE @CommandString varchar(512);
/*DECLARE @HeaderRecord  SYSNAME;*/
DECLARE @RecordData    varchar(512);
DECLARE @FileName      SYSNAME;

SET @FileName = CAST(@File as SYSNAME);

/*SET @HeaderRecord = 'CUSTNMBR    CUSTNAME                   RATEPID    CRLMTAMT     CUSTBLNC   AGPERAMT_1    TTLSLYTD   TTLSLLYR    LSTTRXDT    LASTPYDT';*/
SET @CommandString = ' > ' + @FileName;

exec master..xp_cmdshell @CommandString, NO_OUTPUT

DECLARE CustomerList CURSOR FOR

SELECT CAST(IV00102.ITEMNMBR AS CHAR(10))+ CAST(IV00101.ITEMDESC AS CHAR(40))+
CAST(IV00101.PRCHSUOM AS CHAR(5))+
CAST(SELNGUOM AS CHAR(5))+
CAST(PM00200.VENDORID AS CHAR(10))+
CAST(VENDNAME AS CHAR(26))+
CAST(VNDITNUM AS CHAR(10))+
CAST(IV00102.QTYONHND as CHAR(15))+
CAST(QTYCOMTD AS CHAR(15))+
CAST(IV00102.QTYONORD AS CHAR(15))+
CONVERT(CHAR(14),IV00101.MODIFDT,101)+
CAST(VNDITNUM AS CHAR(10))+
CAST(USCATVLS_2 AS CHAR(10))+
CAST((round(CURRCOST /(100% - uomprice), rndgamnt)  AS CHAR(13))+
convert(char(14),FUFILDAT,101)
FROM IV00103 INNER JOIN SOP30300 ON IV00103.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN PM00200 ON IV00103.VENDORID = PM00200.VENDORID
INNER JOIN IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV00102 ON SOP30300.ITEMNMBR = IV00102.ITEMNMBR
WHERE SOP30300.SOPTYPE = 3 AND IV00102.LOCNCODE = '1' AND FUFILDAT = dateadd(day, -1, convert(datetime, convert(varchar(10), getdate(), 120), 120))
 
OPEN CustomerList
FETCH NEXT FROM CustomerList INTO @RecordData

WHILE @@FETCH_STATUS = 0

BEGIN
    SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
    EXEC master..xp_cmdshell @CommandString, NO_OUTPUT

    FETCH NEXT FROM CustomerList INTO @RecordData
END

CLOSE CustomerList
DEALLOCATE CustomerList




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




0
Comment
Question by:Pioneermfg
[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
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18758922
Change:
round(CURRCOST /(100% - uomprice), rndgamnt)
To:
round(CURRCOST /(1 - (uomprice/100)), rndgamnt)     --if uomprice is  < 1 then forget the div by 100

Don't understand what you mean by "price levels" and "assure the output of each level"
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 18759488
in the tables iv00107 and iv00108 there are 3 price levels with price breaks based on quantity.  Great formula.  a couple of questions though.  in Great Pains (not a mispelling) rndgamnt is not a value per se, it is either round up to the next quarter, or half or dollar.
so an example is round(.59 /(1-(70/100), 25) = 2.00
.59/.3 = 1.97 round up to next quarter dollar it is now 2.00.  will the round that you wrote still work?

0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 18762150
will the round that you wrote still work?
Nope.
That's a very unconventional "rounding".  Assuming values of 25, 50 and anything else for rndgamnt, try this:


...
,CAST(
CASE rndgamnt
  WHEN 25 THEN  CEILING((CURRCOST /(1-(uomprice/100)))*4)/4
  WHEN 50 THEN  CEILING((CURRCOST /(1-(uomprice/100)))*2)/2
  ELSE CEILING(CURRCOST /(1-(uomprice/100)))
END
AS CHAR(13)
)+
convert(char(14),FUFILDAT,101)






0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 3

Author Comment

by:Pioneermfg
ID: 18764263
in the case statement, I am getting an error of invalid syntax, but I have a bigger question.  This company uses the following rounding amounts .25, .50, 1.00, 10.00, and 25.00.

 WHEN 25 THEN  CEILING((CURRCOST /(1-(uomprice/100)))*4)/4 <-- I'm assuming quarters
 WHEN 50 THEN  CEILING((CURRCOST /(1-(uomprice/100)))*2)/2 <-- I'm assuming half dollar
WHEN 1.00 THEN CEILING((CURRCOST /(1-(UOMPRICE/100
WHEN 10.00 THEN CEILING((CURRCOST /(1-(UOMPRICE/100)
WHEN 25.00 THEN CEILING((CURRCOST /(1-(UOMPRICE/100)
0
 
LVL 3

Author Comment

by:Pioneermfg
ID: 18767357
According to microsoft, your solution was closest to being correct.  I am just going to write a small VB app to do all of this garbage.  SQL makes it almost impossible to do this type of rounding.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18767867
Upon closer examination, if rndgamnt is a number of evenly divisible into dollars, (i.e. .25, .50, 1.00, 10.00, 25.00 ), then this expression should work:

CEILING(CURRCOST/(1-(uomprice/100))/rndgamnt))*rndgamnt


0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

615 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