[SQL] Afronden in SQL query gaat mis

[Urk]

Ik heb de volgende SQL query (Microsoft SQL Server 2005):

SQL:

SET DATEFORMAT dmy
SELECT a.omzet_bedrag, a.omzet_jaarmaand, a.omzet_winkel_uid, a.omzet_ksp_uid,
(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) AS omzet_bedrag_nieuw, 
CAST(((omzet_bedrag-(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid))/(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid)*100) AS float) AS percentage
FROM omzetten a WHERE a.omzet_jaarmaand = '01/10/2007'
AND a.omzet_bedrag IS NOT NULL
ORDER BY percentage

Deze query geeft bijv. als resultset:

3500,00	2007-10-01 00:00:00	316	44	NULL	NULL
340,00	2007-10-01 00:00:00	37	36	380,00	-10,52
6000,00	2007-10-01 00:00:00	318	45	4900,00	22,44

Wat hier gebeurd is dat de het verschil wordt berekend in percentage tussen de 1ste en de 5e kolom, het resultaat (percentage) staat dan in de 6e kolom.
De waarde in de 1ste kolom is de nieuwe waarde, de waarde in kolom 5 is de oude waarde.
Dus (als voorbeeld voor het derde record): 6000-4900 = (1100 / 4900)*100 = 22,44897 afgerond zou dat dus 22,45 moeten zijn, maar SQL rond dit af naar 22,44 (zie hierboven).

Dat klopt dus niet, ik heb al de query geprobeerd aan te passen met CAST() en ROUND() maar dat lukt ook niet...

Iemand een idee...?

Als het kan graag even een reactie puur op het afrondprobleem... (query moet ik misschien later nog perfectioneren)

[TeeDee]

Wat lukt er dan niet aan ROUND()

Eventueel zou je ook kunnen kijken op de MSDN naar de diverse Math functions.

SQL:

select round(22.44897,2)

geeft als resultaat: 22.45000

[ Voor 84% gewijzigd door TeeDee op 20-03-2008 15:41 ]

[Voutloos]

Urk schreef op donderdag 20 maart 2008 @ 15:34:
Als het kan graag even een reactie puur op het afrondprobleem... (query moet ik misschien later nog perfectioneren)

Gelukkig, nu kan je mooi de query drastisch inkorten zodat het rekensommetje beter zichtbaar is (tip: maak een testcase waar je direct met getallen werkt).

[Urk]

TeeDee schreef op donderdag 20 maart 2008 @ 15:37:
Wat lukt er dan niet aan ROUND()

Eventueel zou je ook kunnen kijken op de MSDN naar de diverse Math functions.

Wanneer ik de volgende query met ROUND functie gebruik:

SQL:

SET DATEFORMAT dmy
SELECT a.omzet_bedrag, a.omzet_jaarmaand, a.omzet_winkel_uid, a.omzet_ksp_uid,
(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) AS omzet_bedrag_nieuw, 
ROUND(((omzet_bedrag-(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid))/(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid)*100),2) AS percentage
FROM omzetten a WHERE a.omzet_jaarmaand = '01/10/2007'
AND a.omzet_bedrag IS NOT NULL
ORDER BY percentage

Dan blijft het percentage op 22,44 en niet op 22,45. Dat klopt dan dus nog steeds niet....
Zonder afrondingsfuncties komt de waarde ook op 22,44, het lijkt dus of het resultaat altijd op 2 decimalen komt... Dat klopt dan toch niet?

De kolom omzet_bedrag is trouwens van het type money.

[P.O. Box]

nevermind...

doe je query eens door op de plekken van (select top 1 ...) gewoon keihard de bedragen in te vullen... en kijk dan eens wat het resultaat is?

[ Voor 169% gewijzigd door P.O. Box op 20-03-2008 15:48 ]

[Urk]

Hmmm...dit werkt wel goed, het is gelukt:

SQL:

SET DATEFORMAT dmy
SELECT a.omzet_bedrag, a.omzet_jaarmaand, a.omzet_winkel_uid, a.omzet_ksp_uid,
(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) AS omzet_bedrag_nieuw, 
ROUND(((omzet_bedrag-CAST((SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) as decimal(9,2)))/ CAST((SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) as decimal(9,2))*100),2) AS percentage
FROM omzetten a WHERE a.omzet_jaarmaand = '01/10/2007'
AND a.omzet_bedrag IS NOT NULL
ORDER BY percentage

Wel vreemd dat je echt hard moet specificeren dat de input getallen decimal(9,2) moeten zijn i.p.v. de money() die het als datatype heeft.

Ik krijg nu echter wel als resultaat bijv: 22,45000000000000, kan ik daar nog 22,45 van maken?

Bedankt voor jullie hulp trouwens!

[TeeDee]

ROUND kan je ook gebruiken om een TRUNCATE te doen.
Niet helemaal waar inderdaad.

offtopic:
veel zaken staan trouwens ook gewoon letterlijk beschreven op MSDN

[ Voor 53% gewijzigd door TeeDee op 20-03-2008 16:16 ]

[_js_]

De reden is dat money intern met 4 cijfers achter de komma werkt, wat daarna komt wordt afgekapt.

In je berekening doe je eerst de deling, en daarna keer 100, dus effectief heb je maar twee cijfers achter de komma.

Je kunt decimal gebruiken of eerst de vermenigvuldiging doen en dan de deling.

Voor het afrond verhaal, het ,00000000 is een display iets, niet het resultaat. In je rapportage programma kun je opmaak instellen. Als je de resultaten rechtstreeks in je queryeditor wilt laten zien, dan kun je eventueel een cast van money naar varchar met als optie 0 of 1 doen.

[Urk]

TeeDee schreef op donderdag 20 maart 2008 @ 15:59:
ROUND kan je ook gebruiken om een TRUNCATE te doen.

Hoe dan? Want ROUND(waarde,2) rond wel af maar laat wel trailing nullen van de decimal(9,2) waarden zien.

[Urk]

_js_ schreef op donderdag 20 maart 2008 @ 16:09:
De reden is dat money intern met 4 cijfers achter de komma werkt, wat daarna komt wordt afgekapt.

In je berekening doe je eerst de deling, en daarna keer 100, dus effectief heb je maar twee cijfers achter de komma.

Je kunt decimal gebruiken of eerst de vermenigvuldiging doen en dan de deling.

Voor het afrond verhaal, het ,00000000 is een display iets, niet het resultaat. In je rapportage programma kun je opmaak instellen. Als je de resultaten rechtstreeks in je queryeditor wilt laten zien, dan kun je eventueel een cast van money naar varchar met als optie 0 of 1 doen.

Perfecte uitleg! Bedankt!
Kwam ook net tegen in het SQL datatype overzicht dat money() maar 4 decimalen heeft.

Blijft de waarde uit de berekening dan ook in het datatype van de invoer eigenlijk? Wat je hierboven zegt wel, toch?

[ Voor 12% gewijzigd door Urk op 20-03-2008 16:15 ]

[Urk]

Ik loop in dezelfde query nu tegen een ander probleem aan:

SQL:

SET DATEFORMAT dmy
SELECT a.omzet_bedrag AS omzet_nieuw, a.omzet_jaarmaand, a.omzet_winkel_uid, a.omzet_ksp_uid,
(SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) AS omzet_oud, 
ROUND(((omzet_bedrag-CAST((SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) as decimal(9,2)))/ ABS(CAST((SELECT TOP 1 omzet_bedrag FROM omzetten b WHERE b.omzet_jaarmaand = '01/10/2006' AND b.omzet_winkel_uid = a.omzet_winkel_uid AND b.omzet_ksp_uid = a.omzet_ksp_uid) as decimal(9,2)))*100),2) AS percentage
FROM omzetten a WHERE a.omzet_jaarmaand = '01/10/2007'
AND a.omzet_bedrag IS NOT NULL
ORDER BY percentage

Wanneer de waarde waardoor gedeeld wordt 0 is krijg ik natuurlijk een "Divide by zero error encountered.", logisch....
Alleen hoe kan ik tijdens de query 0 waarden bijv. vervangen door iets anders (bijv. 100).

In dit geval is het nodig dat:- wanneer beide waarden 0 zijn het percentage ook 0 is.
- wanneer de oude waarde 0 is dient het percentage 100 te zijn.
- wanneer de nieuwe waarde 0 is dient het percentage -100 te zijn.

Kan dat...?

[TeeDee]

En wat heb je zelf al gedaan/getest/uitgezocht/gevonden?

[Urk]

TeeDee schreef op donderdag 20 maart 2008 @ 20:08:
En wat heb je zelf al gedaan/getest/uitgezocht/gevonden?

Ben nu even een aantal Math functies aan het testen, zoals Replace()

[Niemand_Anders]

Dat je het afronden in de database doet, kan ik enigsinds nog voorstellen, maar waarom moet de database de achterliggende nullen al weglaten? Ik zou gewoon de data zonder afronden terug geven aan de (web) applicatie en die de afronding c.q. presentatie laten doen.

Daarbij als jij de bedrag al afrond bij het terug geven uit de database, dan kun je er ook nooit meer (correct) mee rekenen in de applicatie (bijv. uitsplitsing BTW om maar iets te noemen).

Het devies: Nooit afronden op database niveau. Afronden in .net is erg simpel. Het Money datatype komt als decimal terug via bijvoorbeeld de SqlDataReader. Decimal.ToString("#,##0.00") doet bijvoorbeeld ook direct afronding naar twee cijfers achter de komma en je krijgt er gratis ook nog een duizendtal teken bij. ToString gebruik voor het formaat Amerikaanse notatie, maar door een CultureInfo mee te geven is dat eenvoudig te veranderen.