[solved] SQL - max Sum within a period request
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2023-06-27 17:25

Here comes a thing, which may be easy for somebody who works with it
every day (this is unfortunately not me).
Please see screenshot.

Every day there are margins required. Some day one, some day two or
three, some days not at all.

(not sure, if this feature to add:
Some days the margin are closed the same day, some days not.)

My query shall answer the question: How huge has the account to be, to
pay this within a certain time?
This may be e.g. the first quarter of the year 2023.
In our example the answer is shown by the blue arrow.

----------------------------------------------------------------------
Martin_fr · 2023-06-27 17:48

Important question: What happens if on one line Entry_Date and
Exit_Date differ?

Otherwise, you can get the sum for each day
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select
entry_date, sum(margin) as msum from TbTrades group by entry_date;
To get the biggest such margin
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select
entry_date, sum(margin) as msum from TbTrades group by entry_date
order by msum desc limit 1;

You can limit either of those queries to a specific range of time by
adding (after the from, before "group by")
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i where
entry_date > XXX and entry_date < YYY

----------------------------------------------------------------------
paweld · 2023-06-27 18:17

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
zukonto, entry_date, SUM(margin) margin_sum, MAX(margin) margin_max
FROM tbtrades WHERE zukonto=4 AND entry_date>=1.1.2023 AND
entry_date<=31.12.2023 GROUP BY zukonto, entry_date ORDER BY
entry_date

----------------------------------------------------------------------
Nicole · 2023-06-27 18:20

to Martin:

This works wonderfully.
Thank you so very much!!


Well, what about if entry and exit days differ. Then it gets hard.
In ancient days I had such queries, but it was huge work for me and I
cannot remember how I did it.

Then I would have to check for overlapping.
E.g. on the 3rd of August we pay 3000, on the 5th we pay 1000. On the
7th we get back 500...
And so on.

Before we will have to order the data. And even here my head gets mad:
for the entry or for the exit?

I wonder, if this will ever be needed. So I hesitate to put energy in
it.
The old question: Do you work for any needed purpose or will you
forget about this time-consuming feature soon?

At the moment I tend to say: thank you!
And leave it like this.
However this is ugly style.
Perhaps I write a comment for it.

to Paweld:
Thank you as well!
I will try this too, looks good.
and: zuKonto is really important. I added it in a different way, your
way looks better.