muliple reoceds in a Case statement 2

[siituser]

I've got a fairly simple Case statement:

Select (Case
when(Select distinct TSR.QP
From TSR
where TSR.DateStart between Date1 and Date2 and TSR.isActive =1) = 0 then 0

when(Select distinct TimeSheetRaw.QuickPay
From TSR
where TSR.DateStart between Date1 and Date2 and TSR.isActive =1) = 1 then 1
end ) as QP

It has worked well up to this point - we have now found an instance where the inner Select statement can have 2 distinct records.

Is there a way to use the 'record count' of the select statment in a WHEN? logic: if the recordcount of the select > 1 then 1

or how else can I handle multiple recordsets in a case statement

 

[aco636]

How about
WHERE RecordID IN (SELECT RecordID, COUNT(*) FROM Table
GROUP BY RecordID HAVING COUNT > 1)

or

WHERE RecordID IN (SELECT MAX(RecordID))

REgards ACO

 

[siituser]

thanks - that was perfect!