SQL Server 2008 R2 SubQuery Error – How to Overcome More Than 1 Result

sql serversql-server-2008-r2subqueryt-sql

I found this syntax already answered to a diff question, but I am getting the error of

More than 1 result returned

what do I need to alter in my syntax in order to defeat this error?

Declare @ONE Table
(
    employee varchar(250)
    ,eod int
)

Declare @Two Table
(
    employee varchar(250)
    ,amtpaid decimal(10,2)
)

Insert Into @One (employee, eod) VALUES
('Frank',   '1122')
,('Frank',  '1188')
,('Bill',   '1100')
,('Larry', '1199')
,('Bill',   '2211')
,('Jerry',  '3311')
,('Jerry',  '3322')
,('Stan',   '3388')
,('Stan',   '3300')
,('Larry',  '3377')

INSERT INTO @Two (employee, amtpaid) VALUES
('Frank', '11.11'),('Bill', '10.10')
,('Larry', '44.44'),('Jerry', '20.32')
,('Stan', '100.33')

SELECT a.employee
    ,[EOD] = b.eod
    ,[Paid] = CASE 
        WHEN b.eod = (
                SELECT min(b.eod)
                FROM @Two
                WHERE b.employee=a.employee
                )
            THEN COALESCE((
                        SELECT TOP 1 SUM(ISNULL(amtpaid, 0))
                        FROM @Two b
                        WHERE a.employee = b.employee
                        ), 0)
        ELSE 0
        END
FROM @One b
INNER JOIN @Two a
ON a.employee = b.employee
GROUP BY a.employee, b.eod, b.employee
ORDER BY a.employee ASC

EDIT
My desired output is like below – amtpaid showing for only 1 employeename not all of them

Employee    EOD Paid
Bill    1100    10.10
Bill    2211    0.00
Frank   1122    11.11
Frank   1188    0.00
Jerry   3311    20.32
Jerry   3322    0.00
Larry   1199    44.44
Larry   3377    0.00
Stan    3388    0.00
Stan    3300    100.33

Best Answer

No GROUP BY, no subqueries needed. The answer assumes that @Two has only one row per employee:

SELECT 
    a.employee,
    [EOD] = a.eod,
    [Paid] = CASE WHEN a.eod = MIN(a.eod) OVER (PARTITION BY a.employee)
                 THEN b.amtpaid
                 ELSE 0
             END
FROM 
    @One a
    JOIN @Two b
    ON  a.employee = b.employee ;

Another way would be to use window functions and a left join:

SELECT 
    a.employee,
    [EOD] = a.eod,
    [Paid] = COALESCE(b.amtpaid, 0)
FROM 
    ( SELECT a.employee, a.eod,
             rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                     ORDER BY a.eod)
      FROM @One AS a
    ) AS a
    LEFT JOIN @Two b
    ON  a.employee = b.employee 
    AND a.rn = 1 ;

which would be easier to modify if @Two has more than one rows per employee and we want the sum:

SELECT 
    a.employee,
    [EOD] = a.eod,
    [Paid] = COALESCE(b.amtpaid, 0)
FROM 
    ( SELECT a.employee, a.eod,
             rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                     ORDER BY a.eod)
      FROM @One AS a
    ) AS a
    OUTER APPLY
    ( SELECT amtpaid = SUM(b.amtpaid)
      FROM @Two b
      WHERE a.employee = b.employee 
        AND a.rn = 1
    ) AS b ;
Related Question