0

I have 2 tables from which I need to fetch unique records. When the condition don't match I need to get the next valid value.

Table A -

RefNo Enddate StartDate
1234 20071010 20070618
1234 20051021 20050627
1234 20060608 20060220

Table B -

RefNo CompareDate FirstName LastName
1234 19680611 John Smith
1234 20060220 John Mason
1234 20070618 John Smith

Result should be -

RefNo Enddate Startdate Firstname Lastname
1234 20071010 20070618 John Smith
1234 20051021 20050627 John Mason
1234 20060608 20060220 John Mason

My current query is

SELECT RN = ROW_NUMBER() OVER (ORDER BY A.Refno), A.Refno, A.Startdate, A.Firstname, A.Lastname, A.EndDate
  FROM Table A A
  LEFT JOIN Table B b
  ON A.Refno = B.Refno
  WHERE A.Startdate BETWEEN B.StartDate AND B.Enddate

My current result is

RN Refno StartDate Firstname Lastname Enddate
1 01000184 20070618 John Smith 20071010
2 01000184 20060220 John Mason 20060608

Is it possible to get my results without stored procedure? Appreciate any assistance.

I need to fetch firstname, Lastname from one table if the start and end date is inbetween the startdate. I am getting when the condition match but I need all 3 rows with Firstname and Lastname from another row.

GMB
  • 216,147
  • 25
  • 84
  • 135
AKS
  • 21
  • 5
  • 1
    It is after my edit, @AKS . Please do take the time to check the preview pane to see what your posts look like before you submit them. Note that I had used a markdown table on your bottom dataset already, which (should have) guided you on how ti create them. – Thom A Apr 03 '23 at 14:55
  • 1
    In regards to your query, your clause in the `WHERE` should be in `ON`; you are implicitly turning your `LEFT JOIN` into an `INNER JOIN`. – Thom A Apr 03 '23 at 14:56
  • I changed it as you suggested. I still get the NULL value when my condition don't match. Please advise. "SELECT RN = ROW_NUMBER() OVER (ORDER BY A.Refno), A.Refno, A.Startdate, A.Firstname, A.Lastname, A.EndDate FROM Table A A LEFT JOIN Table B b ON A.Refno = B.Refno and A.Startdate BETWEEN B.StartDate AND B.Enddate " – AKS Apr 03 '23 at 15:15
  • Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) – philipxy Apr 24 '23 at 23:28
  • Please clarify via edits, not comments. – philipxy Apr 24 '23 at 23:30
  • Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. – philipxy Apr 24 '23 at 23:30
  • Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. – philipxy Apr 24 '23 at 23:31

1 Answers1

2

We can get the "closest" row to the reference end date with apply. Since we disregard the start date, this happily returns the "previous" match.

select row_number() over(partition by a.refNo order by a.startDate) rn,
    a.*, 
    b.firstName, b.lastName
from tablea a
outer apply (
    select top (1) b.*
    from tableb b
    where b.refNo = a.refNo and b.dateCompare <= a.dateEnd
    order by b.dateCompare desc
) b
order by rn
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Worked like a charm. Appreciate your assistance. I was not aware of the date with 'apply'. Learned something new today. – AKS Apr 05 '23 at 00:32