Inner Join with logic issue

Transact-SQL
1

Hi,
I have a query where i want to return one row for a given foreign key. This i have working but an extra bit of logic i require is that if there is a specific value set for this subquery I would like to return that row otherwise I would like to return the one with the greatest expiry date.

cqID	cqtID	cqtName	cqFromDT	cqExpiryDT	cqNoExpiryDT
1047	1041	Welding License2014-11-30 	2016-11-30 	0
1046	1040	Site Induction	2015-07-01 	2016-09-03 	0
1099	1040	Site Induction	2012-04-12 	2013-04-12 	1

I hope the table above is legible but what id like to have returned from that query would be the 1st and 3rd rows. Rows 2 and 3 are the same cqtID but I only want the 3rd row to be returned because the cqNoExpiryDT is set to 1. So basically if a cqtID has a value of 1 i would like that row returned otherwise I want the row with the greatest cqExpiryDT date returned.

What I have at the moment is

SELECT cqt.cqtID, cqt.cqtName, cq.cqFromDT, cq.cqExpiryDT, cq.cqID, cq.cqNoExpiryDT
FROM contractorQualification cq 
Inner Join 
	( Select cqtID ,max(cqExpiryDT) as cqExpiryDT 
					From contractorQualification 
					where contractorID = 1036 
					Group By cqtID) As [q] On cq.cqtID = q.cqtID and cq.cqExpiryDT = q.cqExpiryDT 
				
WHERE cq.contractorID = 1036
order by cq.cqExpiryDT desc

What i was trying to do was build in some logic into the inner join using case statements similiar to the following

SELECT cqt.cqtID, cqt.cqtName, cq.cqFromDT, cq.cqExpiryDT, cq.cqID, cq.cqNoExpiryDT 
FROM contractorQualification cq 
Inner Join 
	( case select count(*) from contractorQualification bt where contractorID = 1036 and cq.cqtID=bt.cqtID and bt.cqNoExpiryDT=1
				when 0 then 
					Select cqtID ,max(cqExpiryDT) as cqExpiryDT 
					From contractorQualification 
					where contractorID = 1036 
					Group By cqtID) As [q] On cq.cqtID = q.cqtID and cq.cqExpiryDT = q.cqExpiryDT 
				else
					Select cqID
					From contractorQualification 
					where contractorID = 1036 
					and bt.cqNoExpiryDT=1) As [q] On cq.cqID = q.cqID
				end

WHERE cq.contractorID = 1036 
AND cq.cqIsActive = 1 
order by cq.cqExpiryDT desc

The above gave syntax errors and whatever way I tried I was running into a dead end.
Any help much appreciated.

Thanks

2

I would change that to use ROW_NUMBER() OVER() partitioned on contractorID (I think??!!) with an ORDER BY to get the rows in the sequence that you want so that the first one is the relevant one

Might need some fancy footwork to get the first cqNoExpiryDT=0 and the first cqNoExpiryDT=1 ...

1 Like
3

In future, please provide consumable test data:

CREATE TABLE #t
(
	cqID int NOT NULL
	,cqtID int NOT NULL
	,cqtName varchar(30) NOT NULL
	,cqFromDT date NOT NULL
	,cqExpiryDT date NOT NULL
	,cqNoExpiryDT bit NOT NULL
);
INSERT INTO #t
VALUES (1047, 1041, 'Welding License', '20141130', '20161130', 0)
	,(1046, 1040, 'Site Induction', '20150701', '20160903', 0)
	,(1099, 1040, 'Site Induction', '20120412', '20130412', 1);

One approach is to use the ROW_NUMBER() windowed function:

WITH OrderedRows
AS
(
	SELECT cqID, cqtID, cqtName, cqFromDT, cqExpiryDT, cqNoExpiryDT
		,ROW_NUMBER() OVER (PARTITION BY cqtID ORDER BY cqNoExpiryDT DESC, cqExpiryDT DESC) AS rn
	FROM #t
)
SELECT cqID, cqtID, cqtName, cqFromDT, cqExpiryDT, cqNoExpiryDT
FROM OrderedRows
WHERE rn = 1;
1 Like
4

Thanks for that Kristen and Ifor. I'm not as familair with the row_number() function but it works perfectly here.

Thanks again