January 16, 2009 at 9:09 am
This?
DECLARE@View1 TABLE
(
vw1id INT,
vw1date DATETIME
)
INSERT@View1
SELECT2, '2009-02-01' UNION ALL
SELECT3, '2009-03-01'
DECLARE@View2 TABLE
(
vw2id INT,
vw2date DATETIME
)
INSERT@View2
SELECT4, '2009-03-01' UNION ALL
SELECT6, '2009-05-01'
DECLARE@View3 TABLE
(
vw3id INT,
vw3date DATETIME
)
INSERT@View3
SELECT2, '2009-02-01' UNION ALL
SELECT7, '2009-03-01' UNION ALL
SELECT6, '2009-05-01'
SELECTvw1.vw1id,
vw1.vw1date,
vw2.vw2id,
vw2.vw2date,
vw3.vw3id,
vw3.vw3date
FROM(
SELECTvw1id,
vw1date,
ROW_NUMBER() OVER (ORDER BY vw1date) AS recID
FROM@View1
) AS vw1
FULL JOIN(
SELECTvw2id,
vw2date,
ROW_NUMBER() OVER (ORDER BY vw2date) AS recID
FROM@View2
) AS vw2 ON vw2.recID = vw1.recID
FULL JOIN(
SELECTvw3id,
vw3date,
ROW_NUMBER() OVER (ORDER BY vw3date) AS recID
FROM@View3
) AS vw3 ON vw3.recID = vw2.recID
N 56°04'39.16"
E 12°55'05.25"
January 16, 2009 at 10:32 am
Joe, to be honest, it will be difficult for anyone to help you based off of what you posted. We don't have any of those tables, or any data to go off of. It would be a giant help if you could do the leg work of posting the table schema along with sample data (in the form on INSERT statements) as well as an example recordset.
Is that query that you wrote what you came up with to solve your original post? Did you try the code that I posted? Didn't that give you what you wanted?
Here's a link to help you formulate a post so we can better help you:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 16, 2009 at 11:09 am
I agree with John, the better the example, the better the help.
From the sounds of it you are on the right track. Using Outer join instead of inner join is correct because not every date will have a cleared value so not every table/view will have a date value to join on.
for your ordering you want to use the order by clause instead of the group by. Provided a link explaining that clause.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply