help with an SQL - statement
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-03-04 15:28

I want to "merge" 2 queries:

1)
select date, and, some, more from tableA where fk = 5

2)
select date, and, some, more from tableA where fk = 6

The problem is the date.
Let us say, the first requests starts and ends earlier than the second
one.
In this case only the OVERLAPPING dates shall be found.
And to add a complexity:
In case one date is NOT in BOTH requests, this date shall be skipped.

as an example:
Data first starts in January and ends in June. Data second starts in
March and ends in August.
In this case March to June shall be selected in those field, which are
contained in both series.

----------------------------------------------------------------------
jcmontherock · 2024-03-04 22:55

It could be work with "Left join" these 2 tables (?...)

----------------------------------------------------------------------
dseligo · 2024-03-05 00:16

Quote from: Nicole on March 04, 2024, 04:28:00 pmLet us say, the first
requests starts and ends earlier than the second one.

Do you have more date fields in one row? This 'fk' field, is it unique
or are there more fields with i.e. fk = 5?
Can you give some example rows how your data looks and what you want
to accomplish?

Something like that:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i fk |
date---+-----------5  | 05.03.2024.5  | 15.03.2024.6  | 01.01.2024.6
 | 06.06.2024.6  | 26.06.2024.

----------------------------------------------------------------------
Zvoni · 2024-03-05 07:30

Haven't understood a word.
Do you have any sample-Data/Records?

Where is "Start"/"End" coming from? are they separate fields? or the
one Date-Field, but different records?

EDIT: If i'm reading this right, you have 3 scenarios
1) No overlapping at all
2) partial overlapping (your example)
3) full overlap (one result fully contained within the other)

for all 3: What to return?

Tested in SQLite
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 WITH      
    CT      AS (SELECT ID, Mydate, FK,                        
ROW_NUMBER() OVER(PARTITION BY FK ORDER BY Mydate) AS FirstDate,     
                 ROW_NUMBER() OVER(PARTITION BY FK ORDER BY Mydate
DESC) AS LastDate                        FROM tbl_test),        MX   
 AS (SELECT MAX(Mydate) AS MaxStart FROM CT WHERE FirstDate=1),      
MI      AS (SELECT MIN(Mydate) AS MinLast FROM CT WHERE LastDate=1), 
     MU      AS (SELECT MaxStart, MinLast FROM MX INNER JOIN MI ON
1=1) SELECT T.ID, T.Mydate, T.FK FROM tbl_test AS TINNER JOIN CT ON
CT.ID=T.IDINNER JOIN MU ON T.Mydate BETWEEN MU.MaxStart AND MU.MinLast
WHERE T.FK IN (5,6) ORDER BY T.Mydate
I used following sample-Data
ID   MyDate   FK
1   2024-01-05   5
2   2024-06-28   5
3   2024-03-02   6
4   2024-03-28   6
5   2024-04-06   6
6   2024-04-22   6
7   2024-05-17   6
8   2024-06-09   6
9   2024-07-12   6
10   2024-08-05   6


Returns

ID   MyDate   FK
3   2024-03-02   6
4   2024-03-28   6
5   2024-04-06   6
6   2024-04-22   6
7   2024-05-17   6
8   2024-06-09   6
2   2024-06-28   5

EDIT2: Huh?
Just tested it with all of my 3 scenarios
1) Correct. No result, since there is no overlapping (FK=5 starts in
Jan, ends in Feb, FK=6 as above)
2) Correct. see above
3) Correct. only the "inner" records are returned (fk=5 starts in Jan,
ends in October, FK=6 as above)

----------------------------------------------------------------------
Nicole · 2024-03-05 09:53

There is only one table, so at least we can do it without needing
join.
And the good news are, that the combination of fk and date are unique.


the DDL reads

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 CREATE
TABLE TBKURSZEILEN(  ID_KURSZEILE integer NOT NULL,  FK_KONTRAKT
integer NOT NULL,  DATUM date NOT NULL,  O decimal(18,7),  H
decimal(18,7),  L decimal(18,7),  C decimal(18,7),  VOLUME integer, 
OPENINTEREST integer,  CONSTRAINT PK_IDKURSZEILEN PRIMARY KEY
(ID_KURSZEILE),  CONSTRAINT UNQ_KONTRAKT_DATUM UNIQUE
(FK_KONTRAKT,DATUM));ALTER TABLE TBKURSZEILEN ADD CONSTRAINT
FK_KONTRAKT  FOREIGN KEY (FK_KONTRAKT) REFERENCES TBKONTRAKTE
(ID_KONTRAKT) ON DELETE CASCADE;GRANT DELETE, INSERT, REFERENCES,
SELECT, UPDATE ON TBKURSZEILEN TO  SYSDBA WITH GRANT OPTION GRANTED BY
SYSDBA;

to make it easier, I tell you, what its function is:
E.g. you have crude oil delivered in May, then the foreign key points
at the table, which contains all information about May-Crude-oil. Then
there is crude-oil which is delivered in December. The foreign key
will point at December-Crude-oil.

Now my software shall answer the question: Is crude oil of May of
December more expensive? It is clear, that in June there is no price
for May-Crude Oil, because May is over.
So the topic is, that we only have overlapping datas in comparing.

What, if I want do compare crude oil with gold? In London they have a
holiday and not in New York? In this case there is one day with data
and the other one without. In this case, the value shall be skipped.

----------------------------------------------------------------------
egsuh · 2024-03-06 04:36

union?

----------------------------------------------------------------------
dseligo · 2024-03-06 09:21

Quote from: egsuh on March 06, 2024, 05:36:27 amunion?

Maybe. Or joining TBKURSZEILEN table with itself with two different
dates and same key.
I still don't understand how actual data looks like, that is why I
asked for a sample.

----------------------------------------------------------------------
Nicole · 2024-03-06 09:29

Thank you Zvoni for taking the time to test this.

The syntax works, but makes my FlameRobin freeze and crash.

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     WITH  
           CT      AS (SELECT ID_KURSZEILE, datum, fk_kontrakt,      
                    ROW_NUMBER() OVER(PARTITION BY fk_kontrakt ORDER
BY datum) AS FirstDate,                            ROW_NUMBER()
OVER(PARTITION BY fk_kontrakt ORDER BY datum DESC) AS LastDate       
                   FROM TBKURSZEILEN),            MX      AS (SELECT
MAX(datum) AS MaxStart FROM CT WHERE FirstDate=1),            MI    
 AS (SELECT MIN(datum) AS MinLast FROM CT WHERE LastDate=1),         
 MU      AS (SELECT MaxStart, MinLast FROM MX INNER JOIN MI ON 1=1)  
     SELECT T.ID_KURSZEILE, T.datum, T.fk_kontrakt    FROM
TBKURSZEILEN AS T    INNER JOIN CT ON CT.ID_KURSZEILE=T.ID_KURSZEILE 
 INNER JOIN MU ON T.datum BETWEEN MU.MaxStart AND MU.MinLast    WHERE
T.fk_kontrakt IN (5,6)    ORDER BY T.datum


then I tried it with union, but there is a syntax problem in line 8,
which you may be see at first glance  in "FROM MX union MI ON 1=1))".
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     WITH  
           CT      AS (SELECT ID_KURSZEILE, datum, fk_kontrakt,      
                    ROW_NUMBER() OVER(PARTITION BY fk_kontrakt ORDER
BY datum) AS FirstDate,                            ROW_NUMBER()
OVER(PARTITION BY fk_kontrakt ORDER BY datum DESC) AS LastDate       
                   FROM TBKURSZEILEN),            MX      AS (SELECT
MAX(datum) AS MaxStart FROM CT WHERE FirstDate=1),            MI    
 AS (SELECT MIN(datum) AS MinLast FROM CT WHERE LastDate=1),         
 MU      AS (SELECT MaxStart, MinLast FROM MX union MI ON 1=1)       
SELECT T.ID_KURSZEILE, T.datum, T.fk_kontrakt    FROM TBKURSZEILEN AS
T    INNER union CT ON CT.ID_KURSZEILE=T.ID_KURSZEILE    INNER union
MU ON T.datum BETWEEN MU.MaxStart AND MU.MinLast    WHERE
T.fk_kontrakt IN (5,6)    ORDER BY T.datum

----------------------------------------------------------------------
egsuh · 2024-03-06 10:00

Please look for union syntax.

----------------------------------------------------------------------
Nicole · 2024-03-06 10:15

If I would be able to solve this by Google and syntax check by myself,
I would have done it.

----------------------------------------------------------------------
rvk · 2024-03-06 11:03

Quote from: Nicole on March 06, 2024, 11:15:20 amIf I would be able to
solve this by Google and syntax check by myself, I would have done it.
You might want to put some effort in creating a sample table on
dbfiddle.uk so anybody can test their suggestions.

Like the following: https://dbfiddle.uk/3EEDkjsh
(I've put your SQL in to see what the result is, dbfiddle doesn't hang
on your SQL)

Can you describe what exactly the dataset looks like that you want
returned (from that example from dbfiddle)?

Because your effert doesn't follow the rule:
"" In case one date is NOT in BOTH requests, this date shall be
skipped. ""

Isn't this just what you want?
https://dbfiddle.uk/RikuSWzL

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
datumFROM TBKURSZEILENWHERE fk_kontrakt IN (5,6)GROUP BY 1HAVING
COUNT(*) = 2
It will give you all the dates where there is fk_kontract for 5 AND 6.
The begin and end dates are automatically followed.
(You say you wanted the records itself)

Because we do a group you can't distinguish between other fields (if
they are important)....

----------------------------------------------------------------------
Nicole · 2024-03-06 14:45

Thank you so much.
This looks extremely promising.
This is what I want of the idea, but not of the output.

I wrote
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
datum    FROM TBKURSZEILEN    WHERE fk_kontrakt IN (2340,2369)   
GROUP BY 1    HAVING COUNT(datum) = 2
and got a long list of datum, which probably are exactly the matches I
am looking for.
With these matches I can do it with Lazarus in the worst case.

in the best cast, SQL does this for me.
What I want is this (this cannot work, I know)

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
datum, id_kurszeile, c    FROM TBKURSZEILEN    WHERE fk_kontrakt IN
(2340,2369)    GROUP BY 1    HAVING COUNT(datum) = 2
This id_kurszeilen and c cannot work, because I just searched for
mathcing fields of data.
Not sure, if this is possible at all and how.
I would need them sorted by fk:

So my output must be:
The date of the working query from above FOLLOWED
 by the fields fk=2340 and the field c belonging to this fk.
 by the fields fk=2369 and the field c belonging to this fk.

In other words:
At the moment the output is:
10.8.2018
14.8.2018
21.8.2018

and I want it to be
10.8.2018, 2340, c-value, 2369, c-value
14.8.2018, 2340, c-value, 2369, c-value
21.8.2018, 2340, c-value, 2369, c-value

or similar
To put it differently: I need more fields of this matching dates.

At them moment I read this error-message:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Invalid
expression in the select list (not contained in either an aggregate
function or the GROUP BY clause)an error message not very surprising,
but I do not see the way out.

----------------------------------------------------------------------
gidesa · 2024-03-06 16:47

Hello,
try this:

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 with tbd as
(select datumfrom TBKURSZEILENwhere fk_kontrakt in (5,6)group by
1having count(*) = 2 ) select id_kurszeile, fk_kontrakt, t.datum from
tbkurszeilen t  where exists (select * from tbd where t.datum = datum)
Simply it search any row from tbkurszeilen in the list of dates
previously extracted.

Result example:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
|ID_KURSZEILE           |FK_KONTRAKT            |      | DATUM 5     
5       2024-05-016       5       2024-06-017       5      
2024-07-018       5       2024-08-0111      6       2024-05-0112    
 6       2024-06-0113      6       2024-07-0114      6      
2024-08-01 [/color]

----------------------------------------------------------------------
Nicole · 2024-03-06 17:50

Thank you!

The data are there and looking good, but still too many.
The problem is, that the restriction to the 2 foreign keys does not
work yet.
I attach you a screenshot with 2 attempts.

----------------------------------------------------------------------
rvk · 2024-03-06 17:50

Quote from: gidesa on March 06, 2024, 05:47:46 pmHello,
try this:
Yes. Like that.
You probably need to add the where for 5,6 in the main select too
otherwise you can get other numbers too, but then it should work.

----------------------------------------------------------------------
Nicole · 2024-03-06 18:00

Thank you all so very much, it works.
And I learnt a lot

    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 with tbd as
(    select datum    from TBKURSZEILEN    where fk_kontrakt in
(2340,2369)    group by 1    having count(datum) = 2 )         select
id_kurszeile, fk_kontrakt, t.datum from tbkurszeilen t      where
exists (select * from tbd where t.datum = datum and t.FK_KONTRAKT in
(2340,2369))

----------------------------------------------------------------------
gidesa · 2024-03-06 18:31

@rvk yes, you are right