Can't display proper data from two tables

Hi everyone:

I have 2 tables that have data in the format as below:

CREATE TABLE #ASSESSMENT(
ASSESSMENT_KEY NUMERIC(12,0) NULL,
COMPLETED_DATE DATETIME NULL)

A1      JULY 10TH
A2      JULY 11TH
A3      JUNE 6TH

CREATE TABLE #CONTROL_ASSESSMENT(
ASSESSMENT_KEY NUMERIC(12,0) NULL,
CONTROL_KEY NUMERIC(12,0) NULL)

A1      CTRL1
A2      CTRL2
A3      CTRL2
A3      CTRL3

I want to display the data from the two as below. Basically display only the values (labelled "correct" ) of assessment and control which have the latest date for a control.
 
A1      CTRL1      JULY10TH      <-- WRONG
A2      CTRL1      JULY11TH      <-- CORRECT
A1      CTRL2      JULY10TH      <-- CORRECT
A3      CTRL2      JUNE6TH      <-- WRONG
A3      CTRL3      JUNE6TH      <-- CORRECT

CREATE TABLE #LATEST_CONTROL_ASSESSMENT(
ASSESSMENT_KEY NUMERIC(12,0) NULL,
CONTROL_KEY NUMERIC(12,0) NULL,
COMPLETED_DATE DATETIME NULL)

I have desperately tried to get the information into the above table by grouping on control key but then I am unable to get the corresponding assessment key.

I tried this after:
UPDATE #LATEST_CONTROL_ASSESSMENT
SET ASSESSMENT_KEY=(
      SELECT AC1.ASSESSMENT_KEY
                                    FROM ASSESSMENT_CONTROL AC1, ASSESSMENT A1, #LATEST_CONTROL_ASSESSMENT LCA
                                    WHERE AC1.CONTROL_KEY=LCA.CONTROL_KEY AND
                                    A1.ASSESSMENT_KEY=AC1.ASSESSMENT_KEY AND A1.DATA_EFFECTIVE_KEY=8
AND A1.COMPLETED_DATE IS NOT NULL       AND A1.COMPLETED_DATE=LCA.COMPLETED_DATE                  
      GROUP BY AC1.ASSESSMENT_KEY
But I get this error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."

Would someone please advice?

Many thanks in advance.Start Free Trial