Hi Team,
1st image shows the input data of Settlement date, Amount , Account No . Now i need to compare the data with another sheet .2nd image shows the data which need to be validated.
Settlement date = posting date(if date is not found i need to look into + - 2 days , even if not found 2days i need to look into + - 5 days), Amount = Original amount, Account no = Account No and Method should be check as always. Can anyone help me in this case i need to use only linq
This query might work for just comparing the dates & other columns
SELECT t1., t2.
FROM table1 t1
JOIN table2 t2
ON t1.SettlementDate = t2.postingdate AND t1.amount = t2.Originalamount AND t1.accountno = t2.accountno AND AND t1.method = ‘Post’ AND t2.method = ‘Post’
or to check for ±2 or ±5 days gap you can use ‘Interval’
SELECT t1., t2.
FROM table1 t1
JOIN table2 t2
ON (t1.SettlementDate = t2.postingDate OR t1.Settlementdate BETWEEN t2.postingDate - INTERVAL 2 DAY AND t2.postingDate + INTERVAL 2 DAY OR t1.SettlementDate BETWEEN t2.postingDate - INTERVAL 5 DAY AND t2.postingDate + INTERVAL 5 DAY )
Can you try this
from t1 in dt1
from t2 in dt2
where (t1.SettlementDate == t2.PostingDate ||
(t1.SettlementDate >= t2.PostingDate.AddDays(-2) && t1.SettlementDate <= t2.PostingDate.AddDays(2)) ||
(t1.SettlementDate >= t2.PostingDate.AddDays(-5) && t1.SettlementDate <= t2.PostingDate.AddDays(5)))
select new { t1, t2 };
Try this sample & modify according to your filters
P.S : I have just filtered on Account Num, date1=date2 or date1 in range of ±2 in date2 , you can add additional logic to that linq acc to your requirement. LinQ.zip (15.9 KB)