|
The code was for SQL Server which most of the people here use. It's been ages since I last used Access, but try just subtracting the two times (end.Date_TimeWorked - start.Date_TimeWorked). I'm not sure but it may give you the result you want.
|
|
|
|
|
Well I was playing with Excel sheets to database, so I was trying something on access database. and thank you for your reply, the query looks like it works.. but I got the idea which i was looking for, thanks 
|
|
|
|
|
You're welcome
|
|
|
|
|
Hello Mika, I need your help on this query, you suggested me
SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes
FROM TableName start,
TableName end
WHERE start.ID = end.ID
AND start.Status = 'Ready'
AND end.Status = 'Completed'
Ready-Complete ok, but I want one more condition added to it, I need
time between Date_Timeworked when status is Ready and Rejected?
|
|
|
|
|
If you want them on separate rows, you can use UNION and duplicate the query (just modify Status for end condition)
If you want them on the same row it would be something like (I'll change the aliases so that this becomes more clear):
SELECT DATEDIFF(minute, readyRow.Date_TimeWorked, completedRow.Date_TimeWorked) AS FromReadyToCompleted,
DATEDIFF(minute, readyRow.Date_TimeWorked, rejectedRow.Date_TimeWorked) AS FromReadyToRejected
FROM TableName readyRow
LEFT OUTER JOIN
TableName completedRow
ON readyRow.ID = completedRow.ID
LEFT OUTER JOIN
TableName rejectedRow
ON readyRow.ID = rejectedRow.ID
WHERE readyRow.Status = 'Ready'
AND completedRow.Status = 'Completed'
AND rejectedRow.Status = 'Rejected'
The query is now modified to outer joins since I believe that the same ID cannot be both completed and rejected at the same time.
The example may contain typos, but you'll get the idea
Hope this helps,
Mika
|
|
|
|
|
Hi!
Would it be possible to detect if your SQL database is opened and some actions (DELTE | INSERT| UPDATE) performed in the database. I want to detect it with pure SQL statements and a pre-defined trigger function could initiate an autoresponder which could tell me the IP address of the intruder?
Thanks
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
How would you know if the access is an intruder ? The whole idea of having a database is that select,insert,update and delete would be occurring all the time. 
|
|
|
|
|
Your are partially right about it but what if a user (not an authenticated one)tries to perform such action in the database?
Presumable it does mean that for an intruder: He has allready comprimised the server and captured the password and login ID of my database. What if SQL server is still up an running? Wouldn't it be possible to trigger one procedure within SQL database?
I am not an expert about SQL server. You may find the question quite absurd.
Thanks.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
JUNEYT wrote: He has allready comprimised the server and captured the password and login ID of my database
So he would be able to start sql server anyway. If you have been compromised to this extent you are already, as they say round here, "up sh*t creek without a paddle"
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I know how to set up a maintenance plan and all but only if I'm gonna backup the database on the "same" machine. Now I need to specify another machine to backup the database. The other machine also has sql server. How can I accomplish that? Please, Add step to the answer.
Thanks
|
|
|
|
|
I think this is a limitation of SQL Server, I know we use SQL Backup from Red-Gate to do this as well as compression. Bloody useful toolbox.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When defining a backup, use a backup device which points to a network share. Preferrably use UNC naming. For more info: Backup Devices[^]
Hope this helps,
Mika
|
|
|
|
|
I didn't think you could point to a network share when backing up SQL Server - thank heavens I'm not employed as a DBA 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i have a table with repeated values like this in block_name field:
DUBAI
DUBAI
MANAMA
DUBAI
MANAMA
BEIRUT
CAIRO
CASABLANCA
MANAMA
BEIRUT
DUBAI
how can i delete all reepeated values and just keep one value for each using SQL statement?
|
|
|
|
|
Do you have any primary key or other unique column or any date column which can be used for ordering on that table.
|
|
|
|
|
yes i have..
it's block_id
|
|
|
|
|
Ok, if block_id is numerical and unique, you can try something like following:
DELETE FROM TableName
WHERE EXISTS (SELECT 1
FROM TableName T2
WHERE T2.Block_Name = TableName.Block_Name
AND T2.Block_Id > Table_Name.Block_Id)
Hope this helps,
Mika
|
|
|
|
|
Another way:
delete from mytable where block_id not in <br />
(select top 1 t1.block_id from mytable as t1 where t1.block_name=mytable.block_name order by t1.block_id)<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
listen, don´t delete prevent it from happening
do this in your procedure
table_countries
|id_country|name|...|
you ask for this right?
@country varchar(50)
if exists(select * from table_countries where name = @country)
begin
select 'ERROR: that country exists'
end
else
begin
insert into table_countries values (@country)
select 'OK: country added'
end
-----------------
it would help if you read and write them in upper() or lower()
upper(hello) = HELLO
lower (HELLO) = hello
use it like this: ...upper(@country)...
that way you won´t get Dubai,DUBAI,dubai etc
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
I'm trying to figure out how to include google maps in my application. so i when i get the cords i can render a google map. ideas on how to do this?
|
|
|
|
|
Don't know, except you are in the wrong forum
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi. I'm trying to locate the date/time of a certain data. I've tried the typical "SELECT * FROM this_table WHERE column_name = '<some date>'. This does not return anything. Can someone help me with this? I want to search quickly for the specified date and time. Thanks! </some>
|
|
|
|
|
Hi,
If the data type of column_name is datetime, 'some date' should be convertible to datetime implicitely or you should convert it explicitely. For conversion use CONVERT function. For example:
column_name = CONVERT(datetime, '2008-09-07 12:02:00', 120)
Also notice that this is exact match so minutes, seconds and so on must match.
Mika
|
|
|
|
|
Wow thanks. However, What if I would want to search all records under 2008-09-07?
|
|
|
|
|
If you know for sure that the time is always at midnight, then you can use equality like:
column_name = CONVERT(datetime, '2008-09-07', 105)
If time portion can vary, use ranges like:
... column_name >= CONVERT(datetime, '2008-09-07', 105)
AND column_name < CONVERT(datetime, '2008-09-08', 105)
Hope this helps,
Mika
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
