T-SQL query to exclude certain rows

Question

Post reply

T-SQL query to exclude certain rows

kevin_nikolai

SSCarpal Tunnel

Points: 4343
More actions
May 18, 2010 at 1:39 pm

#221597

/*
Need assistance in creating a T-SQL query to exclude certain rows:
The idea is to calculate each odometer and date where Event is 1 and 0,
skip second consecutive 1 or 0 (Event), calculate next section where Event is 1 and 0.
Below is an example of what should be excluded, see (Event 1/1 ***).
This example works (already applied today), but I am looking for more examples.
*/
-------------------------------------------------------------
CREATE TABLE MyTable2 (
UserID int,
REGNO char(9),
StartDate varchar(30),
EndDate varchar(30),
Odometer int,
EndOdometer int
)
------------------------------------------------------------
CREATE TABLE MyTable3 (
RNO int,
UserID int,
REGNO char(9),
StartDate varchar(30),
EndDate varchar(30),
Odometer int,
EndOdometer int
)
------------------------------------------------------------
insert into MyTable2 (
UserID,
REGNO,
StartDate,
EndDate,
Odometer,
EndOdometer
)
select 12, 'YCA100', '26-03-2010 08:29', '26-03-2010 08:31', 194780, 194780 union all -- Event 1/0
select 12, 'YCA100', '26-03-2010 08:32', '26-03-2010 09:04', 194780, 194802 union all -- Event 1/0
select 12, 'YCA100', '26-03-2010 10:17', '26-03-2010 10:27', 194820, 194829 union all -- Event 1/1 ***
select 12, 'YCA100', '26-03-2010 09:41', '26-03-2010 10:27', 194802, 194829 -- Event 1/0
------------------------------------------------------------
INSERT INTO MyTable3
([RNO]
,[UserID]
,[REGNO]
,[StartDate]
,[EndDate]
,[Odometer]
,[EndOdometer])
SELECT
ROW_NUMBER() OVER(PARTITION BY EndOdometer ORDER BY StartDate) AS 'RNO',
UserID,
REGNO,
StartDate,
EndDate,
Odometer,
EndOdometer
FROM MyTable2
order by StartDate
------------------------------------------------------------
/*
Finally, this query returns all rows except where RNO (rownumber) = 2,
So, the 2nd consecutive instance where Event = 1, is excluded.
Refer to:
select 12, 'YCA100', '26-03-2010 10:17', '26-03-2010 10:27', 194820, 194829 union all -- Event 1/1
*/
select * from MyTable3
WHERE RNO <> 2
------------------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

clive thank you for making this so easy....the table definitions and data helps enourmously!

you rock!

here's how i'd "ignore" the double events in a row; i'm using row number and a partion on odometer and event.

see the first query will return 1 or 2 for the rw field int he test data.

using that as a subselect, and selecting only where rw=1 gets you the filtered data you were after:

select row_number() OVER (PARTITION BY odometer,event order by odometer) AS rw ,* from #MyTable order by odometer

select * from (

select row_number() OVER (PARTITION BY odometer,event order by odometer) AS rw ,* from #MyTable

)

where rw = 1

order by odometer

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

Several questions:

How do you calculate the duration?

Example:

If you exclude the second occurance of event 1 for Odometer 194780 (Date = 2010-03-26 08:32:00.000), how do you get 32minutes (I'd expect 35min)?

Example 2:

Where do you get the 2minutes for "194780 - 194780 = 0 km (2 minutes)" from? Based on your sample data it would be a negative value.

What is the exact reason to skip 194820? Is it because there is only one row or because there is no Event=0?

Why is 194873 skipped but not 194880?

I'm thinking of either using a few ROW_NUMBER() statements or a self join to a reduced-to-valid-rows table... Not sure yet...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's the second part of your calcualtion as well, i think:

;with myCTE as (

select row_number() over (order by odometer,event) as comprow,* from

(

select row_number() OVER (PARTITION BY odometer,event order by odometer) AS rw ,* from #MyTable)x where rw = 1

)

select

datediff(ss,t1.[date],t2.[date]) / 60 as [minutes],t2.odometer - t1.odometer as distance,t2.odometer,t1.odometer

from mycte t2

left outer join mycte t1 on t1. comprow = t2.comprow -1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ravi4work SSC Veteran Points: 201 More actions · Answer 4

The Following will exclude the undesired rows.

[highlight=""];WITH cte

AS

(

SELECT row_number() OVER(PARTITION BY GETDATE() ORDER BY date) AS row , * FROM #MyTable

)

SELECT * FROM cte A

WHERE isnull(( A.event + (SELECT EVENT FROM cte B WHERE (A.row - 1)= B.row)),1) = 1[/highlight]

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 5

Thanks ravi4work. Your method worked excellent in the one type of stored procedure. In the other type of stored procedure my method worked excellent. Thanks again.