COUNT DAYDIFF > required value 1

[Windy77]

Hi,

I need to calculate average response times in days (from the date a letter is posted to when a reply is received). I use the following to get the total days difference, and when output to Excel with the count of letters sent it works just fine.

DECLARE @DAYDIFF INT

SET @DAYDIFF = (SELECT SUM(DATEDIFF(day, kf.ReplyReceived, kf.InstructionSent))
FROM Reports1.dbo.KeyFields kf
WHERE Canx2 IS NULL)

My problem is that I need to also calculate (again within Excel) what percentage of the letters were over 14 days between send & receive. So I need to use COUNT to give me the number over 14 days DATEDIFF. I have tried the following (but it isn't right, and I am struggling to fix it) :

DECLARE @DAYDIFF INT
DECLARE @COUNTGT14 INT

SET @COUNTGT14 = (SELECT (COUNT @DAYDIFF = DATEDIFF(day, kf.ReplyReceived, kf.InstructionSent)
FROM Reports1.dbo.KeyFields kf
WHERE Canx2 IS NULL
AND @DAYDIFF > 14)

Suggestions would be appreciated.
Thanks,
Paul

 

[tlbroadbent]

Try this.

DECLARE @COUNTGT14 INT

SELECT @COUNTGT14 = COUNT(*)
FROM Reports1.dbo.KeyFields
WHERE Canx2 IS NULL
AND DATEDIFF(day, ReplyReceived, InstructionSent)>14

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Windy77]

Thanks Terry,

That looks like it makes a lot of sense. I'm between meetings right now and am in the UK so finish for the weekend soon. I'll check it on Monday and let you know how it goes, although as I said before it looks a lot cleaner than the way I was trying to approach this.

The solution to every problem is a lesson learned.

Thanks again & have a good weekend,

Paul

 

[tlbroadbent]

I like to keep it simple whenever possible.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Windy77]

This worked just fine, thanks. Sorry it took a while to get back to you, "changing business priorities" have altered my project priorities over the last few weeks. If only we could get on with what we have to do without the users & without the interfering management !¬)