CODEWITHSUNDEEP
sql-servergroup-by
Peter Connolly
Peter Connolly

Reputation: 131

SQL Most recent Record with specific criteria

I have a table that i use to figure out what sites/shops are due a visit, based on the date of the last visit to it.

There's a quick process to get your head around to understand one of the requirements;

A visit is documented by the value: Task Type = CASH. A review of a visit is shown as: Task Type = SALE.

What i need is:

The most recent row in the table related to a Asset ID for either the most recent SALE or CASH line. (Sometimes CASH lines do not occur, but a SALE line is manually populated on the table instead).

I've included all the columns i would like visible on the final table.

Here's a mock up of the data - i'm still learning how to use SQLFiddle - and all the links from this site i take to it end up in an error! :(

TASK_TYPE AVERAGE_REVENUE ASSET_ID  POSTING_DATE
SALE                   25 A001      01/05/2017
CASH                   20 A002      27/04/2017
SALE                   20 A003      25/04/2017
TESTING                 0 A002      28/04/2017
REPAIR                  0 A002      27/04/2017
SALE                   22 A004      30/04/2017
CASH                   25 A001      22/04/2017
CASH                   22 A004      01/05/2017

Here's what i would be expecting from the above example:

TASK_TYPE   AVERAGE_REVENUE ASSET_ID    POSTING_DATE
    SALE    25              A001        01/05/2017
    CASH    20              A002        27/04/2017
    SALE    20              A003        25/04/2017
    CASH    22              A004        01/05/2017

Any examples ive found on stackoverflow seem to solve part of the problem, but not all of it, and my knowledge isnt strong enough to fill in the gaps.

Any help is much appreciated.

Upvotes: 0

Views: 61

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

In SQL server, you can team up row_number with top 1 with ties to find latest rows:

select top 1
with ties *
from your_table
where task_type in ('SALE', 'CASH')
order by row_number() over (
        partition by asset_id order by posting_date desc
        )

Demo

Upvotes: 1

Robert Kock
Robert Kock

Reputation: 6008

You might try the following:

SELECT   task_type,
         average_revenue,
         asset_id,
         posting_date
FROM     my_table first
WHERE    task_type IN ('SALE', 'CASH')
  AND    posting_date = (SELECT MAX(posting_date)
                         FROM   my_table second
                         WHERE  second.task_type = first.task_type
                           AND  second.asset_id = first.asset_id)
ORDER BY asset_id;

Upvotes: 1

Martin Schneider
Martin Schneider

Reputation: 3268

One solution is a LEFT JOIN on the table itself. What this query does is join all relevant rows with all other relevant rows (same ASSET_ID and type cash/sale) if the date of the latter is newer. Then we only retrieve those rows which do not have a row which is newer.

SELECT
  A.*
FROM
  mytable A LEFT JOIN mytable B ON (A.ASSET_ID = B.ASSET_ID AND 
                                    B.TASK_TYPE IN ('SALE','CASH') AND 
                                    A.POSTING_DATE < B.POSTING_DATE)
WHERE
  A.TASK_TYPE IN ('SALE','CASH') AND
  B.ASSET_ID IS NULL

Upvotes: 1

Related Questions