Get last two IDs from rows for last two days

Question

I have a table that looks like this:

    +------------+------------+--------+----------+--------------+
    | terminalid |    date    | toolid | toolname | workcenterid |
    +------------+------------+--------+----------+--------------+
    |         17 | 2015-12-17 |    132 | RE8      |            7 |
    |         17 | 2015-12-17 |    140 | RE77     |           10 |
    |         17 | 2015-12-17 |    141 | RE0      |            1 |
    |         17 | 2015-12-17 |    153 | RTR      |           13 |
    |         18 | 2015-12-18 |     29 | C47      |           12 |
    |         18 | 2015-12-18 |     53 | C45      |           10 |
    |         18 | 2015-12-18 |     58 | TXR      |            9 |
    |         18 | 2015-12-18 |     61 | BMS      |            6 |
    |         19 | 2015-12-21 |    128 | REV      |            3 |
    |         19 | 2015-12-21 |    135 | REZS     |           12 |
    |         19 | 2015-12-21 |    154 | REP      |            1 |
    |         20 | 2015-12-22 |     21 | RR14     |            9 |
    |         20 | 2015-12-22 |     69 | REX      |            1 |
    |         20 | 2015-12-22 |     71 | REA      |            4 |
    |         20 | 2015-12-22 |     72 | REW      |           11 |
    |         20 | 2015-12-22 |     91 | RER      |            1 |
    +------------+------------+--------+----------+--------------+

I want to get the last 2 terminalids from the rows where the date is today or yesterday.

For example, if today was 2015-12-18, the query should return me this:

    +------------+------------+--------+----------+--------------+
    | terminalid |    date    | toolid | toolname | workcenterid |
    +------------+------------+--------+----------+--------------+
    |         17 | 2015-12-17 |    132 | RE8      |            7 |
    |         17 | 2015-12-17 |    140 | RE77     |           10 |
    |         17 | 2015-12-17 |    141 | RE0      |            1 |
    |         17 | 2015-12-17 |    153 | RTR      |           13 |
    |         18 | 2015-12-18 |     29 | C47      |           12 |
    |         18 | 2015-12-18 |     53 | C45      |           10 |
    |         18 | 2015-12-18 |     58 | TXR      |            9 |
    |         18 | 2015-12-18 |     61 | BMS      |            6 |
    +------------+------------+--------+----------+--------------+

If today was 2015-12-19, then only the terminalid=18 rows should be returned. And if today was, for instance, 2015-12-20, no rows should be returned, because there is no data either for today or for yesterday.

How can I achieve that?

I'm using SQL Server.

Answer 1

You can use the rank function to get the last two terminalid's:

select terminalid, date, toolid, toolname, workcenterid
from (
    select terminalid, date, toolid, toolname, workcenterid
         , dense_rank() over ( order by terminalid desc ) as rnk
    from T
    where date <= now() -- this will be dependent of your DBMS
) as X
where rnk <= 2;

Note that date is a reserved word so it is wise to name the column otherwise. Beside that it is a date of some kind (hard to tell from the question), say transaction_date in lack of a better understanding of your domain.

Answer 2

I'm assuming you just want to get any rows that match either today, or yesterday...?

SELECT 
terminalid, date, toolid, toolname, workcenterid
FROM 
TableName
WHERE 
convert(VARCHAR(8), date, 112) >= convert(VARCHAR(8), getdate(), 112) - 1

I am also assuming your [date] field is of the type datetime.

Answer 3

Below query will give you the results as desired

   -- create table 

 CREATE TABLE dbo.terminal
    ([terminalid] int, [date] datetime, [toolid] int, [toolname] varchar(4), [workcenterid] int)
;

INSERT INTO dbo.terminal
    ([terminalid], [date], [toolid], [toolname], [workcenterid])
VALUES
    (17, '2015-12-17 00:00:00', 132, 'RE8', 7),
    (17, '2015-12-17 00:00:00', 140, 'RE77', 10),
    (17, '2015-12-17 00:00:00', 141, 'RE0', 1),
    (17, '2015-12-17 00:00:00', 153, 'RTR', 13),
    (18, '2015-12-18 00:00:00', 29, 'C47', 12),
    (18, '2015-12-18 00:00:00', 53, 'C45', 10),
    (18, '2015-12-18 00:00:00', 58, 'TXR', 9),
    (18, '2015-12-18 00:00:00', 61, 'BMS', 6),
    (19, '2015-12-21 00:00:00', 128, 'REV', 3),
    (19, '2015-12-21 00:00:00', 135, 'REZS', 12),
    (19, '2015-12-21 00:00:00', 154, 'REP', 1),
    (20, '2015-12-22 00:00:00', 21, 'RR14', 9),
    (20, '2015-12-22 00:00:00', 69, 'REX', 1),
    (20, '2015-12-22 00:00:00', 71, 'REA', 4),
    (20, '2015-12-22 00:00:00', 72, 'REW', 11),
    (20, '2015-12-22 00:00:00', 91, 'RER', 1)
;

   -- query to get desired result

declare @date datetime
set @date = '2015-12-18 00:00:00.000' -- change here as per your need

select terminalid, [date], toolid, toolname, workcenterid
from dbo.terminal
where [date]  >= dateadd(d, datediff(d, 0, @date)-1, 0) and
      [date] <= dateadd(d, datediff(d, 0, @date), 0)
      order by terminalid