SQL Help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Help needed

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2015-01-14 : 16:18:31

I have the following simple stored procedure:

ALTER PROCEDURE [dbo].[BobTest]
@StartDate datetime
AS

SELECT DISTINCT kc.EmpNum,
kc.Company,
kc.FirstName,
kc.LastName,
kc.Addr1,
kc.City,
kc.[State],
kc.Zip,
kc.SSN,
IsNull(CONVERT(int, st.Mon) +
CONVERT(int, st.Tue) +
CONVERT(int, st.Wed) +
CONVERT(int, st.Thu) +
CONVERT(int, st.Fri) +
CONVERT(int, st.Sat) +
CONVERT(int, st.Sun), '0')
As DaysWorked
FROM tKronMnthlyMostCurrData kc LEFT OUTER JOIN
Salespeople sp ON kc.EmpNum = sp.EmpNum LEFT OUTER JOIN
SalesOfficeTeams st ON sp.SalesOfficeID = st.SalesOfficeID
WHERE kc.EmpStat = 'A'
AND kc.MonthDate = @StartDate
AND ((kc.Company = '104'
AND NOT kc.[State] = 'WA')
OR kc.Company = '105')
AND sp.Active = 1
ORDER BY kc.EmpNum

This returns the rows I want. Now I want to calculate the hours worked for the last 12 months for each EmpNum. So I tried a subquery like this in my SELECT:

,(SELECT IsNull(cp.RegHours, 0) + IsNull(cp.OTHours, 0)
FROM CorporatePayroll cp
WHERE cp.CKDate BETWEEN '1/1/2014' AND '1/14/2015'
GROUP BY cp.EmpNum) AS HoursWorked

I then Execute this and no errors exist. But when I run the stored proc, I get this error:

Msg 512, Level 16, State 1, Procedure BobTest, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What am I doing wrong?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-14 : 17:28:13

Have you posted everything here? The thing is I don't see any sub queries that are preceded or followed by an equal sign or comparison

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-14 : 18:15:42

You're missing the WHERE condition to correlate the subquery to the main query:


SELECT ...,
...
,(SELECT IsNull(cp.RegHours, 0) + IsNull(cp.OTHours, 0) 
 FROM CorporatePayroll cp
 WHERE cp.CKDate BETWEEN '20140101' AND '20150114'
     AND cp.EmpNum = kc.EmpNum
 GROUP BY cp.EmpNum
 ) AS HoursWorked

bh0526
Yak Posting Veteran

71 Posts

Posted - 2015-01-15 : 08:06:11

I made the change like you suggested but I get the same error.

bh0526
Yak Posting Veteran

71 Posts

Posted - 2015-01-15 : 08:13:07

Sorry, my mistake. It did work. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources