Fetch stock balance on a given day (or day)

Asked

Viewed 377 times

-1

I have the table "saldo_dia" where I record the product (ID), the date and the balance. I need to generate a report (Kardex) and fetch the balance of a given day as "starting balance".

My question is the following: if the user searches from a date that the product did not have movement (that is, there is no "saldo_dia" for that date), I am looking for the next day with balance. However, it may be that there is no "next day", in this case - and I think it is the right one - it would be to look for the last day with balance. But how to do this without generating unnecessary additional queries?

Example:

The reporting period is from 01/12/2019 to 10/12/2019. The last "saldo_dia" was 25/11/2019 and the next "saldo_dia" was only 05/12/2019.

Which "day balance" should I display as the starting balance? And how to correctly search for this balance?

EDIT

The consultation I am currently carrying out is as follows::

SELECT SaldoDia.saldo FROM saldo_dias AS SaldoDia WHERE SaldoDia.produto_id = 1 AND SaldoDia.dia >= '2019-12-01' LIMIT 1

The informed date is the initial date of the requested report.

mysql query

  • Do the following, create an IF in your code, if it does not find anything at the current date, limit a number of days for it to find results, example, 7 days and mount a new query inside the if, one for 7 days more and another for 7 days less, can leave this number for the client’s choice when generating the report... select * FROM tabela WHERE saldo_dia BETWEEN concat(CURRENT_DATE(), ' 00:00:00') AND concat(CURRENT_DATE() + INTERVAL 7 DAY, ' 23:59:59'), this helps you?

  • @Jaksonfischer this is basically the consultation I already do today. What I’m trying to avoid is just keep creating IF’s. In addition there is the question of a given product may not have had movement (and therefore without "saldo_dia") in a period of 7 days, for example.

  • And why not report that there are no results for this interval?

  • @Jorgeb. is that you may have movement in the reported interval. What you may not have is a "saldo_dia" coinciding with the initial day of the report. For example, as I mentioned above: the report starts on 01/12/2019, but the last "saldo_dia" is only on 25/11/2019. That is, it has movement, but does not have the "saldo_dia" from the beginning of the report. In this case, I have to take the balance closer to the beginning of the report.

  • 1

    But isn’t the initial balance always the balance of the day immediately prior to the requested period in which a movement took place? In this case it is not sufficient to take the balance of the longest <= starting date of the period or, if no such movement exists, consider the zero balance?

  • @anonymity makes sense. I hadn’t thought of it that way and I think I was complicating things a bit here. I’m going to test this and I think it will work.

Show 1 more comment

2 answers

0

Try this and see if it returns what you need:

   SELECT saldo_id, dia, saldo,

   ISNULL((SELECT SUM(saldo) FROM saldos_dias AS SaldoDiaAtual WHERE 
    SaldoDiaAnterior.saldo_id = SaldoDiaAtual.saldo_id AND SaldoDiaAnterior.dia >= 
   SaldoDiaAtual.dia),0) as total

   FROM saldos_dias AS SaldoDiaAnterior
   where SaldoDiaAnterior.id_produto=1 and SaldoDiaAnterior.dia and '2019-11-10' 
   between SaldoDiaAnterior.dia and SaldoDiaAtual.dia LIMIT 1;

0

See if that’s not what you want. Within the reporting date, fetch the latest balance. Do not find a value in the period, find the most recent. If not, return 0.

In this case, the start date of the report is irrelevant and only matters the end date, to always look for values prior to that date.

SELECT IFNULL(
  (SELECT COALESCE(SaldoDia.saldo, 0)
    FROM saldo_dias AS SaldoDia
    WHERE
            SaldoDia.produto_id = 1
        AND SaldoDia.dia <= '2019-11-10'
    ORDER BY SaldoDia.dia DESC
    LIMIT 1),
  0
);

Example fiddle: http://sqlfiddle.com/#! 9/47d535/1/0

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.