Link to home
Start Free TrialLog in
Avatar of Pioneermfg
PioneermfgFlag for United States of America

asked on

rounding in a sproc

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




Avatar of dqmq
dqmq
Flag of United States of America image

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"
Avatar of Pioneermfg

ASKER

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?

ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
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.
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