0

TableA 

id        dtchk
1001    3/31/2018
1002    9/30/2018
1004    3/31/2019
1003    5/25/2018
1005    9/30/2019
1006    3/31/2020

TableB 

id     type  startdate  endate      name
1001    1    4/5/2018   12/31/2025  akshi
1002    100  4/27/2015  12/31/2023  polard
1004    100  4/1/2017   12/31/2019  kathi
1003    1    1/25/2018  5/25/2019   smoth
1005    1    3/21/2017  12/31/2020  sumi
1005    100  3/26/2019  12/31/2021  chechi
1006    1    2/28/2019  3/31/2021   paul
1006    100  2/28/2017  3/31/2019   rand

First we need to verify dtchk is between startdate and endate based on id if date eists between those dates then we need check the types if types 1 & 100 both fits then pick row with type 100 else pick as is if the date fits in those dates

output 

id      name
1002    polard
1004    kathi
1003    smoth
1005    chechi
1006    paul
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Quikr
  • 21
  • 4
  • 2
    You've told us your goal, so what is your question? What didn't work when you made your attempts? – Thom A May 29 '20 at 14:44
  • I tired with cte with row_number function and left join, and but not quite getting it. – Quikr May 29 '20 at 14:47
  • The first of the problem is an INNER JOIN between both tables, checking that dtchk is within the interval, and the second part of the problem is to join the first row according to your rules, take a look here : https://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row Try to solve it and tell us what you've tried and where you get stuck. – Marc Guillot May 29 '20 at 14:48
  • Put your attempt(s) in the question, @Quikr . – Thom A May 29 '20 at 14:49

1 Answers1

0

I think you want apply:

select a.*, b.*
from a cross apply
     (select top (1) with ties b.*
      from b
      where b.id = a.id and
            a.dtchk between b.startdate and b.endate and
            b.type in (1, 100)
      order by b.type desc
     ) b;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • how does it make difference when we apply outer apply, will i get non matching values from table b as null's? thats what I'm seeing but just want to understand how does outer apply works – Quikr May 30 '20 at 20:38
  • Gordo sorry, i didn't realize clicked twice, it was by mistake. answer is absolutely useful – Quikr May 30 '20 at 20:39
  • @Quikr . . . `OUTER APPLY` will keep all rows in `a`, even if there are no matches. – Gordon Linoff May 31 '20 at 02:00