DECLARE @ID INT
SET @ID = 12345

DECLARE @Field1 VARCHAR(MAX)
DECLARE @Field2 VARCHAR(MAX)
DECLARE @Field3 VARCHAR(MAX)



SELECT @Field1 = Field1,@Field2 = Field2 FROM Table1 WHERE ID = @ID

    SELECT CONVERT(VARCHAR(8),T.Date,112) AS Date,SUM(T.Test1) AS Test1,SUM(T.Test2) AS Test2,COUNT(DISTINCT T.Test3) AS Test3
    FROM (
        SELECT Date,CONVERT(INT,1) AS Test1,CONVERT(INT,0) AS Test2,t2.Test3
        FROM Table2 t2
        INNER JOIN (SELECT DISTINCT Test3
                FROM Table2 t22
                WHERE CASE WHEN LEN(@Field1) > 0 THEN t22.Field1 ELSE t22.Field2 END =
                CASE WHEN LEN(@Field1) > 0 THEN @Field1 ELSE @Field2 END) t ON t2.Test3 = t22.Test3
        UNION ALL
        SELECT Date,CONVERT(INT,1) AS Test1,CONVERT(INT,0) AS Test2,t3.Test3
        FROM Table3 t3
        INNER JOIN (SELECT DISTINCT Test3
                FROM Table3 t33
                WHERE CASE WHEN LEN(@Field1) > 0 THEN t33.Field1 ELSE t33.Field2 END =
                CASE WHEN LEN(@Field1) > 0 THEN @Field1 ELSE @Field2 END) t ON t3.Test3 = t33.Test3
        ) T
    GROUP BY CONVERT(VARCHAR(8),T.Date,112)
    ORDER BY CONVERT(VARCHAR(8),T.Date,112) DESC