How to show the closest date to the selected one

I’m trying to extract the stock in an specific date. To do so, I’m doing a cumulative of stock movements by date, product and warehouse.

select          m.codart AS REF, 
                m.descart AS 'DESCRIPTION', 
                m.codalm AS WAREHOUSE, 
                m.descalm AS WAREHOUSEDESCRIP, 
                m.unidades AS UNITS, 
                m.entran AS 'IN', 
                m.salen AS 'OUT', 
                m.entran*1 + m.salen*-1 as MOVEMENT,
                (select sum(m1.entran*1 + m1.salen*-1)
                    from MOVSTOCKS m1 
                    where m1.codart = m.codart and m1.codalm = m.codalm and m.fecdoc >= m1.fecdoc) as 'CUMULATIVE',
                m.PRCMEDIO as 'VALUE', 
                m.FECDOC as 'DATE', 
                m.REFERENCIA as 'REF', 
                m.tipdoc as 'DOCUMENT'
    from        MOVSTOCKS m
    where       (m.entran <> 0 or m.salen <> 0)
                and (select max(m2.fecdoc) from MOVSTOCKS m2) < '2020-11-30T00:00:00.000'
    order by    m.fecdoc

Without the and (select max(m2.fecdoc) from MOVSTOCKS m2) < '2020-11-30T00:00:00.000' it shows data like this, which is ok.

REF  WAREHOUSE  UNITS   IN   OUT  MOVEMENT  CUMULATIVE   DATE   
1       0         2      0    2      -2        -7      2020-11-25 
1       1         3      0    3      -3        -3      2020-11-25
1       0         5      0    5      -5        -7      2020-11-25
1       0         9      9    0       9         2      2020-11-26 
2       0         2      2    0       2         2      2020-11-26
1       0         1      1    0       1         3      2020-12-01

The problem is, with the subselect in the where clause it returns no results (I think it is because it just looks for the max date and says it is bigger than 2020-11-30). I would like it to show the closest dates (all of them, for each product and warehouse) to the selected one, in this case 2020-11-30.

It should look slike this:

REF  WAREHOUSE  UNITS   IN   OUT  MOVEMENT  CUMULATIVE   DATE   
1       1         3      0    3      -3        -3      2020-11-25
1       0         9      9    0       9         2      2020-11-26 
2       0         2      2    0       2         2      2020-11-26

Sorry if I’m not clear. Ask me if I have to clarify anything

Thank you

Answer

I am guessing that you want something like this:

select t.*
from (select m.*,
             sum(m.entran - m1.salen) over (partition by m.codart, m.codalm order by fecdoc) as cumulative,
             max(fecdoc) over (partition by m.codart, m.codalm) as max_fecdoc
      from MOVSTOCKS m
      where fecdoc < '2020-11-30'
     ) m
where fecdoc = max_fecdoc;

The subquery calculates the cumulative amount of stock using window functions and filters for records before the cutoff date. The outer query selects the most recent record from the combination of codeart/codalm, which seems to be how you are identifying a product.

Advertisement

Answer