May 29, 2014 at 11:24 am
Hi all, Im trying to create a stored procedure that Pulls in Chargeable and Non Chargeable hours for our employees however When I run the Stored Procedure I get this error "Conversion failed when converting date and/or time from character string." I am having a hard time figuring out were this is happening in the Stored Procedure, any help would be greatly appreciated. Also I would like to be able to Add a parameter that would be the StartDate and EndDate for which the stored procedure would pull time for.
ALTER PROCEDURE [dbo].[Chargeability]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE [dbo].[#Chargeability]
(
[Department] nvarchar(100),
[Employee Name] nvarchar(50),
[Work Type] nvarchar(100),
[Hours] numeric(4,2)
)
INSERT INTO [#Chargeability]
(
[Department] ,
[Employee Name],
[Work Type],
[Hours]
)
SELECT dbo.[Department Code].T1, dbo.EmpList.FirstLast, CASE WHEN dbo.TCDaily.Kind Like 'J%' THEN 'Chargeable' ELSE dbo.[Work Kind].LName END, dbo.TCDaily.Hrs
FROM dbo.[Work Kind] RIGHT OUTER JOIN
dbo.TCDaily ON dbo.[Work Kind].WorkKind = dbo.TCDaily.Kind LEFT OUTER JOIN
dbo.EmpList LEFT OUTER JOIN
dbo.[Department Code] ON dbo.EmpList.Role = dbo.[Department Code].LID ON dbo.TCDaily.TCDay = dbo.EmpList.EmpID
WHERE (dbo.TCDaily.Kind <> 'A02') OR (dbo.TCDaily.Kind <> 'J02') OR (dbo.TCDaily.Kind <> 'J04') OR (dbo.TCDaily.Kind <> 'J06')
SELECT *
FROM #Chargeability
DROP TABLE #Chargeability
END
May 29, 2014 at 11:37 am
Cannot say much without knowing more about the table structures, first guess would be this part of the join
😎
dbo.TCDaily.TCDay = dbo.EmpList.EmpID
May 29, 2014 at 11:44 am
SHIT! Thanks stupid stupid stupid me 🙂
May 29, 2014 at 11:56 am
npatel 17252 (5/29/2014)
SHIT! Thanks stupid stupid stupid me 🙂
:-DThat is how we learn!:-D
😎
May 29, 2014 at 1:04 pm
Might be another "learning opportunity", but I have to mention the following segment of the code:
WHERE (dbo.TCDaily.Kind <> 'A02') OR (dbo.TCDaily.Kind <> 'J02') OR (dbo.TCDaily.Kind <> 'J04') OR (dbo.TCDaily.Kind <> 'J06')
That WHERE clause isn't going to restrict the values of TCDaily.Kind in any way. I suspect that what you really want is:
WHERE dbo.TCDaily.Kind NOT IN ('A02', 'J02', 'J04', 'J06')
Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 29, 2014 at 1:15 pm
Why would that where clause not work? Ive used similar ones before :/
May 29, 2014 at 2:03 pm
npatel 17252 (5/29/2014)
Why would that where clause not work? Ive used similar ones before :/
Because it's a series of ORs. Your WHERE clause basically says that the Kind field can be NOT EQUAL to one value, OR, not equal to another value, OR ... Effectively, you don't have any restrictions on it's value at all. Think about how SQL will evaluate the entire expression. Any one of those individual not equal comparisons that results in TRUE for that comparison will result in the record getting through the filter. Thus, even if the value is one of the four listed, each of the four values listed would satisfy one of the other comparisons and the record would get through. If you had used AND, then I would have simply suggested an improved version of the clause to just use NOT IN instead of all the repeated not equals comparisons. Does that make sense?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 29, 2014 at 2:31 pm
So its because its going through a series of filters rather then determining if the value is or is not there.
May 29, 2014 at 3:51 pm
By using OR, you effectively create a series of filters for which ANY ONE comparison being TRUE results in the record passing through into the resultset. Had you used AND instead of OR, then ALL the conditions would need to be TRUE in order for the record to pass through. As that seemed to be the most likely desired result, and because there's a simpler way to write the same thing, I chose to recommend using the NOT IN methodology to achieve what would have been the same result.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 29, 2014 at 4:14 pm
In other words.
This:
WHERE (dbo.TCDaily.Kind <> 'A02') AND (dbo.TCDaily.Kind <> 'J02') AND (dbo.TCDaily.Kind <> 'J04') AND (dbo.TCDaily.Kind <> 'J06')
Is exactly the same as this:
WHERE NOT( (dbo.TCDaily.Kind = 'A02') OR (dbo.TCDaily.Kind = 'J02') OR (dbo.TCDaily.Kind = 'J04') OR (dbo.TCDaily.Kind = 'J06') )
Or this:
WHERE dbo.TCDaily.Kind NOT IN ('A02', 'J02', 'J04', 'J06')
But all of those are different from this:
WHERE (dbo.TCDaily.Kind <> 'A02') OR (dbo.TCDaily.Kind <> 'J02') OR (dbo.TCDaily.Kind <> 'J04') OR (dbo.TCDaily.Kind <> 'J06')
May 29, 2014 at 7:24 pm
Luis Cazares (5/29/2014)
In other words.This:
WHERE (dbo.TCDaily.Kind <> 'A02') AND (dbo.TCDaily.Kind <> 'J02') AND (dbo.TCDaily.Kind <> 'J04') AND (dbo.TCDaily.Kind <> 'J06')Is exactly the same as this:
WHERE NOT( (dbo.TCDaily.Kind = 'A02') OR (dbo.TCDaily.Kind = 'J02') OR (dbo.TCDaily.Kind = 'J04') OR (dbo.TCDaily.Kind = 'J06') )Or this:
WHERE dbo.TCDaily.Kind NOT IN ('A02', 'J02', 'J04', 'J06')But all of those are different from this:
WHERE (dbo.TCDaily.Kind <> 'A02') OR (dbo.TCDaily.Kind <> 'J02') OR (dbo.TCDaily.Kind <> 'J04') OR (dbo.TCDaily.Kind <> 'J06')
Yep... that's all correct. The first 3 WHERE clauses are functionally equivalent from the point of view of the resultset. They may differ in the resulting execution plan, although I'm not certain that the optimizer would miss out on making the plans the same.
The last WHERE clause is, effectively, no WHERE clause at all, and I suspect the optimizer will simply take it out of play.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2014 at 12:26 pm
Hi,
There are other ways to skin the cat, depending on whether performance is an issue. Using some kind of a an actual table, CTE, or derived table to store the excluded TCDaily.Kind values would allow you to make a LEFT OUTER JOIN which could benefit from an index on Kind if one exists. The alter method proposed yesterday would result in a Table Scan whereas the LEFT OUTER JOIN would result in a seek if the index is in place. If performance is not an issue, then use the suggestion made yesterday, otherwise consider some of the suggestions above.
May 30, 2014 at 12:40 pm
lorrin.ferdinand (5/30/2014)
Hi,There are other ways to skin the cat, depending on whether performance is an issue. Using some kind of a an actual table, CTE, or derived table to store the excluded TCDaily.Kind values would allow you to make a LEFT OUTER JOIN which could benefit from an index on Kind if one exists. The alter method proposed yesterday would result in a Table Scan whereas the LEFT OUTER JOIN would result in a seek if the index is in place. If performance is not an issue, then use the suggestion made yesterday, otherwise consider some of the suggestions above.
Do you have anything to support that?
With an index, NOT EXISTS should work better than a LEFT OUTER JOIN. Without using a physical table you'll get zero reads from your excluded values.
Unless you have a performance test that proves otherwise, here's some tests made by Gail Shaw
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply