Solved

rounding in a sproc

Posted on 2007-03-20
6
511 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

810 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