|
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
|
|
|
|
|
I tried to use "select @@identity" in my ADO/ACCESS/C++ application to retrieved
the primary key of the last inserted record but failed
the table only contain two column
1.cNo ---> is a primary key (autonumber)
2.cText ----> just dummy field (char 255)
this is the code
CString strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\test\\test.mdb;");
ADODB::_ConnectionPtr connectionPtr;
hr = connectionPtr.CreateInstance( __uuidof( ADODB::Connection ) );
connectionPtr->Open((const TCHAR*)strConnect,_T(""),_T(""),NULL);
CString queryString = _T("insert into test_table (cText) values ('dummy')");
connectionPtr->Execute((LPCTSTR)queryString,NULL,ADODB::adCmdText);
CString getIDQueryString = _T("select @@identity");
ADODB::_RecordSetPtr rPtr = connectionPtr->Execute((LPCTSTR)getIDQueryString,NULL,ADODB::adCmdText);
int lastInserted = rPtr->Fields->GetItem(_T("cNo"))->GetValue().lVal;
The execution of getIDQueryString is fine.
but I got an error when try to get the value of 'cNo' , saying that it cannot be found (cNo is the table primary key with autonumber).
Hope someone can give me some solution.
Thank in advance.
|
|
|
|
|
I could be wrong (it's been so long since i used Access) but I don't think it supports this kind of operation - all you can do is runa "select max(id)" or "select top 1 id from.. order by id desc" type query... but note that these are NOT specific to your connection. If someone else happens to insert a record between your insert and you calling this, you will get their last insert id, not yours... one reason why Access should not be used in multi-user environments.
|
|
|
|
|
I get it working now(after losing few of my hair) by modified the query to "SELECT @@IDENTITY AS temp"
and then calling GetData on "temp" field which is work fine now.
but as you said since ACCESS doesn't even support multi query i guess this is still as dangerous as calling MAX(id).
but my application is Single user I guess it ok for now.
Thank for your answer.
|
|
|
|
|
Hi
I am not sure of the right terminology but here goes. (I am fairly new to this any help will be appreciated)
I am trying to take data and assign it a new string name each time a new row has been through the loop. This is in asp classic.
rs1.Open "Select * from email where deleted = 0;", connStr1, 3 , 4
startbody = rs1("body")
rs1.MoveNext
rs1.Close
Set rs1 = Nothing
for each record that comes out I need a new sting name
example
startbody1 = rs1("body") (first record returned)
startbody2 = rs1("body") (second record returned) etc.
modified on Sunday, September 7, 2008 4:53 PM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
