Posted by bobc on 10/02/07 21:09

CORRECTION: syntax of the case statement was wrong in last post.
Shoud be...

CREATE PROCEDURE GetLeadsByStatusAge
@CommCode char(3),
@Result varchar(255) OUTPUT

AS

SELECT @Result =

(SELECT
ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d1 AND
@d2-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d2 AND
@d3-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d3 AND
@d4-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) BETWEEN @d4 AND
@d5-1
THEN 1 ELSE 0 END))) + '.' +

ltrim(str(SUM(CASE WHEN
DATEDIFF(day, statuschangedate, CURRENT_TIMESTAMP) >= @d5
THEN 1 ELSE 0 END))) + '.'


FROM prospects
WHERE CommunityCode = @CommCode
AND prospectstatus='L'
)

GO

• Next in forum: replication or mirroring
• Prev in forum: Re: SQL Configuration - RAM
• Thread view: Re: I want to parse @ArrayOfDays into @d1 through @d5

[Reply to this message]