Number of decimal places returned by dividing integers?
--in sql server 2005.
SELECT 5/13; ----> 0 SELECT Cast(5/13 AS real); --> 0 SELECT 5.00/13.00; --> 0.3846153 SELECT cast(5/13 as decimal(10, 2)); --> 0.00 SELECT cast(5 as decimal)/cast(13 as Decimal); --> 0.3846153846153846153
Is there a global setting for number of decimal places returned by dividing integers? All I need is to have the result as .38
There is no global setting. (That is the question you are asking.)
When you divide two integers, the result is an integer. You simply want to express the value to two decimal places. John Capelletti has the right answer in a comment. Convert the values to non-integers and then convert back to the format you want. I normally do this by multiplying by 1.0:
select cast((5 * 1.0 / 13) as decimal(10, 2)
There are quite arcane rules for the number of decimal places produced when you do arithmetic on decimal values.
See also questions close to this topic
MYSQL: Using outer alias in a subquery
I'm trying to display a list of payments made on transactions and show the current balance after each payment. Below is an example of expected result
| number | DATE(p.date) | total | paid | balance | | 1355 | 2016-10-31 | 899.00 | 450.00 | 449.00 | | 1355 | 2016-12-06 | 899.00 | 449.00 | 0.00 | | 1359 | 2016-09-28 | 4045.00 | 1515.00 | 2530 | | 1359 | 2016-10-24 | 4045.00 | 35.00 | 2495.00 | | 1361 | 2016-09-28 | 1548.00 | 1548.00 | 0.00 |
and here is my query so far, but have an error in the where clause
select t.number, DATE(p.date), ti.total 'total', SUM(p.amount) 'paid', ti.total - paid.total 'balance' from payments p left join transactions t on p.transaction_id = t.id left join ( select inner_ti.transaction_id, sum((inner_ti.price - inner_ti.discount) * inner_ti.quantity) 'total' from transaction_items inner_ti group by inner_ti.transaction_id ) ti on t.id = ti.transaction_id left join ( select inner_p.transaction_id, sum(inner_p.amount) 'total' from payments inner_p where inner_p.date <= p.date -- error unknown column p.date group by inner_p.transaction_id ) paid on t.id = paid.transaction_id group by t.number, DATE(p.date), ti.total, paid.total order by DATE(p.date) ASC
Please note that I'm grouping by
p.datesince we are concern on payments made within the day.
Can someone please enlighten me why I'm getting that error? And is there any workaround to achieve the expected result?
Get columns name and write it into a file
Hi guys i wrote a bash script that execute a query e and print the result into a file but it doesn't work
source ../db.config msqlcmd="mysql -u $DB_USER -p$DB_PASS -s -B -q" echo "USE INFORMATION_SCHEMA;\nSELECT * FROM COLUMNS" | $mysqlcmd > ../tmpsql/columns.txt
Into the db.config there are the credential to access to db
Excel VBA Filtering: AODB vs Autofilter vs SQLite
I have an Excel sheet with data in about 100,000 rows, 16 columns. I have to extract data (one value from the 1 row I find) from this table a few thousand times. I have been using Autofilter to extract this value, but have also tried AODB/SQL statements to see if I can improve the speed of the queries.
The data is sorted by column 1. When I use Autofilter, it takes on average 0.3 seconds to get my value, no matter the location of the value. AODB takes between 0.1 to 0.6 seconds to find the value, depending on where the value is in the table (if the value is near the top of the table, it takes 0.1 seconds, while if the data is near the end of the table it can take up to 0.6 seconds). I had expected the AODB to be much faster than Autofilter, but it seems that on average they are about the same.
When I import this data into SQLite and use a tool such as SQLiteStudio, the query returns the value in less than 0.01s!
Can I change anything in my code to to speed up the Queries in VBA?
Codes are below. AODB method:
Sub AODB_method() 'Freeze Screen and other options to run code faster Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayStatusBar = False Application.EnableEvents = False Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim vv As Double Dim Time2 As Double Time2 = Timer Set cn = New ADODB.Connection Set rs = New ADODB.Recordset With ThisWorkbook If Application.Version < 12 Then cn.ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & .FullName & ";" & _ "Extended Properties=Excel 8.0" Else cn.ConnectionString = _ "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & .FullName & ";" & _ "Extended Properties=Excel 8.0" End If cn.Open Set rs = cn.Execute("SELECT [Cl] FROM [Table1$] WHERE [Wind]=150 AND [Weight]=200000 AND [Altitude] = 20000 AND [ISA] = 0") vv = rs.Fields(0).Value rs.Close cn.Close End With 'Turn Options on again Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.DisplayStatusBar = True Application.EnableEvents = True Debug.Print "Connection Time: " & Timer - Time2 End Sub
Sub Autofilter_method() 'Freeze Screen and other options to run code faster Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayStatusBar = False Application.EnableEvents = False Dim vv As Double Dim Time2 As Double Time2 = Timer With ThisWorkbook.Worksheets("Table1") .AutoFilterMode = False With .Range("A1:H1") .AutoFilter .AutoFilter Field:=4, Criteria1:=0 .AutoFilter Field:=2, Criteria1:=200000 .AutoFilter Field:=3, Criteria1:=20000 .AutoFilter Field:=1, Criteria1:=-150 End With End With vv = Range("H" & finallastrow("Table1", "H")).Value 'Turn Options on again Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.DisplayStatusBar = True Application.EnableEvents = True Debug.Print "Connection Time: "; Timer - Time2 End Sub
I am using Excel 2013. The Jet OLEDB provider seemed faster but I can't get the value to be saved in variable vv (I get an error).
When Wind is -150 (Column1, -150 is near the top of the table), the AODB query runs in 0.1s, while a wind value of +150 takes 0.6 seconds.
Thanks for your help!
nvarchar or varchar or date in joins of SQL Server
I have a management project developed in Web using SQL Server 2008 R2 and Windows Application using SQL Server CE 4.0.
Both uses almost same database structure. I have a fees table as :
TABLE [fees] ( [feeid] bigint NOT NULL IDENTITY(1,1), [acno] int NULL DEFAULT 0, [billingmonth] nvarchar(10) NULL, [oldbal] numeric(18,2) NULL DEFAULT 0, [duedtamt] numeric(18,2) NULL DEFAULT 0, [afterdtamt] numeric(18,2) NULL DEFAULT 0, [bal] numeric(18,2) NULL DEFAULT 0, [depamt] numeric(18,2) NULL DEFAULT 0, [totdpo] numeric(18,2) NULL DEFAULT 0, [depdt] datetime NULL )
billingmonthwill always use format
BillingMonthuses various joins (inner and left outer join) in other tables.
How to increase performance of joins with
BillingMonth? , should i :
- Convert nvarchar to varchar ( as it will always store Month in SQL2008R2 )
- Convert nvarchar to datetime ( as first day of month 01-MMM-yyyy in SQL Server CE and SQL Server 2008 R2)
How do you update a DB table with Python where the update statement uses a Python method?
I'm having a hard time finding an online example of this. I want to update a database table by passing the columns of the table into the function. So my pseudo code looks like this:
import pypyodbc from fuzzywuzzy import fuzz from fuzzywuzzy import process UPDATE table SET result = fuzz.ratio(DBColumn1,DBColumn2)
Import data from Oracle to SQL server using SSIS
We want import data from Oracle to SQL server using SSIS I was able to transfer data from Oracle to one table (Staging)in SQL. then I need to transform data and I found that I need to run stored procedure to transform the data from Staging to Actual production data. But I wonder How we can do it.
What is the quickest way to update each row in one table with a use-once value from another table?
I have a table called
dbo.RecycleProductIDthat has just a primary key
ProductIDcolumn and a
ProductID (pk, int) | Used (bit) 23 1 65 1 68 1 90 NULL 104 NULL ... 60983471 NULL
The table above has about 6.5m
ProductIDvalues that need to be 'recycled' and assigned to products in a different table. Once a
ProductIDhas been assigned to a product, I must update the
Usedcolumn value to
1to indicate it has been used. There are gaps in the
ProductIDsequence, some in the thousands (.e.g it can jump from 1010 to 8055) and there are way more rows in this table than the receiving table
dbo.Producttable (has about 1.5m rows) is just a list of products but with no
ProductID (pk, int) | ProductName (varchar) 23 Toothpaste 65 Speakers 68 Galaxy S8 NULL Plate NULL Monitor NULL Carpet .....
I am currently running a while loop to get the
DECLARE @Max int = (select max(ProductID) from [dbo].[RecycleProductID]); DECLARE @Min int = (select min(ProductID) from [dbo].[RecycleProductID]); while @Min <= @Max begin UPDATE TOP (1) [dbo].[Product] SET ProductID = (SELECT TOP 1 ProductID FROM [dbo].[RecycleProductID] b1 WHERE b1.Used IS NULL ORDER BY ProductID ASC) OUTPUT INSERTED.ProductID INTO dbo.UsedProductID WHERE ProductID is null; UPDATE rp1 SET rp1.Used = 1 FROM [dbo].[RecycleProductID] rp1 INNER JOIN dbo.UsedProductID ub1 ON ub1.ProductID = rp1.ProductID set @Min = @Min+1 end;
With this basically being a CURSOR, it is taking forever. It has been running for almost two days and only updated about 326515 rows. Is there a quicker way of doing this?
SQL Execute AS userA vs Login as userA
I have a tableA that disallow userA to update it. (userA is a DB user)
DENY UPDATE ON tableA TO userA;
When I login with Window Authentication, I update tableA with below query.
EXECUTE AS USER = 'userA'; GO update tableA set colA='0001' where id=1; GO REVERT;
The UPDATE permission was denied on the object 'tableA', database 'DW', schema 'dbo'.
====> This is my expected result.
However, when I changed to login as 'userA' with SQL Server Authentication.
update tableA set colA='0001' where id=1
====> It allowed to update! It is supposed to block from updating, right? May I know why it does not block it? Anything I have missed out?
T-SQL Select - Over clause equivalent LINQ
I need to write an extension method for LINQ on C# which implements following scheme
...ROW_NUMBER() OVER(PARTITION BY columnName1 ORDER BY columnName2 DESC) ....
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD, ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS RowNumber FROM Sales.vSalesPerson WHERE RowNumber = 1