Query help..

Question

Query help..

sql_learner29

SSCrazy Eights

Points: 9107
More actions
July 12, 2010 at 10:11 am

#223231

Hi all
I have to form a query, need some help on that,Here is the case....
From a table that has an ID, FromDate , ToDate, I have to find all the records where From date lies in between another records From and to date range for that Id....
Here is the sample code:
CREATE TABLE [test2](
[id] [nvarchar](14) NULL,
[fromdt] [datetime] NULL,
[todt] [datetime] NULL
)
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '500', 'Mar 25 2010 ', 'Apr 26 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '500', 'Apr 25 2010 ', 'May 20 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '500', 'Jan 9 2010 ', 'Mar 20 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '390', 'Mar 4 2010 ', 'Apr 21 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '500', 'Jan 8 2010 ', 'Feb 10 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '496', 'Jan 19 2010 ', 'Feb 16 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '450', 'Jan 29 2010 ', 'Mar 10 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '496', 'Jan 18 2010 ', 'Feb 22 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '390', 'Feb 18 2010 ', 'Mar 28 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '450', 'Jan 30 2010 ', 'Feb 10 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '496', 'Jan 3 2010 ', 'Feb 26 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '550', 'Jan 12 2010 ', 'Feb 10 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '900', 'Jan 17 2010 ', 'Feb 3 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '160', 'Mar 30 2010 ', 'Apr 28 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '172', 'Feb 9 2010 ', 'Mar 28 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '522', 'Jan 10 2010 ', 'Feb 12 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '660', 'Feb 12 2010 ', 'Mar 3 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '160', 'Feb 2 2010 ', 'Mar 13 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '172', 'Feb 2 2010 ', 'Mar 3 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '496', 'Feb 12 2010 ', 'Mar 13 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '522', 'Feb 12 2010 ', 'Mar 13 2010 ')
INSERT INTO test2 ( id,fromdt,todt) VALUES ( '850', 'Feb 20 2010 ', 'Apr 5 2010 ')
select * from test2 order by id,fromdt
Result for this one should show
172Feb 9 2010 Mar 28 2010
390Mar 4 2010 Apr 21 2010
450Jan 30 2010 Feb 10 2010
496Feb 12 2010 Mar 13 2010
496Jan 18 2010 Feb 22 2010
496Jan 19 2010 Feb 16 2010
500Apr 25 2010 May 20 2010
500Jan 9 2010 Mar 20 2010
as for all these ID's they are coming in other From-To date range...
Let me know if the Requirement is not clear..
thanks
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 13 posts - 1 through 12 (of 12 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 1

It's not quite clear what you mean to me. You want to compare every record to every other record? Or given a particular record, find other records who have a FROM date inside the range for that record?

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

Thanks for quick response,To make it clear Let me put it like this,

Que:

Find all the rows where Fromdate lies between any other date range for that ID

there can be more than one row for an ID with diffrent date range,

The Id is key column to compare I have to only check from FROMDT column, is there any from date value that lies in the middle of another to and from date value for that Id...

Like

Id from TO

500 5-apr 6 may

500 10 apr 21 may

here fromdate=10 apr lies between previous records from -to range...so i need this row..

Exception: Todate may be equal to From date( I dont want to consider that row )

hope its clear..

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 3

Is this some type of test question? We are not here to do your homework/exam work for you.

I'm still not clear what the starting criteria is. If I am searching for ID 172, am I going to return a dozen rows that might have included the FROM date there?

What you are writing seems to jump around and it's not clear to me. If you're repeating requirements that are on a test or exam, then you should be trying to determine the WHERE clause you need yourself.

If this is for work, then go back and find the business people and get clarification. Are you returning multiple rows for each ID, or a set of rows based on starting with a single ID.

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 4

first of all, Its not that easy as it appears ....so its not a homework...

the input for every Id will have not more than 10 rows and only exception rows will show such anomaly...I will certainly have to go through all the rows for that Id to determine if it has from date from any other date range...

for ID 172

1722010-02-02 00:00:00.0002010-03-03 00:00:00.000

1722010-02-09 00:00:00.0002010-03-28 00:00:00.000

only one row will be returned i.e

1722010-02-09 00:00:00.0002010-03-28 00:00:00.000

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

LutzM SSC Guru Points: 107049 More actions · Answer 5

Here's the approach I would use:

SELECT

t1.id,

t1.fromdt,

t1.todt

FROM [test2] t1

WHERE EXISTS

( SELECT 1

FROM [test2] t2

WHERE t1.id = t2.id

AND t1.fromdt <> t2.fromdt

AND t2.fromdt < t1.fromdt

AND t2.todt > t1.fromdt

)

ORDER BY id

I'm not sure if it'll cover all possible scenarios but at least it does return the expected rows.

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]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 6

172 2010-02-02 00:00:00.000 2010-03-03 00:00:00.000
172 2010-02-09 00:00:00.000 2010-03-28 00:00:00.000
only one row will be returned i.e
172 2010-02-09 00:00:00.000 2010-03-28 00:00:00.000

How does the code "know" which row(s) is(are) the "exception" rows and which is the "non-exception" row? IOW, they overlap each other, so how do I know which is the "bad" one(s)?

Scott Pletcher, SQL Server MVP 2008-2010

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 7

Thank you so much!!!!!!

This is exactly what I wanted..the results are PERFECT!!!!!!!!

I really don't know how this is working but your code is fetching( in seconds :-)) what I wanted to achieve...

Thanks again...........

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

LutzM SSC Guru Points: 107049 More actions · Answer 8

priya__ (7/12/2010)
Thank you so much!!!!!!
This is exactly what I wanted..the results are PERFECT!!!!!!!!
I really don't know how this is working but your code is fetching( in seconds :-)) what I wanted to achieve...
Thanks again...........

I strongly recommend you take the time to understand the code. One option would be to change it into a INNER JOIN query. Then compare the execution plans and the performance of both queries and decide, which one you want to use in your environment.

To simply copy and paste a code snippet from an online forum into your production environment isn't really something you should consider unless you completely understand how it works. Just imagine you need to change that code in some state of emergency. What will you do?

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]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 9

Ohh yeah, I have to change it a little, yesterday it was time to leave so could not understand the code, but got it now...

Thanks again

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 10

LutzM (7/12/2010)
Here's the approach I would use:
SELECT
t1.id,
t1.fromdt,
t1.todt
FROM [test2] t1
WHERE EXISTS
( SELECT 1
FROM [test2] t2
WHERE t1.id = t2.id
AND t1.fromdt <> t2.fromdt
AND t2.fromdt < t1.fromdt
AND t2.todt > t1.fromdt
)
ORDER BY id
I'm not sure if it'll cover all possible scenarios but at least it does return the expected rows.

Classic, Lutz... Classic. I AM going to have to hide my magic decoder ring. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 11

Jeff Moden (7/13/2010)
...
Classic, Lutz... Classic. I AM going to have to hide my magic decoder ring. 😉

Thanx, Jeff! :blush:

Btw: What feature of your magic decoder ring are you going to hide? And where do you have that ring anyway? I actually doubt it's on your finger since it's gotta be really heavy based on the endless features you frequently pull out of it... 😀

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]

MMartin1 One Orange Chip Points: 27795 More actions · Answer 12

So to put it plainly you are merely joining the table to itself where the from date of a given row is between the from and start date of another row. This will give a one to many relationship which in essence repeats the input row for every row it gets joined with in the t2 table.

----------------------------------------------------