2

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
  • 1
    What exactly is this query trying to do? I'm sure there's a less convoluted way – ypercubeᵀᴹ Feb 9 '17 at 11:46
  • @ypercube - I want to show amtpaid for only one employee. Does that make more sense? – StarsFlyFree FromCozyNights Feb 9 '17 at 13:13
  • 1
    Not really. Can you edit the question and add the expected output? – ypercubeᵀᴹ Feb 9 '17 at 13:16
  • @ypercubeᵀᴹ - see edit, I added desired output. I want paid to only be updated for one employee name. Not paid showed multiple times for each employee name. – StarsFlyFree FromCozyNights Feb 9 '17 at 13:24
2

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 ;
  • How would i handle if @Two has more than one row per employee? – StarsFlyFree FromCozyNights Feb 9 '17 at 13:46
  • 1
    and you want the sum to appear in one row, as before? – ypercubeᵀᴹ Feb 9 '17 at 13:53
  • Yes - still only one row showing the sum. – StarsFlyFree FromCozyNights Feb 9 '17 at 13:54
  • Is it quicker to use a Outer Apply than using a sub-query? – StarsFlyFree FromCozyNights Feb 9 '17 at 14:06
  • 2
    It's similar. We could keep the LEFT JOIN and add a GROUP BY (instead of the OUTER APPLY). – ypercubeᵀᴹ Feb 9 '17 at 14:07
0

Change your query to like below. I assumed you are trying to add amtpaid value of #Two to Minimum of eod record of #ONE employee

SELECT  a.employee
    ,[EOD] = A.eod 
    ,[Paid] = CASE A.EOD
        WHEN (
                SELECT MIN(A1.EOD)
                FROM #ONE A1
                WHERE A1.employee = A.employee
                )
            THEN (
                    SELECT SUM(B2.amtpaid)
                    FROM #Two B2
                    WHERE A.employee = B2.employee
                    )

    ELSE 0 END
FROM #ONE A

The result will be

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.