Solved

rounding in a sproc

Posted on 2007-03-20
6
507 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 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

13 Experts available now in Live!

Get 1:1 Help Now