3

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.

2

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.

0

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.

  • Thanks for the fast response, but please check the question again, it has been reviewed and changed... – FiN May 27 '16 at 10:23
  • By converting the date column, it is now non SARGable. ( tech-recipes.com/rx/55535/…). A full table scan will result instead of a seek against an existing index. – datagod May 27 '16 at 13:14
0

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
  • Will the downvoter please comment for a downvote ? Just downvoting does not help ! – Kin Shah May 27 '16 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.