case statements returnin duplicate results.

[NickyJay]

Hi all,

in the SQL for my report i have used several case statements to display specific results as Y or N in 5 different fields.

My problem is that where a record has a yes in more than one of the fields, that record is output twice or more according to the number of Y's. the reuslts are correctly displaying as Y in the relevant rows, ie, record 1, Y Y N
but are being output as
Record 1 Y Y N
Record 1 Y Y N.

SELECT
		CLIENTS.Client_Ref, 
        CASE 
			WHEN (SELECT COUNT(*) FROM HISTORY WHERE HistoryType_Ref = 'F' AND HistoryCategory_Ref = 'L' AND DateEnd IS NULL AND HISTORY.Client_Ref = CLIENTS.Client_Ref) > 0 THEN 'Y' ELSE 'N' 
        END AS LDDStatement, 
        CASE 
			WHEN (SELECT COUNT(*) FROM HISTORY WHERE HistoryType_Ref = 'F' AND HistoryCategory_Ref = '1' AND DateEnd IS NULL AND HISTORY.Client_Ref = CLIENTS.Client_Ref) > 0 THEN 'Y' ELSE 'N' 
        END AS LDDNoStatement, 
        CASE 
			WHEN (SELECT COUNT(*)FROM CHISTORY WHERE HistoryType_Ref = 'F' AND HistoryCategory_Ref = '3' AND DateEnd IS NULL AND HISTORY.Client_Ref = CLIENTS.Client_Ref) > 0 THEN 'Y' ELSE 'N' 
		END AS SchoolAction, 
		CASE 
			WHEN (SELECT COUNT(*) FROM HISTORY WHERE HistoryType_Ref = 'F' AND HistoryCategory_Ref = 'M' AND DateEnd IS NULL AND HISTORY.Client_Ref = CLIENTS.Client_Ref) > 0 THEN 'Y' ELSE 'N' 
		END AS SchoolActionPlus, 
		CASE 
			WHEN (SELECT COUNT(*) FROM HISTORY WHERE HistoryType_Ref = 'N' AND DateEnd IS NULL AND HISTORY.Client_Ref = CLIENTS.Client_Ref) > 0 THEN 'Y' ELSE 'N' 
		END AS Sect139aCompleted
FROM CLIENTS

I don't really want to combine the case statments into one as i really need to output my results as a matrix of ticks where a Y is the result of each case.

Please help!

Many thanks Nicola

 

[Crowley16]

this should probably go into the microsoft sql server: programming thread...

anyway, have you considered using a distinct? i.e.

select distinct
... rest of your select statement

--------------------
Procrastinate Now!