How to calculate the total days between open and close date

Hi All,

I have a table call case and case_status have two fields, date and status as below:

date status

04/01/2006 open

04/05/2006 closed

04/10/2006 open

04/15/2006 closed

Whenever i open and closed the case, one record is insert into the case_status table.

Now I would need to calculate the total days of the case in storeprocedure.

Anyone can help me please.

Aung

This articledoes something similar. check if it helps.|||

One try:

CREATE

PROCEDURE [dbo].[caseDays]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;RETURN(SELECTSUM(datediff( dd, c.openDate, d.closedDate))as myCaseDateFROM(SELECT a.cDateas openDate, row_NUmber()over(ORDERBY a.cDate)as ROWNUMBERFROM case_statusAS aWHERE(a.status='open'))as c

inner

join(SELECT b.cDateas closedDate, row_NUmber()over(ORDERBY b.cDate)as ROWNUMBERFROM case_statusAS bWHERE(b.status='closed'))as dON c.ROWNUMBER=d.ROWNUMBER)

END

I hope this one will be close to your solution.

Limno

|||

Thanks for your responsed.

But my problem is total days in two date between open and closed. I still facing this problem.

Thanks

Aung

|||

Hello:

datediff( dd,openDate,closedDate)

This function will give you how many days between open and closed days.

If this is not what you want, give a little more details about your problem.

Limno

|||Wouldn't the table also need a CaseID field so you know what case was being opened and closed?