Query Syntax

jasmeen kaur
jasmeen kaur used Ask the Experts™
on
My columns in the table is defined as below

fund1   fund2   pricingdatefund1    pricingdatefund2

I have to fetch the records in a way that  if pricing date fund 1= 15 Nov then fetch fund 1 record , if pricing date fund 2= 15 Nov then fetch fund2 record and if both of the pricingdatefund1, pricingdatefund2 matches my date then fetch both fund 1 and fund2.

I know it is complicated. I hope I have explained it

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Can you please provide few rows and the expected output? I mean columns you need in the output.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please try this , tested solution for you.

Table Creation and Data Geneation

CREATE TABLE Funds
(
	 fund1 VARCHAR(30)  
	,fund2   VARCHAR(30)  
	,pricingdatefund1 DATETIME    
	,pricingdatefund2 DATETIME
)
GO

INSERT INTO Funds 
VALUES   ( 'A' , 'B' , '2017/11/15' ,  '2017/11/15'  ),
		 ( 'D' , 'C' , '2017/11/15' ,  '2017/11/16'  ),
		 ( 'R' , 'T' , '2017/11/14' ,  '2017/11/15'  )

GO

Open in new window


SOLUTION

DECLARE @myDate AS DATETIME = '2017/11/15'
SELECT * FROM 
(
	SELECT fund2 Fund , pricingdatefund2 Datex
	FROM Funds 
	WHERE pricingdatefund2 = @myDate AND pricingdatefund2 <> pricingdatefund1
	UNION ALL
	SELECT fund1 Fund , pricingdatefund1 Datex
	FROM Funds 
	WHERE pricingdatefund1 = @myDate AND pricingdatefund2 <> pricingdatefund1
	UNION ALL
	SELECT fund1 Fund , pricingdatefund1 Datex
	FROM Funds 
	WHERE pricingdatefund1 = @myDate AND pricingdatefund2 = pricingdatefund1
	UNION ALL
	SELECT fund2 Fund, pricingdatefund2 Datex
	FROM Funds 
	WHERE pricingdatefund1 = @myDate AND pricingdatefund2 = pricingdatefund1
)r

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Fund                           Datex
------------------------------ -----------------------
T                              2017-11-15 00:00:00.000
D                              2017-11-15 00:00:00.000
A                              2017-11-15 00:00:00.000
B                              2017-11-15 00:00:00.000

(4 row(s) affected)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Question abandoned. Provided solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial