Click here to Skip to main content
15,354,171 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
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:
SQL
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
Posted
Updated 6-Mar-14 0:03am
v2
Comments
dbrenth 6-Mar-14 16:29pm
   
What is the reason for the "constraint"? Is this a homework assignment?
Utkarsh Agarwal 7-Mar-14 0:32am
   
I am trying it myself as a process of learning

1 solution

On the chance that it is not a homework assignment, this will get you the same result without the subqueries:

select * from
tbl_Rate n LEFT JOIN tbl_Rate e ON  N.RateCur = E.RateCur
	and n.DateLoad = '06 Mar 2014' and e.DateLoad = '05 Mar 2014'
where 
    n.DateLoad = '06 Mar 2014' and e.DateLoad is NULL
   
Comments
Utkarsh Agarwal 7-Mar-14 0:33am
   
Thanks, it worked. Just want to know one thing why it is required to put the dateload as 6th Mar in where cluase also ?
dbrenth 7-Mar-14 8:27am
   
Because without it, you get that one and the '05 mar 2014' lines - which you don't want.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900