ROWS UNBOUNDED PRECEDING WITH FLOAT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

ROWS UNBOUNDED PRECEDING WITH FLOAT

Author

Topic

MohamedAliSalim
Starting Member

9 Posts

Posted - 2014-05-12 : 03:59:13

I have table with three columns float - float - int

i have query to calculate balance for-each row

The next code to create the table and some of data for example


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestFLoat]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TestFLoat](
	[AddQuantity] [float] NOT NULL,
	[ExQuantity] [float] NOT NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
END
GO



SET IDENTITY_INSERT [dbo].[TestFLoat] ON 

GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (1.53, 0, 3)
GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (0, 1.53, 4)
GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (5.13, 0, 5)
GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (9.25, 0, 6)
GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (0, 1.56, 7)
GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (0, 3.57, 8)
GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (0, 9.25, 9)
GO
INSERT [dbo].[TestFLoat] ([AddQuantity], [ExQuantity], [ID]) VALUES (15.08, 0, 10)
GO
SET IDENTITY_INSERT [dbo].[TestFLoat] OFF
GO

The next code is for my query i want


SELECT	 AddQuantity
	,ExQuantity 	  
	,SUM(AddQuantity-ExQuantity) OVER ( ORDER BY ID  ROWS UNBOUNDED PRECEDING)	 AS CurrentQuantity

FROM	dbo.TestFLoat

after execute this query look at the line number 7 it must be 0 not else


     AddQuantity	    ExQuantity	    CurrentQuantity
1-   1.53	            0	            1.53
2-   0	                    1.53	    0
3-   5.13	            0	            5.13
4-   9.25	            0	            14.38
5-   0	                    1.56	    12.82
6-   0	                    3.57	    9.25
7-   0	                    9.25	    -1.77635683940025E-15
8-   15.08	            0	            15.08

Mohamed Ali Salim

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-12 : 05:18:45

You are using FLOAT data type , that's what...
use a cast to decimal or round : cast(X as decimal(18,2))

sabinWeb MCP

MohamedAliSalim
Starting Member

9 Posts

Posted - 2014-05-12 : 05:56:25

I have a big problem with decimal
--------------------------------------
DECLARE @x DECIMAL (25,10)= 100
DECLARE @y DECIMAL (25,10)= @x / 3
DECLARE @z DECIMAL (25,10)= @y * 3
SELECT @x, @y , @z
-----------------------------
@z must be 100 not 99.9999999999

Thanks..

Mohamed Ali Salim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-12 : 07:01:34

Thats because when you do /3 it wont loses some scale as it can store upto 2 decimal places alone. The catual value is 33.333.... So when you multiply 3 back it applies it to stored value (33,33) which is why you get 99.999 instead of 100.
If you check this it will give you 100 as result


DECLARE @x float = 100
DECLARE @y float= @x / 3
DECLARE @z float= @y * 3

SELECT @x, @y ,@z

output
-------------------------------------
100	33.3333333333333	100

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MohamedAliSalim
Starting Member

9 Posts

Posted - 2014-05-12 : 09:30:23

Thanks visakh16
So I used float but look at the table above and query the float returns -1.77635683940025E-15 it must be 0

What i should to do ?

Mohamed Ali Salim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-12 : 23:50:51

Because you are using floating for quantity and this will be the issue with floating point that you will be facing.

Do you really need to use float to store quantity ? Can you use decimal data type ?

KH
[spoiler]Time is always against us[/spoiler]

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-13 : 01:13:30

[code]select -1.77635683940025E-15, round(-1.77635683940025E-15,12),cast(-1.77635683940025E-15 as decimal(35,12))[code]
output:
[code]
(No column name) (No column name) (No column name)
-1.77635683940025E-15 0 0.000000000000
[/code]

-1.77635683940025E-15 is something like -0.000000000000001780

sabinWeb MCP

MohamedAliSalim
Starting Member

9 Posts

Posted - 2014-05-14 : 04:04:54

Thanks all
I have other columns AddValue , ExValue , CurrentValue , CurrentCost
it's must be float because...
--------------------------------
DECLARE @x DECIMAL (25,10)= 100
DECLARE @y DECIMAL (25,10)= @x / 3
DECLARE @z DECIMAL (25,10)= @y * 3
SELECT @x, @y , @z
------------------------------
@z must be 100 not 99.9999999999

this make my values is invalid

What i should to do ?

Mohamed Ali Salim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-14 : 04:21:27

round( @y * 3 , 2 )

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources