June 20, 2022 at 12:13 pm
Table 1
Bank ID
Transaction ID
Date
Amount
1
1
30-06-2020
50
1
2
30-06-2020
80
1
1
31-12-2020
72
1
2
31-12-2020
71
2
1
30-06-2020
70
2
2
30-06-2020
100
2
1
31-12-2020
67
2
2
31-12-2020
82
Table 2
Bank ID
Transaction ID
Date
Amount
1
1
31-03-2021
83
1
2
31-03-2021
88
Required Table
Bank ID
Transaction ID
Date
Amount
2
1
31-03-2021
67
2
2
31-03-2021
82
Can some help me with the query
Select distinct t1.Bank ID, t1.Transaction ID, t1.Date, t1.Amount from Table 1 t1 INNER JOIN Table 2 t2 ON t2.Bank ID <> (select distinct (Bank ID) from Table 2 where Date >= (select max(Date) from Table 2))
June 20, 2022 at 9:47 pm
It would have been nice if you had provided test data in a format that is readily usable. That's generally a prerequisite for having any success in getting a response on this site.
Since you are new to the site, I have done what you should have done and this is what I came up with based on your input:
DECLARE @Table1 TABLE (
[Bank ID] INT,
[Transaction ID] INT,
[Date] DATE,
[Amount] DEC(28,2)
)
INSERT INTO @Table1 ([Bank ID],[Transaction ID],[Date],[Amount])
VALUES
(1,1,'2020-06-30',50),
(1,2,'2020-06-30',80),
(1,1,'2020-12-31',72),
(1,2,'2020-12-31',71),
(2,1,'2020-06-30',70),
(2,2,'2020-06-30',100),
(2,1,'2020-12-31',67),
(2,2,'2020-12-31',82)
DECLARE @Table2 TABLE (
[Bank ID] INT,
[Transaction ID] INT,
[Date] DATE,
[Amount] DEC(28,2)
)
INSERT INTO @Table2 ([Bank ID],[Transaction ID],[Date],[Amount])
VALUES
(1,1,'2021-03-31',83),
(1,2,'2021-03-31',88)
DECLARE @RequiredTable TABLE (
[Bank ID] INT,
[Transaction ID] INT,
[Date] DATE,
[Amount] DEC(28,2)
)
INSERT INTO @RequiredTable ([Bank ID],[Transaction ID],[Date],[Amount])
VALUES
(2,1,'2021-03-31',67),
(2,2,'2021-03-31',82)
I'm sure this will help someone out there untangle and transform your query. 🙂
June 21, 2022 at 12:12 am
Your query does not join table 1 and table 2. How are 1 and 2 related?
I might suggest for readability creating a variable to hold max date prior to the join query, but that is just my preference.
June 21, 2022 at 11:05 am
Looking at this, the RequiredTable results are not possible from the query you've written. In the query, you select all of the columns from table 1, but the result rows do not match any rows from table 1.
Could you post a more 'plain english' definition of what you're trying to achieve, as from what you've posted so far it's not really clear?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply