Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

rounding in a sproc

Posted on 2007-03-20
6
Medium Priority
?
515 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 2000 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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