September 4, 2012 at 11:25 pm
I need some assistance in finding records from a table where a certain ID exists within close proximity to another ID, but before the subsequent ID. Example as follows: If TypeID 11 occurs first and within 1 minute of TypeID 22
Table1
ProcessDate TypeID
2012-09-01 00:00:01.123 11
2012-09-01 00:01:00.456 22
I can't seem to pin the syntax down. Any help is appreciated.
Thanks.
R
September 5, 2012 at 1:44 am
I really didn't understand the requirement. Would be better if you could come up with a better example. Please have a look at the link in my signature to get to know how to get the best answers from forum posts.
What does this statement mean??...."TypeID 11 occurs first and within 1 minute of TypeID 22"??
Does this mean 11 occurs just 1 minute before 22??....
This statement is a little better than the one mentioned above.
"I need some assistance in finding records from a table where a certain ID exists within close proximity to another ID, but before the subsequent ID".
It would be great if you could provide an example which contains the "certain ID", "another ID" and the "subsequent ID" mentioned in the above statement.
September 5, 2012 at 1:48 am
Your requirements are very unclear. Do you need to get records in relation to single requested id? Then you could use the following:
declare @table table (dt datetime, id int)
insert @table select '2012-09-01 00:00:01.123', 11
insert @table select '2012-09-01 00:01:00.456', 22
insert @table select '2012-09-01 00:01:02.456', 33
insert @table select '2012-08-31 23:59:58.456', 44
declare @reqid int
declare @reqdt datetime
set @reqid = 11
select @reqdt = dt from @table where id = @reqid
select *
from @table
where id != @reqid
and dt > dateadd(SECOND,-61,@reqdt) and dt < dateadd(SECOND,61,@reqdt)
Please provide the case setup as per link at the bottom of my signature. It will give much better idea on what you really want to achieve.
September 5, 2012 at 5:56 am
Sorry for the confusion, guys. Let's see if this explains it better. I have come across an issue where a table logs conditions with
a timestamp, eventid and eventtype. The condition 'Session_End_Prompt_Sent' with EventType = 11 is being logged before the condition
'LB_CLOSED_NEVER_STARTED' with EventType = 117. This is being logged typically within a few seconds, but definitely within a minutes
time of each other based on the Timestamp column. This should not happen. I need to know how many times this occurs within the table
but haven't been able to make a query work to get me that information.
Example:
Condition Timestamp EventID EventType
Session_End_Prompt_Sent 2012-09-04 00:08:00.7076468818 11
LB_CLOSED_NEVER_STARTED 2012-09-04 00:07:59.9236468833 117
Once again, any help is appreciated.
September 5, 2012 at 6:09 am
woody_rd (9/5/2012)
Sorry for the confusion, guys. Let's see if this explains it better. I have come across an issue where a table logs conditions witha timestamp, eventid and eventtype. The condition 'Session_End_Prompt_Sent' with EventType = 11 is being logged before the condition
'LB_CLOSED_NEVER_STARTED' with EventType = 117. This is being logged typically within a few seconds, but definitely within a minutes
time of each other based on the Timestamp column. This should not happen. I need to know how many times this occurs within the table
but haven't been able to make a query work to get me that information.
Example:
Condition Timestamp EventID EventType
Session_End_Prompt_Sent 2012-09-04 00:08:00.7076468818 11
LB_CLOSED_NEVER_STARTED 2012-09-04 00:07:59.9236468833 117
Once again, any help is appreciated.
Does it not matter that the EventID is different?
Is the EventID the PK?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 5, 2012 at 6:17 am
No, the eventid is just a link between the production db and the datawarehouse.
September 5, 2012 at 6:25 am
Being kind today and setting up your sample data for you, perhaps something like this might do the trick?
DECLARE @t TABLE (EventID INT, ProcessDate DATETIME, TypeID VARCHAR(2))
INSERT INTO @t
SELECT 1, '2012-09-01 00:00:01.123','11'
UNION ALL SELECT 1, '2012-09-01 00:01:00.456','22'
UNION ALL SELECT 2, '2012-09-01 00:01:01.123','11'
UNION ALL SELECT 2, '2012-09-01 00:01:50.456','33'
UNION ALL SELECT 2, '2012-09-01 00:02:00.456','22'
UNION ALL SELECT 3, '2012-09-01 00:02:01.123','15'
UNION ALL SELECT 3, '2012-09-01 00:02:50.456','33'
UNION ALL SELECT 3, '2012-09-01 00:03:00.456','22'
UNION ALL SELECT 4, '2012-09-01 00:03:01.123','11'
UNION ALL SELECT 4, '2012-09-01 00:03:50.456','33'
UNION ALL SELECT 4, '2012-09-01 00:04:00.456','25'
SELECT EventID
FROM @t
GROUP BY EventID
HAVING 60 >= DATEDIFF(second,
MAX(CASE TypeID WHEN '11' THEN ProcessDate END),
MAX(CASE TypeID WHEN '22' THEN ProcessDate END))
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 5, 2012 at 7:16 am
Kind of odd, that query returns 0 results.
September 5, 2012 at 8:40 am
woody_rd (9/5/2012)
Kind of odd, that query returns 0 results.
To which query do you refer? Mine returns 2 rows:
EventID
1
2
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 6, 2012 at 12:06 am
dwain.c (9/5/2012)
woody_rd (9/5/2012)
Kind of odd, that query returns 0 results.To which query do you refer? Mine returns 2 rows:
EventID
1
2
May be the OP means that when he applied your query to his environment it didn't produce any rows in the result set.
September 6, 2012 at 12:24 am
vinu512 (9/6/2012)
dwain.c (9/5/2012)
woody_rd (9/5/2012)
Kind of odd, that query returns 0 results.To which query do you refer? Mine returns 2 rows:
EventID
1
2
May be the OP means that when he applied your query to his environment it didn't produce any rows in the result set.
Possible. That is why I hinted that additional test data may be required to proof the query I provided.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 6, 2012 at 1:40 am
woody_rd (9/5/2012)
Sorry for the confusion, guys. Let's see if this explains it better. I have come across an issue where a table logs conditions witha timestamp, eventid and eventtype. The condition 'Session_End_Prompt_Sent' with EventType = 11 is being logged before the condition
'LB_CLOSED_NEVER_STARTED' with EventType = 117. This is being logged typically within a few seconds, but definitely within a minutes
time of each other based on the Timestamp column. This should not happen. I need to know how many times this occurs within the table
but haven't been able to make a query work to get me that information.
Example:
Condition Timestamp EventID EventType
Session_End_Prompt_Sent 2012-09-04 00:08:00.7076468818 11
LB_CLOSED_NEVER_STARTED 2012-09-04 00:07:59.9236468833 117
Once again, any help is appreciated.
From what I understood I built up some Sample Data and a query that gets a result set based on the Logic that I understood from your explanation. Following is the Query:
--Creating Table
Create Table Ex
(Condition Varchar(30),
Timestamp DateTime,
EventID BigInt,
EventType Int )
--Inserting Sample Data
Insert Into Ex
Select 'Session_End_Prompt_Sent', '2012-09-04 00:08:00.707',6468818, 11
Union ALL
Select 'LB_CLOSED_NEVER_STARTED', '2012-09-04 00:07:59.923',6468833, 117
Union ALL
Select 'Session_End_Prompt_Sent', '2012-09-04 00:02:00.707',6461818, 11
Union ALL
Select 'LB_CLOSED_NEVER_STARTED', '2012-09-04 00:01:59.923',6461833, 117
Union ALL
Select 'Session_End_Prompt_Sent', '2012-09-04 00:03:00.707',6462818, 11
Union ALL
Select 'LB_CLOSED_NEVER_STARTED', '2012-09-04 00:02:59.923',6462833, 117
Union ALL
Select 'Session_End_Prompt_Sent', '2012-09-04 00:04:00.707',6463818, 11
Union ALL
Select 'LB_CLOSED_NEVER_STARTED', '2012-09-04 00:03:59.923',6463833, 117
Union ALL
Select 'Session_End_Prompt_Sent', '2012-09-04 00:05:00.707',6464818, 11
Union ALL
Select 'LB_CLOSED_NEVER_STARTED', '2012-09-04 00:04:59.923',6464833, 117
Union ALL
Select 'Session_End_Prompt_Sent', '2012-09-04 00:06:00.707',6465818, 11
Union ALL
Select 'LB_CLOSED_NEVER_STARTED', '2012-09-04 00:05:59.923',6465833, 117
--Query For Your Requirement
Select * From
(
Select * From Ex Where Condition = 'Session_End_Prompt_Sent'
) As a
JOIN
(
Select * From Ex Where Condition = 'LB_CLOSED_NEVER_STARTED'
) As b
ON Convert(Date, CONVERT(Varchar(12), a.Timestamp)) = Convert(Date, CONVERT(Varchar(12), b.Timestamp))
AND DATEPART(HOUR, a.Timestamp) = DATEPART(HOUR, b.Timestamp) AND (DATEDIFF(MINUTE, b.Timestamp, a.Timestamp) Between 0 AND 2)
AND (DATEDIFF(SS, b.Timestamp, a.Timestamp) Between 0 AND 60)
I don't know how close it would be to the actual data that you are working with. It would be much easier if you could provide a portion of the log that contains data regarding the situation you are explaining. We could work with that data and get you some better results.
For the time being you could test this query on your data. If it doesn't work, then check how close it is to the result and may be you can play with the JOIN Condition a bit to get the desired output.
If it doesn't work at all then please post a part of the log the explains your requirement a little better.
Hope this helps.
September 6, 2012 at 3:35 pm
Using Vinu's sample data, this might get you pointed in the right direction. Some caveats here though. If there are two instances of event 117 following an event 11 within one minute, you will not be able to determine which instance of 117 should be paired to the 11.
WITH Data AS
(
SELECT
Condition,
Timestamp,
EventID,
EventType,
ROWNUM = ROW_NUMBER() OVER (ORDER BY Timestamp)
FROM ex
)
SELECT
a.*,
b.*
FROM data a INNER JOIN Data b
ON a.EventType = 11
AND b.EventType = 117
AND b.ROWNUM > a.ROWNUM
AND DATEDIFF(second,a.timestamp,b.timestamp) <= 60
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply