SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL help


SQL help

Author
Message
woody_rd
woody_rd
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 62
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
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3645 Visits: 1626
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 ;-)
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12350 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
woody_rd
woody_rd
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 62
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.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41270 Visits: 20001
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
woody_rd
woody_rd
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 62
No, the eventid is just a link between the production db and the datawarehouse.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17723 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
woody_rd
woody_rd
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 62
Kind of odd, that query returns 0 results.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17723 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3645 Visits: 1626
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 ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search