|
Hi,
I am using sql server 7.0 in that i am having a table of 3 feild i.e.
empName, Logindate, HoursWorked
select * from tablename
Result:
empName LoginDate HoursWorked
------- ------------ ------------
Amit 06/03/04 13:44:45
Amit 06/04/04 14:44:45
now i want the sum of the field "HoursWorked"
Can any body help?
Actually im taking 'LoginTime' and 'LogoutTime' Datatype
'DateTime' from "login" table and subtraction of this two field is strored in field 'TimeDiffrence' datatype varchar
Query i m using is
"convert(varchar(8),(logoutTime-loginTime),14)Timediffrence"
now i want the sum of the field "Timediffrence"
|
|
|
|
|
try:
Select empName, Sum(Cast(HoursWorked as int))
From tablename
Group by empName
Or
Select empName, HoursWorked = Sum(DateDiff(ss, LoginTime, LogoutTime)) / 3600
From login
Group by empName
Wout Louwers
|
|
|
|
|
Thanks let me check it out
|
|
|
|
|
Hi
As almost a continuation of my last question, I'm now trying to select the latest date entry record for ALL products out of a table - the latest date varies for the products. I'm using a SELECT DISTINCT productID, but when I try to use MAX(date) it of course is choosing only those with literally the latest date....
Hope you can help again! I am learning..
Patrick Collins
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
Try:
<br />
Select ProductID, Max(Date)<br />
Group by ProductID<br />
Wout Louwers
|
|
|
|
|
Thank you Wout,
How embarrassingly easy!
Just to check, if I select other fields for the product from that record, it has effectively limited them to the record with the Max Date for that product?
Thanks in advance
Patrick Collins
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
No, the GROUP BY clause will only permit you to select the fields in the GROUP BY clause itself and any field in an aggregate function.
To do what you want you would have to create some sort of subquery
SELECT * FROM my_table
INNER JOIN
(SELECT product_id, MAX(date_field)
FROM my_table GROUP BY product_id)
AS inner_table
ON my_table.product_id = inner_table.product_id
This will allow you to return all the fields from your table.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Thanks again Colin,
Got there - It would have helped if any of the tables I was working with were normalized!
Patrick
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
I'm currently using Enterprise Manager to connect to some SQL 2000 Servers to manage them. The problem is the Enterprise cannot connect with some servers but can with other servers, the error is: "SQL Server does not existe or access denied ConnectionOpen(Connect())". To verify the connection, I use Query Analyzer and it can connect all servers. (All connection library was enable for client and servers).
Please help me.
|
|
|
|
|
Would check that the registration properties are correct, and that they are using the default instance name (May be barking up the wrong tree however!)
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
It could be that your connection times out before it can connect to the SQL Server. Try a larger setting.
|
|
|
|
|
How to set timeout for SQL server check access rights ?
SQL server on remote computer either
connects very fast if succeeded,
but if user has no access right connects 10-30 seconds
and makes imression that application is hanged.
So question is:
how to decrease access rights time of check ?
Mostly needed to requrement to program it fast.
How such a problem is usually resolved in else ways ?
Asyncro connection works bad due to driver
not releases Connection.open() request fully,
but sends messages after it,
during that peiod application hangs.
Does that need full multi - threading programing?
Thanks.
|
|
|
|
|
Believe that one of the properties of the connection string is "ConnectionTimeout"
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I tried to set all connection properties
wit word "Timeout"
but for checking rights it os not works.
|
|
|
|
|
|
For check rights it is not working.
|
|
|
|
|
Can you clarify what you mean by access rights. When forming a connection string, you can specify a UserID / Password combination, if you set the connection timeout to a small value and then attempt to connect using a specified user account, then if the login is not found straight away (as seems to be the case from your original post), then the timeout will kick in and the connection attempt will be refused...
Can you give me a little more info please?
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
When I connect to SQL server to which I has permission.
than I wait 20-30 secondes,
instead of 2-3 suffitient.
connection timeout not affects on this.
|
|
|
|
|
Hi,
I need to know the table structure of an MS-Access database. I tried Desc table sql and it doesnt seem to work. Is there any other method to know the structure of a table. I need to know it from VB/VC code and populate the result to a recordset.
Thanks for any help
Vikram Attiganal
|
|
|
|
|
Using ADO, you can call the Connection.OpenSchema method with the adSchemaTables flag. This returns a recordset with all the tables in. You can then iterate through this and call OpenSchema with asSchemaColumns and a table name specified.
Alternatively, you could use ADOX - this exposes a Catalog object, which if you give it an active connection object, holds an object model of the general structure
Catalog+
|
+
Tables+
|
+
Table+
|
+
Columns+
|
+
Column
To use ADOX, set a reference to ADO Ext. For DDL and Security
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I have a vb.net script which inserts a new record into a sqlserver database.
I have ListID as the primary key (autonumber). I need to find a way of returning this number once I have saved the first part of the record, as on the next page I save some other fields of the same record.
Does anyone have any ideas?
*wasn't sure whether to post to here or to VB.net so sorry if on the wrong board*
Cheers, V
|
|
|
|
|
Hi there. You can use a select command with commandText "Select @@Identity" to get the inserted key from SqlServer. Here's a link[^] to a discussion forum thread that shows an example of this in context.
|
|
|
|
|
Thank you very much,
I have it returning the value now.
Cheers, V
|
|
|
|
|
Hi all,
I have a table with one column set as primary key, which
is not supposed to be duplicated. This key has a corresponding textbox on the form which the user enters manually.
I use .ExecuteNonQuery() after setting up the parameters of a SQLCommand object to insert a row. The SP does a simple insert routine.
Using one single button for updating the table (new row/existing row), is there a simple / automatic method to modify an existing row in the table (using Triggers, etc.) ? Or, do I have to write the code in the button that will -
first, fire an sp that returns back a boolean indicating whether a row with the user-entered primary key already exists, then
second, conditionally set the .commandtext value to either of the two SPs (INSERTSP / UPDATESP) depending on whether the boolean was row-exists/does-not-exist.
finally, fire the .executenonquery() ?
Any help will be highly appreciated.
Thanks in advance,
Sanjay.
|
|
|
|
|
How about this?
Instead of having two procedures for a simple task, have a single procedure which will check if the record is existing. If exists, update else insert. And finally this procedure can return you a status if you want to know if the record is inserted or udpated.
Bhaskara
|
|
|
|