SQL Server - Obtención de un Escalar y agrupmiento

Hola a todos.
Intento obtener un escalar(p.e. una fecha) desde un select on agrupamiento.SQL SERVER 2008 express no permite realizar esta operación
CREATE FUNCTION dbo.f1(@id smallint,@s bit)
returns date
AS
BEGIN
DECLARE @f date
SELECT TOP(1)@fecha=C. FECHA,CD.PRODUCTO FROM COMPRAS_DETALLES CD JOIN COMPRAS C ON C.LETRA=CD.LETRA AND C.NUMERO=CD.NUMERO WHERE C.S IN(SELECT * FROM Tipos_de_Moviimientos tm WHERE S=@s) WHERE CD.PRODUCTO=@ID
return @f
END
¿Cómo se resuelve?
Gracias por sus rtas.

DECLARE @f date
SELECT @fecha=C. FECHA FROM COMPRAS_DETALLES CD JOIN COMPRAS C ON C.LETRA=CD.LETRA AND C.NUMERO=CD.NUMERO WHERE C.S IN(SELECT * FROM Tipos_de_Moviimientos tm WHERE S=@s) WHERE CD.PRODUCTO=@ID

No estoy seguro si te funciona asi, o es necesario poner el top 1

SELECT @fecha= top 1 C. FECHA FROM COMPRAS_DETALLES CD JOIN COMPRAS C ON C.LETRA=CD.LETRA AND C.NUMERO=CD.NUMERO WHERE C.S IN(SELECT * FROM Tipos_de_Moviimientos tm WHERE S=@s) WHERE CD.PRODUCTO=@ID

No tengo sql server en este equipo entonces no lo puedo probar

Saludos

para hacerlo con top 1 debe ser con SET en vez de select

SET @fecha= (select top 1 C. FECHA FROM COMPRAS_DETALLES CD JOIN COMPRAS C ON C.LETRA=CD.LETRA AND C.NUMERO=CD.NUMERO WHERE C.S IN(SELECT * FROM Tipos_de_Moviimientos tm WHERE S=@s) WHERE CD.PRODUCTO=@ID)

Lo que estoy 100% seguro que no va a funcionar es:

WHERE C.S IN(SELECT * FROM Tipos_de_Moviimientos tm WHERE S=@s)

¿Porque?, es muy simple, el IN hace una comparacion contra 1 valor (1 columna).

En todo caso habria que cambiarlo por un EXISTS()