Find the first negative value if exist and then do a substraction between dates

Question

I have the next table

Product	Plant	Store	Week	Date	Stock
123456	A123	Z12	0	2001-01-01	-24
123456	A123	Z12	1	2001-01-08	-60
123456	A123	Z12	2	2001-01-16	-60
789123	B345	123	0	2001-01-01	10
789123	B345	123	1	2001-01-08	-20
789123	B345	123	2	2001-01-16	-30
013579	C678	1A3	0	2001-01-01	10
013579	C678	1A3	1	2001-01-08	20
013579	C678	1A3	2	2001-01-16	30

So I would like to get the first negative value then get its date and do a substract between the first date (which is in week=0) and the date for the first negative value, but if is not negative values then get the last positive value and do a substract between the first date (which is in the colum Week and value=0) and the last date (which is in the colum Week and value=2). So at the end I'll get a table like this:

Product	Plant	Store	DaysDiff
123456	A123	Z12	0
789123	B345	123	7
013579	C678	1A3	15

The result in the column DaysDiff is due to (2001-01-01)-(2001-01-01)=0,(2001-01-01)-(2001-01-08)=7 and (2001-01-01)-(2001-01-16)=15.

The query I wrote was this:

SELECT
    Product,
    Plant,
    Store,
    CASE
        WHEN MIN(CASE WHEN Stock < 0 THEN Date END) IS NOT NULL THEN
            DATEDIFF(WEEK, 
                MIN(CASE WHEN Stock < 0 THEN Date END),
                MIN(CASE WHEN Week = 0 THEN Date END)
            )
        ELSE
            DATEDIFF(WEEK, 
                MAX(CASE WHEN Week = 0 THEN Date END),
                MIN(Date)
            )
    END AS DaysDiff
FROM Table
GROUP BY
    Product,
    Plant,
    Store
ORDER BY Product, Plant, Store;

But I only get one result.

Product	Plant	Store	DaysDiff
123456	A123	Z12	0

Any help?

Answer 1

Simpler does it:

SELECT product, plant, store
     , COALESCE(min(date) FILTER (WHERE stock < 0), max(date)) - min(date) AS days_diff
FROM   tbl
GROUP  BY 1,2,3
ORDER  BY 1,2,3;

fiddle

There is no DATEDIFF() function in Postgres. (You may be thinking of MySQL?)
To get the difference between dates, just subtract. See:

• Calculating the number of days between two dates in PostgreSQL

About the aggregate FILTER clause:

• Aggregate columns with additional (distinct) filters

COALESCE to finalize.

About the (optional!) short syntax with ordinal numbers:

• Select first row in each GROUP BY group?