Hi John,
I have created tables as you mentioned in your question and added the data using following code block.
INSERT INTO Table_1 VALUES
(1,'sony', 'Hyd','10','06','2013'),
(2,'jhon','Blr','05','06','2013'),
(3,'Ravi','Hyd','06','06','2013')
INSERT INTO Table_2 Values
(2,10000),
(3,20000)
Here is my query to get desired result set.
SELECT Table_1.Id,Table_1.Name, (CASE WHEN Table_2.Id IS NULL THEN 'Not Matched' ELSE 'Matched' END) As Status,
CAST(DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112))
- (DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112)) % 5) AS VARCHAR)+'-'+
CAST(DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112))
- (DATEDIFF(D,(Table_1.Year+Table_1.Month+Table_1.Date) ,CONVERT(VARCHAR,GETDATE(),112)) % 5)+ 5 AS VARCHAR) As Category,
(CASE WHEN Table_2.Id IS NULL THEN 0 ELSE Table_2.Sal END) As Salary
FROM Table_1 LEFT JOIN Table_2
ON Table_1.Id = Table_2.Id
I hope this helps you.
Happy coding.