CODEWITHSUNDEEP
sqlpysparkapache-spark-sql
Neli
Neli

Reputation: 741

How to take the smallest date of a group?

I have a dataset which looks like that:

| id | status | open_date | name |
| 8 | active | 2019-3-2 | blab |
| 8 | active | 2019-3-8 | blub |
| 8 | inactive | 2019-3-9 | hans |
| 8 | active | 2019-3-10 | ana |
| 9 | active | 2019-3-4 | mars |

I want to achieve the following:

| id | status | open_date | name | status_change_date |
| 8 | active | 2019-3-2 | blab | 2019-3-2
| 8 | active | 2019-3-8 | blub | 2019-3-2
| 8 | inactive | 2019-3-9 | Hans | 2019-3-9
| 8 | active | 2019-3-10 | ana | 2019-3-10
| 9 | active | 2019-3-4 | mars | 2019-3-4

for each id I like to calculate when the status has last changed

I already tried with groupBy, but the problem is I only want to group by the rows with Active and Inactive which are next to each other. If there is an INACTIVE between ACTIVE I like to make a new group for the new ACTIVE.

Someone has an idea to solve that?

Upvotes: 0

Views: 65

Answers (3)

GMB
GMB

Reputation: 222432

Here is a pure SQL solution that uses window functions. This works by generating a partition that contains consecutive records that have the same id and status.

SELECT
    id,
    status,
    open_date,
    name,
    MIN(open_date) OVER(PARTITION BY id, rn1 - rn2 ORDER BY open_date) status_change_date
FROM (
    SELECT 
        t.*, 
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY open_date) rn1,
        ROW_NUMBER() OVER(PARTITION BY id, status ORDER BY open_date) rn2
    FROM mytable t
) x
ORDER BY id, open_date

Demo on DB Fiddle:

| id  | status   | open_date  | name | status_change_date |
| --- | -------- | ---------- | ---- | ------------------ |
| 8   | active   | 2019-03-02 | blab | 2019-03-02         |
| 8   | active   | 2019-03-08 | blub | 2019-03-02         |
| 8   | inactive | 2019-03-09 | hans | 2019-03-09         |
| 8   | active   | 2019-03-10 | ana  | 2019-03-10         |
| 9   | active   | 2019-03-04 | mars | 2019-03-04         |

Upvotes: 1

Micha
Micha

Reputation: 924

You can try this:

var movies = [
    {title: 'The Godfather', rating: 9.2, release: '24 March 1972'},
    {title: 'The Godfather: Part II', rating: 9.0, release: '20 December 1972'},
    {title: 'The Shawshank Redemption', rating: 9.3, release: '14 October 1994'},
];

movies.sort(function(a, b) {
    var dateA = new Date(a.release), dateB = new Date(b.release);
    return dateA - dateB;
});

This sortby works because js lets you compare arithmetic on date objects, which are automatically converted to numeric representations first.

In SQL use MIN function:

ORDER Id OrderDate OrderNumber CustomerId TotalAmount

SELECT MIN(OrderDate) FROM [Order] WHERE YEAR(OrderDate) = 2013

Upvotes: 0

Marrds
Marrds

Reputation: 65

Thats the answer on How to take the smallest date of a group?

let minDate = new Date('0001-01-01T00:00:00Z');

dataset.forEach(x => if( x.date > this.minDate) { this.minDate = x.date } ) 

console.log(this.minDate);

Upvotes: 0

Related Questions