|
SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY
The above is not complete. Could someone help?
Thanks
|
|
|
|
|
|
Hmmm
okay
4 colums with int values
I want a percent difference
A.Attempts A.Accepts B.Attempts B.Accepts
10000 9000 6500 4500
10000 - 9000 = 1000
Then
6500 - 4500 = 2000
Then difference and yes it may be negative
1000 - 2000 = -1000
Then
10000 - 9000 = 1000 / -1000 = -1
SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY
|
|
|
|
|
I don't see where your SQL matches the example you've given. Where is the SUM() coming from, what are you doing with the SUBSTRING.
From the example you gave this would appear to be the correct code:
SELECT cast((A.Attempts-A.Accepts) as float) / cast(((A.Attempts-A.Accepts) - (B.Attempts-B.Accepts)) as float)
FROM ...
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Sorry but what I was attempting to do with the substring function is return 4 characters in lentgh .
The code you provided works and thank you
|
|
|
|
|
Hi,
I am facing a problem while inserting a datarow with data in to a new dataTable.
can anybody plz help me how to add a row with data to a new data table.
regards,
Vinu
|
|
|
|
|
It would be very helpful , to show us whats the error you get ?
Some code would be helpful too..
Bahadir Cambel
|
|
|
|
|
Hi,
I'm having a runtime error when I execute the BeginTrans() _Connection method on a MSAccess database.
Can someone tell me if there is any condition which can cause the error on this method?
I don't believe that the problem would be the ADO version, because I have another application that uses this method on a MSAccess database without any problem, but any way, I am working with ADO 2.7.
Thanks in advance,
Vinicius
|
|
|
|
|
|
Yes it is.
I found, somewhere in MSDN library, that if there is a recordset opened, the BeginTrans doesn't work. In my case, I have a recordset opened. I tryed to do the operation while the recordset was closed and that worked. The problem is that I need the recordset to be opened.
Vinicius
|
|
|
|
|
I am needing to copy data from an MSSQL table to a dbaseIV table and would ideally like to do this with an SQL trigger on the source table.
Can someone please advise if it is possible to do this and if so provide some clues on how to go about this? I have established an ODBC connection to the dbase table folder but I cannot resolve how to address this from Query Analyser.
Many thanks,
Kerry
Kerry Johnson
Business Systems Consultant
|
|
|
|
|
Hi.
I'm trying to connect to a remote SQL serer express through TCP/IP.
I entered the <IP address\instance name> in the server name field. I tried also without the instance name.
I tried with integrated security as well as authentication. Also, I tried with the port address, and I get the error message "TCP Provider: Connection closed forcibly by the remote computer".
P.S. the protocol is enabled on noth the local and remote sides.
Can anyone explain me please how to establish a connection?
Thanks,
Yaakov
|
|
|
|
|
|
I'm trying to connect from the VS IDE.
I own both the server and the client, and I see no problem on both.
Can you reffer me to a relevant artical?
Thanks,
Yaakov
|
|
|
|
|
Hi.
I want to change a column in a table from BIGINT to DECIMAL or NUMERIC.
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations (like multiply and adds) on these data types (dec and numerics).
and, btw, what is the diffrence between 'NUMERIC' and 'DECIMAL'?
thanks
|
|
|
|
|
Menny Even Danan wrote:
what is the diffrence between 'NUMERIC' and 'DECIMAL'?
Reading the documentation indicates that they are synonyms for each other.
Menny Even Danan wrote:
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations
Normally the main killer in performance is to do with volume of data being processed(*) so I'd say that on a large dataset any performance hit is negligable. So, I'd suggest that you are careful about the size of the data. This table from the books on-line will help:
+-----------+---------------+
| Precision | Storage bytes |
+-----------+---------------+
| 1 - 9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
+-----------+---------------+
[EDIT] Missed a bit: By comparison a BIGINT is 8 bytes in length but is constrained to a scale of 0 (nothing after the decimal point) and has a maximum value of (2^63)-1 while a NUMERIC/DECIMAL has a potentially much greater range [/EDIT]
* not necessarlily the same as volume of data returned
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi All,
I am wondering if anybody know sql statement of how to import all the text from the text file into Access database field?
I have a text file, a.txt and afield called TextBody in the table.
TextBody is a memo size.
I was inserting like following
<br />
aTextfile.open("c:\\a.txt",ios::in);<br />
if(aTextfile)<br />
{ <br />
while(!aTextfile.eof())<br />
{<br />
aTextfile.getline(data,1024,'\n');<br />
temp.Format("%s",data);<br />
<br />
qstr.Format("UPDATE %s Set TextBody = TextBody & '%s' ",tablename,temp);<br />
rs.m_pDatabase->ExecuteSQL(qstr);<br />
}<br />
<br />
aTextfile.close();<br />
}<br />
But it takes so long to import all the text from the a.txt. Is there any better way?
Thanks
|
|
|
|
|
Can you read the whole text file into a string and then write the string to the database?
|
|
|
|
|
I'm trying to create a query that counts records based on time periods. I'm querying two tables, one containing SubmittedDate, the other CompletedDate. I'm using DateDiff to calculate the time difference between the two. What I want to do is end up w/ a count for mulitple periods of time like....
< 2 Hours 3
2 - 4 Hours 6
4-8 Hours 10
1 Day 12
1 - 3 Days 8
5 Days 4
> 5 Days 2
Any ideas?
Jeff Martin
My Blog
|
|
|
|
|
|
Please tell me! how can i get all tables name in access file (.mdb) ?
Thanks!
xyz
|
|
|
|
|
I will assume that you do not have Access and/or can not use it.
When you use the Data Adapter Configuration Wizard, it will show the tables available and after you have choosen the one(s) you want, it will show you the fields in the table.
|
|
|
|
|
Try this, this is working on SQL Server.
Create Sample That Lists Tables in a Database
The following sample lists tables in the SQL Server Northwind database.
OleDbSchemaGuid.Tables returns those tables (including views) that are accessible to a given log on. If you specify an Object array of {Nothing, Nothing, Nothing, "TABLE"}, you filter to include only a TABLE_TYPE of TABLE. You then list the table name (TABLE_NAME) of each row in the returned schema table. 1. Start Visual Studio .NET.
2. Create a new Visual Basic Console Application project. Module1.vb is added to the project by default.
3. Open the Code window for Module1. Paste the following code into the top of the Code window, above the Module declaration: Imports System.Data
Imports System.Data.OleDb
4. In the Code window, paste the following code into the Sub Main procedure.
Note You must change User ID <username> and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
Dim cn As New OleDbConnection()
Dim schemaTable As DataTable
Dim i As Integer
'Connect to the Northwind database in SQL Server.
'Be sure to use an account that has permission to list tables.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;Password=;Initial Catalog=Northwind"
cn.Open()
'Retrieve schema information about tables.
'Because tables include tables, views, and other objects,
'restrict to just TABLE in the Object array of restrictions.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
'List the table name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Console.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToString)
Next i
'Explicitly close - don't wait on garbage collection.
cn.Close()
'Pause
Console.ReadLine()
|
|
|
|
|
Hi all.
I have a program running from Start > Programs > Startup .
When I start Winows, the program starts automatically, and I get SqlException (Sql Server Express 2005).
If I start the program manually, It starts normally.
I think that the program is loaded before the SQL service.
How can I check or change this?
Thanks,
Yaakov
|
|
|
|
|
You can wait until the service is started. Check This[^]. Remember to start the service automatically when you start your OS.
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|