help with an SQL - statement

[Nicole]

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]

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

[dseligo]

Let 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][+][-]

1. fk | date
2. ---+-----------
3. 5  | 05.03.2024.
4. 5  | 15.03.2024.
5. 6  | 01.01.2024.
6. 6  | 06.06.2024.
7. 6  | 26.06.2024.

[Zvoni]

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][+][-]

1. WITH   
2.         CT      AS (SELECT ID, Mydate, FK,
3.                         ROW_NUMBER() OVER(PARTITION BY FK ORDER BY Mydate) AS FirstDate,
4.                         ROW_NUMBER() OVER(PARTITION BY FK ORDER BY Mydate DESC) AS LastDate
5.                         FROM tbl_test),
6.         MX      AS (SELECT MAX(Mydate) AS MaxStart FROM CT WHERE FirstDate=1),
7.         MI      AS (SELECT MIN(Mydate) AS MinLast FROM CT WHERE LastDate=1),
8.         MU      AS (SELECT MaxStart, MinLast FROM MX INNER JOIN MI ON 1=1)
9.  
10. SELECT T.ID, T.Mydate, T.FK
11. FROM tbl_test AS T
12. INNER JOIN CT ON CT.ID=T.ID
13. INNER JOIN MU ON T.Mydate BETWEEN MU.MaxStart AND MU.MinLast
14. WHERE T.FK IN (5,6)
15. 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]

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][+][-]

1. CREATE TABLE TBKURSZEILEN
2. (
3.   ID_KURSZEILE integer NOT NULL,
4.   FK_KONTRAKT integer NOT NULL,
5.   DATUM date NOT NULL,
6.   O decimal(18,7),
7.   H decimal(18,7),
8.   L decimal(18,7),
9.   C decimal(18,7),
10.   VOLUME integer,
11.   OPENINTEREST integer,
12.   CONSTRAINT PK_IDKURSZEILEN PRIMARY KEY (ID_KURSZEILE),
13.   CONSTRAINT UNQ_KONTRAKT_DATUM UNIQUE (FK_KONTRAKT,DATUM)
14. );
15. ALTER TABLE TBKURSZEILEN ADD CONSTRAINT FK_KONTRAKT
16.   FOREIGN KEY (FK_KONTRAKT) REFERENCES TBKONTRAKTE (ID_KONTRAKT) ON DELETE CASCADE;
17. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
18.  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]

union?

[dseligo]

union?

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]

Thank you Zvoni for taking the time to test this.

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

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

1.     WITH  
2.             CT      AS (SELECT ID_KURSZEILE, datum, fk_kontrakt,
3.                             ROW_NUMBER() OVER(PARTITION BY fk_kontrakt ORDER BY datum) AS FirstDate,
4.                             ROW_NUMBER() OVER(PARTITION BY fk_kontrakt ORDER BY datum DESC) AS LastDate
5.                             FROM TBKURSZEILEN),
6.             MX      AS (SELECT MAX(datum) AS MaxStart FROM CT WHERE FirstDate=1),
7.             MI      AS (SELECT MIN(datum) AS MinLast FROM CT WHERE LastDate=1),
8.             MU      AS (SELECT MaxStart, MinLast FROM MX INNER JOIN MI ON 1=1)
9.      
10.     SELECT T.ID_KURSZEILE, T.datum, T.fk_kontrakt
11.     FROM TBKURSZEILEN AS T
12.     INNER JOIN CT ON CT.ID_KURSZEILE=T.ID_KURSZEILE
13.     INNER JOIN MU ON T.datum BETWEEN MU.MaxStart AND MU.MinLast
14.     WHERE T.fk_kontrakt IN (5,6)
15.     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][+][-]

1.     WITH  
2.             CT      AS (SELECT ID_KURSZEILE, datum, fk_kontrakt,
3.                             ROW_NUMBER() OVER(PARTITION BY fk_kontrakt ORDER BY datum) AS FirstDate,
4.                             ROW_NUMBER() OVER(PARTITION BY fk_kontrakt ORDER BY datum DESC) AS LastDate
5.                             FROM TBKURSZEILEN),
6.             MX      AS (SELECT MAX(datum) AS MaxStart FROM CT WHERE FirstDate=1),
7.             MI      AS (SELECT MIN(datum) AS MinLast FROM CT WHERE LastDate=1),
8.             MU      AS (SELECT MaxStart, MinLast FROM MX union MI ON 1=1)
9.      
10.     SELECT T.ID_KURSZEILE, T.datum, T.fk_kontrakt
11.     FROM TBKURSZEILEN AS T
12.     INNER union CT ON CT.ID_KURSZEILE=T.ID_KURSZEILE
13.     INNER union MU ON T.datum BETWEEN MU.MaxStart AND MU.MinLast
14.     WHERE T.fk_kontrakt IN (5,6)
15.     ORDER BY T.datum

[egsuh]

Please look for union syntax. 

[Nicole]

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

[rvk]

If 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][+][-]

1. SELECT datum
2. FROM TBKURSZEILEN
3. WHERE fk_kontrakt IN (5,6)
4. GROUP BY 1
5. HAVING 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]

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][+][-]

1.     SELECT datum
2.     FROM TBKURSZEILEN
3.     WHERE fk_kontrakt IN (2340,2369)
4.     GROUP BY 1
5.     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][+][-]

1.     SELECT datum, id_kurszeile, c
2.     FROM TBKURSZEILEN
3.     WHERE fk_kontrakt IN (2340,2369)
4.     GROUP BY 1
5.     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][+][-]

1. 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]

Hello,
try this:

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

1. with tbd as (
2. select datum
3. from TBKURSZEILEN
4. where fk_kontrakt in (5,6)
5. group by 1
6. having count(*) = 2 )
7.  
8. select id_kurszeile, fk_kontrakt, t.datum from tbkurszeilen t
9.   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][+][-]

1. |ID_KURSZEILE  
2.         |FK_KONTRAKT    
3.         |      | DATUM
4.  
5. 5       5       2024-05-01
6. 6       5       2024-06-01
7. 7       5       2024-07-01
8. 8       5       2024-08-01
9. 11      6       2024-05-01
10. 12      6       2024-06-01
11. 13      6       2024-07-01
12. 14      6       2024-08-01
13.  

[/color]

[Nicole]

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]

Hello,
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.