SQL - looks easy but I cannot solve it

[Nicole]

Here is a working SQL statement.
If I simplify it, - it becomes wrong.
Usually it is the other way round?

I create a snapshot of the code in color, so you can see the changes at one glance

This is the working code as text, the problem of false results you see on the screenshot

   

Code: MySQL  [Select][+][-]

1.  SELECT id_trade, EXIT_DATE, Ergebnis,
2.     (
3.     SELECT SUM(Ergebnis)
4.     FROM tbtrades y
5.     WHERE y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade AND
6.     zuKonto = '4'
7.     )
8.     FROM tbtrades x
9.     WHERE (Entry_Date >='27.12.2022' AND Exit_Date <= '31.03.2023') AND
10.     zuKonto = '4'
11.     ORDER BY x.EXIT_DATE, x.id_trade

[rvk]

The base of the WHERE from the sub SELECT ( "y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade" ) should NEVER be taken out.
You did take it out in your second (wrong) SQL.

Why?

The y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade makes sure the total is a running total.
If you take it out the sub select is like this:

Code: SQL  [Select][+][-]

1. SELECT SUM(Ergebnis) FROM tbtrades y WHERE zuKonto = '4'

for EVERY line. And that's why it doesn't change.

So put the "y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade" back in there.

What were you trying to accomplish by taking it out ???

[Zvoni]

And i don’t understand why she‘s still using this convoluted mess to calculate a running total, instead of studying how the SUM OVER works

Agree with rvk: what is your goal?

[sketch]

Code: [Select]

FROM tbtrades y
FROM tbtrades x
Why is the same table aliased twice? Don't you want a UNION between two tables?

[rvk]

Code: [Select]

FROM tbtrades y
FROM tbtrades x
Why is the same table aliased twice? Don't you want a UNION between two tables?

No, the y is in a subselect (presented as a field in the main select).
The x is the main select.
(Note that the FROM y is in a separate sub select in the SQL)

Although this could be done much clearer with a LEFT JOIN.

NOT a UNION. A UNION will append records, but here it is needed that it is added as a extra field (although we are not sure why and if that's really needed).

[Zvoni]

Untested:

Code: SQL  [Select][+][-]

1.      SELECT id_trade, EXIT_DATE, Ergebnis, SUM(Ergebnis) OVER(PARTITION BY zuKonto ORDER BY EXIT_DATE, id_trade) AS SumTotal
2.      FROM tbtrades
3.      WHERE (Entry_Date >='27.12.2022' AND Exit_Date <= '31.03.2023') AND
4.      zuKonto = '4'
5.      ORDER BY EXIT_DATE, id_trade

[rvk]

Untested:

That's what Fiddle is for 
https://dbfiddle.uk/MsMsTHW_

But this is the exact same SQL from the other topic, so I don't know why this new topic was opened.

Here is a working SQL statement.
If I simplify it, - it becomes wrong.

You didn't simplify it.... you just broke it 

(we already showed you why and how you broke it.)

[Zvoni]

Untested:

That's what Fiddle is for 
https://dbfiddle.uk/MsMsTHW_
But this is the exact same SQL from the other topic, so I don't know why this new topic was opened.

Yeah, with a small difference: I've introduced a Partition now in the SUM OVER

adjusted fiddle: https://dbfiddle.uk/pMeGTazz
I've taken out the filter with zuKonto, but added zuKonto as an output-Column, so she can see that the running total is reset for each "zuKonto"

[Nicole]

Thank you all so much!
And this time especially Zvoni for "sum over".

The explanations "why" I worked in a way, which looks strange without explanations:

1)
Why I did not use the sum over from the start, but the genius way of rvk ? - legacy.
When I started my project I had very old Delphi code with a very old firebird version and I had to migrate all SQL-codes to IBX.
This could not be done all at the same time.
Therefore I waited with the upgrade of Firebird to have any working version in Lazarus of my software before.
This old Firebird did not know sum over.

Now I have Firebird 4.0 and nothing hinders me to work with "sum over" - except my lacking knowledge.
As this is done as well, my code runs now with sum over and is quick as hell.

2)
Why I spoiled the rvk-code by taking out the "where":

The code is behind an interface. The user can select in a form, if he wants to see "all" or only a period.
If he selects "all" I can select a "far away" border for the date, which is ugly and a source for future errors.
Therefore I removed the where-clause, if the user clicks "show me all you have".

[Zvoni]

If he selects "all" I can select a "far away" border for the date, which is ugly and a source for future errors.
Therefore I removed the where-clause, if the user clicks "show me all you have".

And even this could be done with a Min/Max-Call per zuKonto
Aircode

Code: SQL  [Select][+][-]

1. SELECT zuKonto, MIN(Entry_Date) AS MinEntry, MAX(Exit_Date) AS MaxExit FROM tbtrades GROUP BY zuKonto

No need to "invent" some "far away" dates....
Put above into a View, then it's a simple If then Else in code constructing your WHERE-Clause

EDIT: Just saw it: NEVER EVER use localized date-representations in Queries!!
Your WHERE-Clause uses German notation. That's a BIG hidey-hole for bugs.

[rvk]

2)
Why I spoiled the rvk-code by taking out the "where":
-
The code is behind an interface. The user can select in a form, if he wants to see "all" or only a period.
If he selects "all" I can select a "far away" border for the date, which is ugly and a source for future errors.
Therefore I removed the where-clause, if the user clicks "show me all you have".

Understandible.
But for the running total to work you would still need the "y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade" part in the sub select.
Because, it you look at it and try to understand it, that's used to calculate the "total" upto that trade_id.
Without it you only get a "total" for everything and not a running total.

You can take out the  zuKonto = '4' but the other parts needs to remain.

So it would be (you may only change line 6 and 9):

Code: Pascal  [Select][+][-]

1.  SELECT id_trade, EXIT_DATE, Ergebnis,
2.     (
3.     SELECT SUM(Ergebnis)
4.     FROM tbtrades y
5.     WHERE y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade
6.                         AND zuKonto = '4'          # you may ONLY change this bit to keep a running total
7.     )
8.  FROM tbtrades x
9.  WHERE (Entry_Date >='27.12.2022' AND Exit_Date <= '31.03.2023') AND zuKonto = '4'
10.  ORDER BY x.EXIT_DATE, x.id_trade

But the SUM OVER PARTITION is a superior way to do it (which is only available with Firebird 3 and higher).

[Zvoni]

Here the solution with a CTE instead of a view:
https://dbfiddle.uk/Ul0pK_ND

Code: SQL  [Select][+][-]

1. WITH CTE AS
2.   (SELECT zuKonto, MIN(Entry_Date) AS MinEntry, MAX(Exit_Date) AS MaxExit
3.   FROM tbtrades GROUP BY zuKonto)
4.  
5. SELECT x.zuKonto, x.id_trade, x.EXIT_DATE, x.Ergebnis,
6. SUM(x.Ergebnis) OVER(PARTITION BY x.zuKonto ORDER BY x.EXIT_DATE, x.id_trade) AS SumTotal
7. FROM tbtrades x LEFT JOIN CTE c
8. ON x.zuKonto=c.zuKonto AND x.Entry_Date>=c.MinEntry AND x.Exit_date<=c.MaxExit
9. WHERE x.zuKonto = '3' /*AND (x.Entry_Date >='2022-12-27' AND x.Exit_Date <= '2023-03-31')*/
10. ORDER BY x.zuKonto, x.EXIT_DATE, x.id_trade
11.  

That way, if user wants it all just leave out the "... AND (x.Entry_Date >='2022-12-27' AND x.Exit_Date <= '2023-03-31')" part of the WHERE-Clause.
The CTE makes sure, that always the complete timewindow for a chosen "zuKonto" will be taken.
If the User choses a Timewindow, just append it to the Where-clause

[Nicole]

thank you for the codes

about:

NEVER EVER use localized date-representations in Queries

hm, yes I know....
I have this problem: These date selection comes from the user interface of one certain frame.
Many queries from many units work with those date-selections.
So in this very unit there is a code snippet, which takes those dates and melts them into a where-snippet.
I only call ThisFrame."date including where" and "date including and" and fill it in as string.

I cannot pass over param-by-names elements into the date-holding unit.
And I "cannot" grab those elements every time again.
Sure, I could, -  and I had it before.
This led to that many errors, that I do this solution now.
I do not like the German date format at all. Date formats are troubling me in my whole work, because there is an input format coming from US by MM / DD / YY.
No idea, why our US collegues want it like this.

The short idea of many words:
I there a way to make "parambyname"-elements into a method?
The way I tried, - did not work.

[rvk]

EDIT: Just saw it: NEVER EVER use localized date-representations in Queries!!
Your WHERE-Clause uses German notation. That's a BIG hidey-hole for bugs.

Uh, the DD.MM.YYYY notation is one of the standard ones.
YYYY-MM-DD is the other.
But if your using dots (.) it's DD.MM.YYYY... ALWAYS.
See here: https://firebirdsql.org/en/firebird-date-literals/

So this is not localization.

(But I also prefer the YYYY-MM-DD notation.)

Note that Firebird does not honor the Windows or Linux date locale format when interpreting date literals. Its interpretation of all-number date formats is decided by the separator character. When dot (.) is used as separator, Firebird interprets it as the non-U.S. notation DD.MM, whereas with any other separator it assumes the U.S. MM/DD notation. Outside the U.S. date locale, your applications should enforce or convert locale-specific DD/MM/CCYY date input to a literal that replaces the forward slash with a period (dot) as the separator. 'DD.MM.CCYY' is valid. Other date literal formats may be substituted.

[af0815]

The german date format (or other) should only be use for presentation. You should never use it in sql-strings. A userinput should be always normalized to the TDateTime format, so it is languageneutral. If you show dá Datetime you can convert in the format you want. I have written application for german/english/mandarin etc. i know what the goal is -> avoid internal string manipulation, always translate on the borders to the user. And then in their wanted format.