February 8, 2011 at 6:51 pm
I need some help compiling a SQL statement for a view. I’ve never tried something like this, not an expert SQL person, so let’s see how we go.
I have two tables which I will just dumb down for the purpose of this exercise.
Table 1 contains an ID field and a Date1 field.
Table 2 contains an ID field and a Date2 field.
I need to build a view that will show me Table1.ID, Table1.Date1, Table2.Date2 based on matching ID.
Simple, yes.
Now the challenge (for me anyway).
Consider the table contained the following data (and by the way the date format is DD/MM/YYYY)
Table1.ID, Table1.Date1
1, 09/03/2009
Table2.ID, Table2.Date2
1, 16/10/2006
1, 20/08/2007
1, 01/01/2008
1, 01/01/2009
1, 21/09/2009
1, 01/01/2010
I can get the view to show me any records where the Table1.Date1 is less than Table2.Date2 with the following;
Select Table1.ID, Table1.Date1, Table2.Date2 From Table1 Inner Join
Table2 On Table1.ID = Table2.ID And Table1.Date1 > Table2.Date2
This will return the following results;
Table1.ID, Table1.Date1, Table2.Date2
1, 09/03/2009, 16/10/2006
1, 09/03/2009, 20/08/2007
1, 09/03/2009, 01/01/2008
1, 09/03/2009, 01/01/2009
Can I limit the result so that it only returns the record where the value of Date2 is closest to the value of Date1 but still less than Date1?
e.g. the following;
Table1.ID, Table1.Date1, Table2.Date2
1, 09/03/2009, 01/01/2009
Cheers
Doug
February 8, 2011 at 7:14 pm
A good explanation of the problem, and though the sample data is not readily consumable, it was not tedious to make it readily-consumable.
Now the business:
I have build the sample data into readily consumable format:
SET DATEFORMAT DMY
DECLARE @Table1 TABLE
(
ID1 INT,
Date1 DATE
)
INSERT INTO @Table1
SELECT 1, '09-03-2009'
DECLARE @Table2 TABLE
(
ID2 INT,
Date2 DATE
)
INSERT INTO @Table2
SELECT 1, '16-10-2006' UNION ALL
SELECT 1, '20-08-2007' UNION ALL
SELECT 1, '01-01-2008' UNION ALL
SELECT 1, '01-01-2009' UNION ALL
SELECT 1, '21-09-2009' UNION ALL
SELECT 1, '01-01-2010'
Now, there are at least 3 ways in achieving this.Lets look at the first way :
1. Using Correlated Sub Query
-- Method 1 : Using Correlated Sub Query
SELECT T1.ID1 , T1.Date1 ,
( SELECT TOP 1 T2.Date2
FROM @Table2 T2
WHERE T2.ID2 = T1.ID1 AND
T2.Date2 < T1.Date1
ORDER BY T2.Date2 DESC
) Nearest_Lesser_Date
FROM @Table1 T1
2. Using ROW_NUMBER()
-- Method 2: ROW_NUMBER()
; WITH CTE ( ID1, DATE1 , DATE2 , RND ) AS
(
SELECT T1.ID1 , T1.Date1 , T2.Date2 ,
RND = ROW_NUMBER() OVER(PARTITION BY T2.ID2 ORDER BY T2.Date2 DESC )
FROM @Table1 T1
JOIN @Table2 T2
ON T2.ID2 = T1.ID1 AND
T2.Date2 < T1.Date1
)
SELECT ID1, DATE1 , DATE2 AS Nearest_Lesser_Date
FROM CTE
WHERE RND = 1
3. Using CROSS APPLY
-- Method 3 : CROSS APPLY
SELECT T1.ID1 , T1.Date1 , CrsApp.Date2 AS Nearest_Lesser_Date
FROM @Table1 T1
CROSS APPLY
(
SELECT TOP 1 T2.Date2
FROM @Table2 T2
WHERE T2.ID2 = T1.ID1 AND
T2.Date2 < T1.Date1
ORDER BY T2.Date2 DESC
) CrsApp
Now, i have had a look at the execution plans, and see that correlated sub queries perform lesser operations that the ROW_NUMBER version. Both CROSS APPLY and correlated sub queries produce identical execution plans. But a sample data of 6 rows wont be worthy enough to judge. So i leave the choice to you to choose between them!
Hope this helps 🙂
{Edit : Added CROSS APPLY as an aliter}
February 8, 2011 at 7:16 pm
There can be a few ways. you can try CTEs and RANK like below
with cte(id, date1, date2, rank) as
(
select t1.id, t1.date1, t2.date2
, rank() over
(PARTITION BY t1.id ORDER BY t1.date1, t2.date2 desc) AS 'RANK'
from table1 t1
join table2 t2 on t1.id = t2.id
and T1.Date1 > T2.Date2
)
select * from cte where rank = 1
OR you can use nested queries, but if there are a large number of records, the performance would not be good.
EDIT: Similar solution provided by ColdCoffee just before me. Guess we were working on it at the same time 🙂
February 8, 2011 at 7:25 pm
pankushmehta (2/8/2011)
EDIT: Similar solution provided by ColdCoffee just before me. Guess we were working on it at the same time 🙂
You bet 🙂 :w00t:
February 8, 2011 at 7:30 pm
Thanks Cold Coffee, your Sub Query method was the way I was leaning towards, just couldnt work out the syntax.
Cheers.
February 8, 2011 at 7:36 pm
doug.milostic (2/8/2011)
Thanks Cold Coffee, your Sub Query method was the way I was leaning towards, just couldnt work out the syntax.Cheers.
You're welcome Doug!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply