Count records that fall within a date range 1

[southbean]

Hello All,

I have a report based on a stored procedure that returns records of tasks that were completed the previous week. It’s run on Mondays and returns those tasks where the ‘completed_dt’ field has a date in the previous week.

WHERE completed_dt BETWEEN
DATEADD("dd",-7, GETDATE()) AND DATEADD("dd",-2, GETDATE())

Each task has a ‘created_dt’ as well. I want to be able to count the number of new tasks within this record set.

I’m using the following formula but get an error message the says ‘This field can not be summed’

If {sp_created_dt} >= Minimum({sp_completed_dt}) And 
{sp_created_dt} <= Maximum({sp_completed_dt}) Then 1 Else 0

Can anyone help me with this?

Thanks for any/all suggestions!

- TM

 

[Turkbear]

Hi,
Why max amd min for those dates?
What determines if a task is "new".






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

You've aleady limited the rows in the SP, so just use:

count({table.field})

Or right click any field in the details and select Insert->Summary->Count

-k

 

[southbean]

Hi Turkbear,

Thanks for your reply.

Tasks can be created (sp_created_dt) weeks or even months ago. This report returns those tasks that were completed (sp_completed_dt) in the previous week.

However, some tasks can be created and completed in the same week. So, I want to count those requests that are 'new', i.e. those created in the previous week by counting the completed tasks that also have a create date (sp_created_dt) that falls in the in the same - previous - week.

Thanks again!

- TM

 

[synapsevampire]

Try:

{sp_created_dt} in currentdate-7 to currentdate-2

Not sure why you use that range in the SP, but I duped it here.

-k

 

[synapsevampire]

I should have said, place that formula in the evaluate->use a formula part of a Running Total.

-k

 

[southbean]

K,

Bingo! That did it. Thanks VERY much!

- TM