Prev: Help with duplicate fields
Next: Tom and Plamen...
From: DavidC on 3 Mar 2010 15:59
I have a stored procedure where I want to not allow an ActivityCode = 64 more
than once in a month for any one person (PeopleLinkID). Below is my IF
condition that is not working correctly. Also, I think it is sometimes
returning an error "Subquery returned more than 1 value. This is not
permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery
is used as an expression."

Any help is appreciated.

IF @ActivityCode = 64 AND
NOT EXISTS(SELECT dbo.ActivityHistory.PeopleLinkID
FROM dbo.ClientCaseNotes INNER JOIN
dbo.ActivityHistory ON dbo.ClientCaseNotes.ActivityID =
dbo.ActivityHistory.ActivityID
WHERE (dbo.ActivityHistory.ActivityCode = 64)
GROUP BY dbo.ActivityHistory.PeopleLinkID,
MONTH(dbo.ActivityHistory.ActivityDate),
YEAR(dbo.ActivityHistory.ActivityDate)
HAVING (dbo.ActivityHistory.PeopleLinkID = @PeopleLinkID)
AND (YEAR(dbo.ActivityHistory.ActivityDate) = YEAR(@ActivityDate))
AND (MONTH(dbo.ActivityHistory.ActivityDate) = MONTH(@ActivityDate)))

--
David
From: Bob McClellan on 3 Mar 2010 20:08
...not much to go on David but give this a shot...
IF @ActivityCode = 64
AND NOT EXISTS(

SELECT dbo.ActivityHistory.PeopleLinkID

FROM dbo.ClientCaseNotes
INNER JOIN dbo.ActivityHistory ON dbo.ClientCaseNotes.ActivityID =
dbo.ActivityHistory.ActivityID

WHERE dbo.ActivityHistory.ActivityCode = 64
and dbo.ActivityHistory.PeopleLinkID = @PeopleLinkID
AND YEAR(dbo.ActivityHistory.ActivityDate) = YEAR(@ActivityDate)
AND MONTH(dbo.ActivityHistory.ActivityDate) = MONTH(@ActivityDate)

GROUP BY dbo.ActivityHistory.PeopleLinkID,
MONTH(dbo.ActivityHistory.ActivityDate),
YEAR(dbo.ActivityHistory.ActivityDate)


)
"DavidC" <dlchase(a)lifetimeinc.com> wrote in message
news:C4AEF74C-6283-4D89-9D6B-FD6E7E8A7CD3(a)microsoft.com...
>I have a stored procedure where I want to not allow an ActivityCode = 64
>more
> than once in a month for any one person (PeopleLinkID). Below is my IF
> condition that is not working correctly. Also, I think it is sometimes
> returning an error "Subquery returned more than 1 value. This is not
> permitted when the subquery follows =, !=, <, <= , >, >= or when the
> subquery
> is used as an expression."
>
> Any help is appreciated.
>
> IF @ActivityCode = 64 AND
> NOT EXISTS(SELECT dbo.ActivityHistory.PeopleLinkID
> FROM dbo.ClientCaseNotes INNER JOIN
> dbo.ActivityHistory ON dbo.ClientCaseNotes.ActivityID =
> dbo.ActivityHistory.ActivityID
> WHERE (dbo.ActivityHistory.ActivityCode = 64)
> GROUP BY dbo.ActivityHistory.PeopleLinkID,
> MONTH(dbo.ActivityHistory.ActivityDate),
> YEAR(dbo.ActivityHistory.ActivityDate)
> HAVING (dbo.ActivityHistory.PeopleLinkID = @PeopleLinkID)
> AND (YEAR(dbo.ActivityHistory.ActivityDate) = YEAR(@ActivityDate))
> AND (MONTH(dbo.ActivityHistory.ActivityDate) = MONTH(@ActivityDate)))
>
> --
> David

From: Plamen Ratchev on 3 Mar 2010 21:54
Try something like this:

IF NOT EXISTS(
SELECT *
FROM dbo.ClientCaseNotes AS C
INNER JOIN dbo.ActivityHistory AS H
ON C.ActivityID = H.ActivityID
WHERE H.ActivityCode = 64
AND H.PeopleLinkID = @PeopleLinkID
AND H.ActivityDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @ActivityDate), 0)
AND H.ActivityDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @ActivityDate) + 1, 0)
GROUP BY CONVERT(CHAR(6), H.ActivityDate, 112)
HAVING COUNT(*) > 1)

--
Plamen Ratchev
http://www.SQLStudio.com
From: DavidC on 4 Mar 2010 10:03
Plamen,
Just curious why you used DATEADD and DATEDIFF combinations rather than the
YEAR and MONTH functions? Is your example faster or?? Thanks.
--
David


"Plamen Ratchev" wrote:

> Try something like this:
>
> IF NOT EXISTS(
> SELECT *
> FROM dbo.ClientCaseNotes AS C
> INNER JOIN dbo.ActivityHistory AS H
> ON C.ActivityID = H.ActivityID
> WHERE H.ActivityCode = 64
> AND H.PeopleLinkID = @PeopleLinkID
> AND H.ActivityDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @ActivityDate), 0)
> AND H.ActivityDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, @ActivityDate) + 1, 0)
> GROUP BY CONVERT(CHAR(6), H.ActivityDate, 112)
> HAVING COUNT(*) > 1)
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Plamen Ratchev on 4 Mar 2010 10:17
The predicates that I used will allow you to utilize efficiently any indexes on the ActivityDate column. If you use the
YEAR and MONTH functions on the column you will not be able to utilize indexes to seek.

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Help with duplicate fields
Next: Tom and Plamen...