July 12, 2010 at 10:11 am
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
Thanks [/font]
July 12, 2010 at 10:19 am
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?
July 12, 2010 at 10:27 am
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..
Thanks [/font]
July 12, 2010 at 10:36 am
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.
July 12, 2010 at 10:45 am
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
Thanks [/font]
July 12, 2010 at 2:07 pm
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.
July 12, 2010 at 2:15 pm
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
July 12, 2010 at 3:25 pm
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...........
Thanks [/font]
July 12, 2010 at 3:39 pm
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?
July 13, 2010 at 8:12 am
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
Thanks [/font]
July 13, 2010 at 11:57 pm
LutzM (7/12/2010)
Here's the approach I would use:
SELECTt1.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
Change is inevitable... Change for the better is not.
July 14, 2010 at 3:44 pm
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... 😀
July 21, 2010 at 12:12 am
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.
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply