Need to search the name only once even if it is coming in 2 different date

Question

I have a table something like this

worker

+--------+-------+-------------+
| Worker | Lotid |    date     |
+--------+-------+-------------+
| ABC    |  1234 | 2016-11-06  |
| ABC    |  1234 | 2016-11-06  |
| ABC    |  1234 | 2016-12-06  |
| XYZ    |  1235 | 2016-12-06  |
+--------+-------+-------------+

I am running this query:

SELECT Max (worker)  AS worker, 
       lotid, 
       Min (trsdate) AS date 
FROM   worker 
WHERE  trsdate BETWEEN '2016-11-06' AND '2016-11-06'
GROUP  BY lotid 

And I am getting the output.

+-----+------+------------+
| ABC | 1234 | 2016-11-06 |
+-----+------+------------+

And when running the same query with: trsdate between '2016-12-06' and '2016-12-06'

I am getting the output as

+-----+------+------------+
| ABC | 1234 | 2016-12-06 |
+-----+------+------------+
| XYZ | 1235 | 2016-12-06 |
+-----+------+------------+

But what I want to achieve is when we count the lotid once it should not come again in different date as its happening for 1234, it is entered 2 time in 2 different date so when I search for 2016-11-06 than its correct but when I search for 2016-12-06 it is coming the same lotid again.

Answer 1

Your grouping should happen before you limit your results with your where clause. Query below works as expected:

SELECT worker
     , lotid
     , [date]
FROM (SELECT Max (worker)  AS worker, 
             lotid, 
             Min (trsdate) AS [date] 
      FROM   worker 
      GROUP  BY lotid) temp
WHERE date between '2016-12-06' and '2016-12-06'

Result:

worker  lotid   date
XYZ     1235    2016-12-06 00:00:00.000