PostgreSQL, Function LAST WHERE?

Question

Once again appears problem which I dont know to solve due to lacks in my SQL knownledge. Let me "draw" it.

DROP TABLE IF EXISTS mytry; 
CREATE TABLE IF NOT EXISTS mytry 
   (mydate timestamp, doctype text, docnum integer, inprice decimal, 
    price decimal, inqty decimal, outprice decimal, outqty decimal); 
INSERT INTO mytry (mydate, doctype, docnum, inprice, price, 
                   inqty, outprice, outqty) VALUES 
('2017-01-01 00:00:00','BegDoc',   0, 13.24, 23.00, 10,     0, 0), 
('2017-01-02 17:18:14','OutDoc',  10,     0,     0,  0, 25.00, 2), 
('2017-01-03 14:04:39','OutDoc',  16,     0,     0,  0, 25.00, 2), 
('2017-01-03 14:36:20','OutDoc',  17,     0,     0,  0, 25.00, 1), 
('2017-01-03 14:58:45','OutDoc',  18,     0,     0,  0, 25.00, 2), 
('2017-01-05 14:39:06','InDoc',   25, 27.20, 34.00,  9,     0, 0), 
('2017-01-11 14:01:33','OutDoc',  57,     0,     0,  0, 34.00, 2), 
('2017-01-13 13:05:44','OutDoc',  74,     0,     0,  0, 34.00, 2), 
('2017-01-13 14:38:40','OutDoc',  75,     0,     0,  0, 34.00, 1), 
('2017-01-16 13:43:35','OutDoc',  88,     0,     0,  0, 34.00, 1), 
('2017-01-17 13:24:10','OutDoc',  95,     0,     0,  0, 34.00, 2), 
('2017-01-18 18:24:30','InDoc',   79, 20.40, 29.60, 20,     0, 0), 
('2017-01-19 17:31:55','OutDoc2',  6,     0,     0,  0, 29.60, 2), 
('2017-01-25 10:53:10','OutDoc', 121,     0,     0,  0, 29.60, 1), 
('2017-01-26 15:17:06','OutDoc2', 13,     0,     0,  0, 29.60, 2), 
('2017-01-28 09:39:05','OutDoc', 128,     0,     0,  0, 29.60, 3), 
('2017-01-28 11:03:42','OutDoc', 138,     0,     0,  0, 29.60, 2), 
('2017-02-03 16:08:23','OutDoc2', 16,     0,     0,  0, 29.60, 2); 

In short this is one "material card" table which shows any change to specific material changes in store depending on doctype and docnum. Here are two main types of documents. Input and output type of documents. Documents have his timestamp with time whern they were created. After that we have input or output prices and input or output quantities defined in those documents.

Problem is that I have to query this table on timestamp which do not exists in table. Like this:

SELECT MAX(mydate) AS lastdate, MAX(doctype) AS lastdoctype, MAX(docnum) AS lastdocnum, 
   LAST(inprice) AS lastinprice, LAST(price) AS lastprice, SUM(inqty-outqty) AS lastqty
 FROM mytry 
WHERE mydate<'2017-01-26 14:00:00' 
LIMIT 1;

What gives result:

"2017-01-25 10:53:10";"OutDoc2";121;0;0;21

Where lastdate, lastdoctype, lastdocnum and lastqty are correct (expected) values but lastinprice and lastprice is not. Aim of this query is to get exact situation on article which was on time where query says. In showed case that would looks like:

"2017-01-25 10:53:10";"OutDoc2";121;20.40;29.60;21

That mean that query should look to columns lastinprice and lastprice last value which is bigger than 0. It would be ideal if I would have function LAST(inprice) WHERE inprice>0 but I haven't it. Or I have?

Please if someone can do for me a query which would produce expected result for lastinprice and lastprice columns based on my description of problem.

Finally, here are few time examples for query and wanted query results which I make for testing (by hand).

'Time in query               Wanted result
'----------------------------------------------------------------------------
'2017-01-02 19:00:00'        "2017-01-02 17:18:14";"OutDoc";10;13.24;23.00;8
'2017-01-07 12:00:00'        "2017-01-16 13:43:35";"OutDoc";88;27.20;34.00;12
'2017-01-18 20:00:00'        "2017-01-18 18:24:30";"OutDoc";95;20.40;29.60;24
'2017-01-29 15:00:00'        "2017-01-28 11:03:42";"OutDoc2";138;20.40;29.60;14

EDIT: Now, when I explain a problem so good :) I find solution by myself. Is this optimal solution regarding performances or may be better/faster?

SELECT LAST(mydate) AS lastdate, LAST(doctype) AS lastdoctype, LAST(docnum) AS lastdocnum, 
   (SELECT LAST(inprice) AS lastinprice FROM mytry WHERE inprice>0 AND mydate<'2017-01-18 20:00:00') , 
   (SELECT LAST(price) AS lastprice FROM mytry WHERE price>0 AND mydate<'2017-01-18 20:00:00'), 
   SUM(inqty-outqty) AS lastqty
 FROM mytry 
WHERE mydate<'2017-01-18 20:00:00' 
LIMIT 1;

Answer 1

Use LAST() with FILTER:

SELECT 
    MAX(mydate) AS lastdate, 
    MAX(doctype) AS lastdoctype, 
    MAX(docnum) AS lastdocnum, 
    LAST(inprice ORDER BY mydate) FILTER (WHERE inprice <> 0) AS lastinprice, 
    LAST(price ORDER BY mydate) FILTER (WHERE price <> 0) AS lastprice, 
    SUM(inqty-outqty) AS lastqty
FROM mytry 
WHERE mydate < '2017-01-26 14:00:00' 

Answer 2

The first solution which I figured out is this:

SELECT
  MAX(m.mydate) AS lastdate,
  MAX(doctype) AS lastdoctype,
  MAX(docnum) AS lastdocnum,
  MAX(case when m.mydate = t.mydate then inprice end) AS lastinprice,
  MAX(case when m.mydate = t.mydate then price end) AS lastprice,
  SUM(inqty - outqty) AS lastqty
FROM mytry m
  JOIN (SELECT MAX(mydate) as mydate
        FROM mytry
        WHERE mydate < '2017-01-26 14:00:00' AND inprice != 0 AND price != 0) t ON TRUE
WHERE m.mydate < '2017-01-26 14:00:00'

there are could probably exists other solutions, more elegant one

What it does:

1. Use subquery to find out the last date when price was not equal to zero.
2. Join the date to each row in main query (look at this ON TRUE)
3. And then I did some kind of hack, I use CASE expression with date check condition. It guarantees (for you case at least) that price for only one row returns real price value, all others rows should return NULL instead of real price values.
4. And then I wrap that CASE in MAX aggregate (MIN should suite too)
5. And as aggregate functions omit NULL values, it returns value of price for the last day when price was not equal to zero.

The same situation for inprice.