CODEWITHSUNDEEP
sqlsql-serverssrs-2012
Marco Onnis
Marco Onnis

Reputation: 25

t-SQL greater/less than more than one value

I'm facing a strange problem:

I have a simple table structured like below:

tbl1

Username | DateOfSell
  Jack     2021/04/04
  Joe      2021/04/14
  Jack     2021/05/20
  Joe      2021/04/11
  Jack     2021/05/01

And Anothe one with informations of the employee, number, hire date and fire date

tbl2

Username | Phone | DateOfStart | DateOfEnd
  Jack     02541   2010/01/01   2021/05/21
   Joe     08522   2012/06/01       NULL

What I need is to create a query to select the employee only if it is still working at some date. for example in this case:

SELECT * 
FROM tbl1 JOIN tbl2 (..) 
WHERE tbl1.DateOfSell < tbl2.DateOfEnd

that would be ok, but I need (for SSRS purposes) to be able to check more than one date.

IF in SSRS I have a parameter with the actual day and the previos day and also the day of the past week, how can I do?

SO: today() = 2021/05/26 previous week: 2021/05/19

SELECT * 
FROM tbl1 JOIN tbl2 (..) 
WHERE  '2021/05/26' < tbl2.DateOfEnd

I want no results because now this employee is not working anymore.

SELECT * 
FROM tbl1 JOIN tbl2 (..) 
WHERE  '2021/05/19' < tbl2.DateOfEnd

in this case I need results because at tat date, the employee was still working.

what if in the parameter report I put both (or more) values?

clearly I have an error An expression of non-boolean type specified in a context where a condition is expected, near ','

Here is a way to resolve this problem?

thank you!

Upvotes: 0

Views: 48

Answers (1)

James
James

Reputation: 3015

Maybe this query could do the trick

declare @dtparam date = '2021-01-10'

select *
from tbl1 t1
  join tbl2 t2
    on t1.Username = t2.Username
where t2.dateOfStart <= @dtparam
  and (
    t2.DateOfEnd >= @dtparam or
    t2.DateOfEnd is null
  )

Upvotes: 0

Related Questions