Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL help Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 11:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 11:35 AM
Points: 15, Visits: 32

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
Post #1354325
Posted Wednesday, September 5, 2012 1:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1354361
Posted Wednesday, September 5, 2012 1:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1354364
Posted Wednesday, September 5, 2012 5:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 11:35 AM
Points: 15, Visits: 32
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.707 6468818 11
LB_CLOSED_NEVER_STARTED 2012-09-04 00:07:59.923 6468833 117

Once again, any help is appreciated.
Post #1354454
Posted Wednesday, September 5, 2012 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
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 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.707 6468818 11
LB_CLOSED_NEVER_STARTED 2012-09-04 00:07:59.923 6468833 117

Once again, any help is appreciated.


Does it not matter that the EventID is different?
Is the EventID the PK?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1354461
Posted Wednesday, September 5, 2012 6:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 11:35 AM
Points: 15, Visits: 32
No, the eventid is just a link between the production db and the datawarehouse.
Post #1354467
Posted Wednesday, September 5, 2012 6:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1354476
Posted Wednesday, September 5, 2012 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 11:35 AM
Points: 15, Visits: 32
Kind of odd, that query returns 0 results.
Post #1354508
Posted Wednesday, September 5, 2012 8:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1354605
Posted Thursday, September 6, 2012 12:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1355032
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse