While it is possible to do this without a cursor, the resulting query will be (IMHO) highly inefficient as the database grows. Here is a query that will return your answer:
select t.contractid, t.status, sum(datediff(d, t.fromdate, isnull(t.todate,getdate()))) days
select c1.contractid, c1.status, c1.moddate fromdate, c2.moddate todate
from DurationCalc c1
outer join DurationCalc c2 on (
c2.contractid = c1.contractid
and c2.id> c1.id
and not exists (
select 1 from DurationCalc c3
where c3.contractid = c1.contractid
and c3.id > c1.id
and c3.id < c2.id)
where t.status = ‘O’
group by t.contractid, t.status
The concepts behind this are:
1. internally, collapse the separate rows into rows where the temporary row has both the beginning and ending date of the status period – this will let us use the datediff function on the fields in the row.
2. We want the “todate” field to be the “next” date after the start of the period, so we need moddate to be greater than the c1.moddate, but use the “not exists” to ensure there are no other records that fall between these.
3. Once we have the temporary table “t” containing the start and end of each period, we can grab all the “O” periods and compute the date difference between the start and end.
4. Note that if the latest “O” has no end date (no subsequent record), we need to use “getdate()” to provide the pseudo-end date for the last period.
5. The “sum” and “group by” will total the days for each contractid when the contract id had a status of “O”.
Personally, I would DEFINITELY write this as a function with a cursor, which would be dozens (if not hundreds) of times more efficient than the above expression.
At the very least, make sure you have an index on ContractID. I would be inclined to create an index as:
Create Unique Index … on DurationCalc (ContractId, Id);
This would at least ensure the above expression is evaluated in the most efficient manner possible.