t.brown 89142 (11/15/2012)

---

Hence all the ROW_NUMBER() functions in my efforts so far.
But it doesn't matter if two #Table2 rows have the same Date and Text - as the outcome is the same whichever is chosen as the match.

t.brown 89142 (11/16/2012)

---

I had assumed (incorrectly as it turns out) that this problem would have a standard SQL solution as its a classic two file problem read file A, go down file B looking for a match. Save position in file B, read next from A, continue down file B from saved position. I've done this kind of thing in procedural languages many times.

dwain.c (11/15/2012)

---

I'm not getting exactly the desired results you posted but maybe this will give you a hint.

;WITH CTE AS (
    SELECT TOP 1 ID, TEXT1, aDate, TEXT2
    FROM #Table1 
    JOIN #Table2 ON TEXT1 = TEXT2
    UNION ALL
    SELECT a.ID, a.TEXT1, b.aDate, b.TEXT2
    FROM #Table1 a 
    JOIN CTE c ON a.ID = c.ID + 1
    JOIN #Table2 b ON a.TEXT1 = b.TEXT2 AND b.aDate >= c.aDate
    )
SELECT TEXT1, aDate, TEXT2
FROM (
    SELECT TEXT1, aDate, TEXT2
        ,n=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY aDate)
    FROM CTE) a
WHERE n = 1
OPTION (MAXRECURSION 0)

Where the code says AND b.aDate >= c.aDate, try making that AND b.aDate > c.aDate and run it both ways. I think this is close and that the answer may lie in reinterpreting the expected results from the sample data.

Unfortunately this is probably going to be quite slow depending on the number of rows in your actual data.

thava (11/19/2012)

---

just by curiosity the last row of the result is not matched so i think adding an identity to the #table2 and using that will solve this issue may i right or we will need to work more

Actually #Table2 has the date, but as its only to the minute, there are duplicates - and the order of TEXT2 does not help - see the last few lines of the sample data - and my added column RowNum has duplicates.

I had assumed (incorrectly as it turns out) that this problem would have a standard SQL solution as its a classic two file problem read file A, go down file B looking for a match. Save position in file B, read next from A, continue down file B from saved position. I've done this kind of thing in procedural languages many times.

CELKO (11/19/2012)

---

[quote]
...
Oh, IDENTITY cannot be a valid key for table either.
...

IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table.

CELKO (11/19/2012)

---

Duplicates? then it is not a table by defintion because it cannot have a key. Oh, IDENTITY cannot be a valid key for table either. But it sure makes you magetnic tape file programmers feel better since it lets you find physical insertion order.



EXACTLY! Your mindset is still stuck writing sequential mag tape code in a set-oriented declarative world. A read/write head position, local variables to hold changing data, etc. You are the flat earth kid in a Geography class !

You need to get a book on RDBMS and read before you try to program again. What you got here is a pile of stinking kludges that only trap you into that old mindset.

Why don't you take a day or two and try to do this with set-oriented programming
and post it? The DDL should have keys, constraints, Declarative Referential Integrity, and good data types. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

t.brown 89142 (11/20/2012)

---

Thank you for your assessment ...
Now go back and hide under your bridge.