alexa

SQL inner join with filtering ?

SQL inner join with filtering ?

Here is how I would do this with Snowflake:

 --create the tables and load data

--table1
CREATE TABLE TABLE1 (ID NUMBER, DATE DATE);

INSERT INTO TABLE1 VALUES (1,   '2022-01-01');
INSERT INTO TABLE1 VALUES (2  , '2022-02-01');
INSERT INTO TABLE1 VALUES (3  , '2022-02-05');

--table 2
CREATE TABLE TABLE2 (ID NUMBER, DATE DATE, AMOUNT NUMBER);
 
INSERT INTO TABLE2 VALUES(1,   '2021-08-01',    15);
INSERT INTO TABLE2 VALUES(1,   '2022-02-10',    15);
INSERT INTO TABLE2 VALUES(2,   '2022-02-15',    20);
INSERT INTO TABLE2 VALUES(2,   '2021-01-01',    15);
INSERT INTO TABLE2 VALUES(2,   '2022-02-20',    20);
INSERT INTO TABLE2 VALUES(1,   '2022-03-01',    15); 

Now obtain the data using a select

 SELECT TABLE1.ID, MAX(TABLE2.DATE), SUM(AMOUNT)
FROM TABLE1, TABLE2
WHERE TABLE1.ID = TABLE2.ID
  AND TABLE1.DATE < TABLE2.DATE 
  GROUP BY TABLE1.ID 

Results

ID MAX(TABLE2.DATE) SUM(AMOUNT)
1 2022-03-01 30
2 2022-02-20 40

261 0
7

Write a Comments


* Be the first to Make Comment

GoodFirms Badge
GoodFirms Badge

Fix Your Meeting With Our SEO Consultants in India To Grow Your Business Online

Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry