CODEWITHSUNDEEP
sql-serversql-server-2008
itsmePJ
itsmePJ

Reputation: 43

T-SQL QUERY setting the date between

I'm creating a report and I'm out of logic for this one. I joined four tables, but for simplicity let's say I have two tables, Table1 and Table 2.

What I need to achieve: join these two tables by Username and the WorkDateTime must be between (or equal) to the INdateTIme and OUTdateTime and the WorkDateTime is based on the user's input.

Something like this example:

SELECT * 
FROM Table1
INNER JOIN Table2 ON Table1.UserName = Table2.UserName
                  AND Table1.WorkDateTime BETWEEN Table2.INdateTIme AND Table2.OUTdateTime
WHERE Table1.WorkDateTime BETWEEN CONVERT(date, '2018-02-01 00:00:00.000') AND CONVERT(date, '2018-02-07 00:00:00.000')

My tables have a thousand records. But please see below sample data. My query above returns 0 data.

enter image description here

My query should only return the first five data from Table1 since in Table2 Username EmpNo1 only works between 5:40am to 2:00pm on Feb 1st. The last two data from Table1 is beyond 2:00pm.

Upvotes: 0

Views: 87

Answers (2)

itsmePJ
itsmePJ

Reputation: 43

So I revised my query to this one (as per @uzi advised) and it works:

SELECT * 
FROM Table1
INNER JOIN Table2 ON Table1.UserName = Table2.UserName
                  AND CONVERT(datetime, Table1.WorkDateTime) BETWEEN CONVERT(datetime, Table2.INdateTIme) AND  CONVERT(datetime, Table2.OUTdateTime)
WHERE CONVERT(date,Table1.WorkDateTime) BETWEEN CONVERT(date, '2018-02-01 00:00:00.000') AND CONVERT(date, '2018-02-07 00:00:00.000')

I just add the CONVERT() function. I'm confused, my tables have the same format but it won't run. I guess, just to be safe, I should include the CONVERT() function everytime.

Upvotes: 1

LJ01
LJ01

Reputation: 589

A cross apply would allow you to filter 

where table1.date > table2.indate and table1.date < table2.outdate.

This isn't going to perform well on a large dataset.

Upvotes: 0

Related Questions