Hi Team,
I am trying to build a query on my table to get a specific data result.
I have a table like below with below data:
CREATE TABLE tbl_Rate
(
ID INT IDENTITY(1,1),
DateLoad DATETIME,
BaseCur VARCHAR(100),
RateCur VARCHAR(10),
Rate NUMERIC(15,4)
)
GO
INSERT INTO tbl_Rate VALUES
('06 Mar 2014','GBP','INR',101.64),
('06 Mar 2014','GBP','ATS',101.64),
('06 Mar 2014','GBP','RBC',101.64),
('06 Mar 2014','GBP','TBC',101.64),
('06 Mar 2014','GBP','JIR',101.64),
('05 Mar 2014','GBP','INR',101.64),
('05 Mar 2014','GBP','ATS',101.64),
('05 Mar 2014','GBP','RBC',101.64),
('05 Mar 2014','GBP','TBC',101.64);
GO
This table contains the data date wise like 06 Mar and 05 Mar. I need to build a query to get the extra row for 06 Mar like below.
ID DateLoad BaseCur RateCur Rate
5 2014-03-06 00:00:00.000 GBP JIR 101.6400
The constraint is to build using Joins. Avoid result sets to work up with data like below:
SELECT * FROM
(SELECT * FROM tbl_Rate WHERE DateLoad = '06 Mar 2014') N LEFT JOIN
(SELECT * FROM tbl_Rate WHERE DateLoad = '05 Mar 2014') E ON N.RateCur = E.RateCur
WHERE E.RateCur is NULL
Thanks
Utkarsh