Get only the first row when exist a negative value else get the last positive value

Question

I have the next table

Product	Plant	Store	Text	Week	Date	Stock
123456	A123	Z12	HelloWorld	1	2001-01-01	20
123456	A123	Z12	HelloWorld	2	2001-01-08	-15
123456	A123	Z12	HelloWorld	3	2001-01-16	-20
789123	B345	123	HelloWorld1	1	2001-01-01	10
789123	B345	123	HelloWorld1	2	2001-01-08	20
789123	B345	123	HelloWorld1	3	2001-01-16	30

So I would like to get the row for the first negative value but if is not negative values then get the last positive value. Also restructured the table like this.

Product	Plant	Store	Text	Date_Start	Stock_Start	Date_Finished	Stock_Finished
123456	A123	Z12	HelloWorld	2001-01-01	20	2001-01-08	-15
789123	B345	123	HelloWorld1	2001-01-01	10	2001-01-16	30

I try multiple query's but the one with the nearest result is as follows:

SELECT Product,Plant,Store,Text,Date, Stock FROM table
WHERE (Stock)<0 
order by Product,Plant,Store,Text,Date, Stock asc

Also I tried to use FETCH FIRST 1 ROWS ONLY but I got the next error ERROR: Syntax error at or near "FIRST". The other query is as follows:

WITH added_row_number AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY Product,Plant,Store ORDER BY Date ASC) AS row_number
  FROM MM_STOCK
)
SELECT
  *
FROM added_row_number
WHERE (Stock)<0 AND row_number = 1;

But beware the data and if I add a case stament I got the error ERROR: Syntax error at or near "CASE" . Any help?

Answer 1

Some Subselects can be used, to solve this

SELECT "Product", "Plant", "Store"
  , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store" ORDER BY "Week" ASC  LIMIT 1) start_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store"  ORDER BY "Week" ASC LIMIT 1) start_stock
    , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store" ORDER BY "Week" DESC LIMIT 1) en_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store"  ORDER BY "Week" DESC LIMIT 1) end_stock
  FROM
 Table2 t1
  GROUP BY 
    "Product", "Plant", "Store"

Product	Plant	Store	start_date	start_stock	en_date	end_stock
789123	B345	123	2001-01-01	10	2001-01-16	30
123456	A123	Z12	2001-01-01	20	2001-01-16	-20

SELECT 2

fiddle

you don't need for this aggregation, i can't find a Version postgres that doesn't work

SELECT DISTINCT "Product", "Plant", "Store"
  , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store" ORDER BY "Week" ASC  LIMIT 1) start_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store"  ORDER BY "Week" ASC LIMIT 1) start_stock
    , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store" ORDER BY "Week" DESC LIMIT 1) en_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store"  ORDER BY "Week" DESC LIMIT 1) end_stock
  FROM
 Table2 t1

Product	Plant	Store	start_date	start_stock	en_date	end_stock
123456	A123	Z12	2001-01-01	20	2001-01-16	-20
789123	B345	123	2001-01-01	10	2001-01-16	30

SELECT 2

fiddle

Answer 2

I need the first stock's negative value with its date and also the first stock's value with its date in order to calculate the difference between both dates, but if it's not a negative in the stock then get me the last value with its day

You could rephrase this as "latest positive stock per product, also latest negative per product", which implies

• if there's no positive stock for a product, you get only negative stock records,
• if there's no negative stock for a product, you get only positive stock records.

As mentioned in the comment, usually the 1 per product translates into a distinct on: demo

(select distinct on (product) * from test
order by product
         ,stock<0 desc
         ,date desc)
union
(select distinct on (product) * from test
where stock>=0
order by product
         ,date desc);

product	plant	store	text	week	date	stock
123456	A123	Z12	HelloWorld	3	2001-01-16	-20
123456	A123	Z12	HelloWorld	1	2001-01-01	20
789123	B345	123	HelloWorld1	3	2001-01-16	30