Hi! I want get data from two table using join and compare date.
DECLARE @table1 AS TABLE(ID INT,StartDate Date, Cod_cust_id int, cod_acct int)
INSERT @table1(Id, StartDate, Cod_cust_id, cod_acct)VALUES (1,'09.03.2023', 1000, 200003)
INSERT @table1(Id, StartDate, Cod_cust_id, cod_acct)VALUES (2,'10.03.2023', 1001, 200004)
INSERT @table1(Id, StartDate, Cod_cust_id, cod_acct)VALUES (3,'10.03.2023', 1002, 200005)
INSERT @table1(Id, StartDate, Cod_cust_id, cod_acct)VALUES (4,'10.03.2023', 1003, 200006)
select * from @table1
DECLARE @table AS TABLE(ID INT,EveryDayDate Date, Cod_cust_id int, score int, category varchar(15))
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (1,'03.09.2023', 1000, 540, 'low-med')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (2,'03.10.2023', 1000, 545, 'low-med')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (3,'03.10.2023', 1001, 520, 'low')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (4,'03.11.2023', 1000, 550, 'med')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (5,'03.11.2023', 1001, 520, 'low')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (6,'03.12.2023', 1000, 560, 'high')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (7,'03.12.2023', 1001, 520, 'low')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (8,'03.12.2023', 1002, 520, 'low')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (9,'03.13.2023', 1000, 570, 'high-med')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (10,'03.13.2023', 1001, 520, 'low')
INSERT @table(Id, EveryDayDate, Cod_cust_id, score, category)VALUES (11,'03.13.2023', 1003, 580, 'med')
select * from @table
select tt.EveryDayDate, t.cod_acct, tt.score, tt.category from @table1 t inner join @table tt on t.Cod_cust_id = tt.Cod_cust_id where t.StartDate = tt.EveryDayDate
The result I want get in bellow:
|
ID
|
EveryDayDate
|
cod_acct
|
score
|
category
|
|
1
|
2023-03-09
|
200003
|
540
|
low-med
|
|
2
|
2023-03-10
|
200004
|
520
|
low
|
|
3
|
2023-03-12
|
200005
|
520
|
low
|
|
4
|
2023-03-13
|
200006
|
580
|
med
|
