T-SQL QUERY设置之间的日期

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.

我正在创建一个报告,我对这个报告不合逻辑。我加入了四个表,但为了简单起见,我说我有两个表,表1和表2。

Table1 has these columns: WorkDateTime, ColTest1, ColTest2, UserName

Table1包含以下列:WorkDateTime,ColTest1,ColTest2,UserName

Table2 has these columns: UserName, INdateTIme, OUTdateTime

Table2包含以下列:UserName,INdateTIme,OUTdateTime

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.

我需要实现的目的:通过Username连接这两个表,WorkDateTime必须介于(或等于)INdateTIme和OUTdateTime之间,WorkDateTime基于用户的输入。

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.

我的表有一千条记录。但请参阅下面的示例数据。我上面的查询返回0数据。

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.

我的查询应该只返回Table1中的前五个数据,因为在表2中,用户名EmpNo1仅在2月1日上午5:40到下午2:00之间工作。表1中的最后两个数据超出下午2:00。

2 个解决方案

#1

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

所以我将我的查询修改为这个(根据@uzi建议)并且它有效:

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.

我只是添加了CONVERT()函数。我很困惑,我的表格格式相同但不会运行。我想,为了安全起见,我应该每次都包含CONVERT()函数。

#2

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.

这在大型数据集上表现不佳。

#1