1 table; complex query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

1 table; complex query?

Author

Topic

Rovastar
Starting Member

38 Posts

Posted - 2008-02-01 : 07:27:11

I thought I was getting ok at query writing for a web server admin but I am a bit stumped on this on.

I have a message table like this:

MessageLogId MessageId S2SMessageID CreatedDate
1787462 8a8eab22-17cf-1596-0117-cf16416b0020 22 31/01/2008 09:03:38.773
1787463 8a8eab22-17cf-1596-0117-cf16416b0020 23 31/01/2008 09:03:39.710
1787464 664F7643-EAD7-486F-8099-FAE1C51B9A6C 18 31/01/2008 09:03:39.790
1787465 8a8eab22-17cf-1596-0117-cf1646e90021 22 31/01/2008 09:03:40.180
1787466 664F7643-EAD7-486F-8099-FAE1C51B9A6C 19 31/01/2008 09:03:40.257
1787467 8a8eab22-17cf-1596-0117-cf1646e90021 23 31/01/2008 09:03:41.320
1787468 8a8eab22-17cf-1596-0117-cf164d130022 10 31/01/2008 09:03:41.727
1787469 8a8eab22-17cf-1596-0117-cf164d130022 11 31/01/2008 09:03:41.990

for XML messages received to our s2s system.
A request is received and later a second or so a response is send.
To make these requests identifiable we have the messageid which is the same for the request and response.

Now I want to find out simply all message request that took more than say 10 seconds to process.

And I am stumped

I am thinking I have 2 of teh same table in a query and match the messageids up and working out the time taken is stumping me. How will I know what is the starting time?

If it helps all request events have an S2SmessageID as an even number and all responses are odd numbers (expect an unsuccessful request response or error which is a 0) - or the request come in first sequentially in the messagelogid table

Can anyone enlighten me?

Cheers,

John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 08:12:44

Can you try like this?

SELECT t1.MessageId 
FROM Table t1
INNER JOIN Table t2
ON t2.MessageId =t1.MessageId 
AND DATEDIFF(ss,t1.CreatedDate,t2.CreatedDate) >10

will give

Rovastar
Starting Member

38 Posts

Posted - 2008-02-01 : 08:25:45

Thank you. That does the trick. I didn't think about datediff.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 08:29:42

You are welcome.Glad that i could help you on this. Feel free to come back whenever you face any probs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources