How do I calculate the datediff between multiple rows in a table when rowcount is unknown?

I am trying to calculate the days that a record has been in a current status.  I have a field that displays the change date when the status changes.  When there are multiple rows for an ID,  I will need to compare the amount of time that passed since the last change date.  When there is only 1 row for the ID, I would just compare the change date to the current date.  As you can see in the snippet, I am trying to also exclude weekends from the count.

Any assistance will be greatly appreciated.
DAYS_IN_STATUS = IF @@ROWCOUNT = 1 (SELECT DATEDIFF(dw,AU_TIME,GETDATE())-(DATEDIFF(wk,AU_TIME,GETDATE())*2) AS DAYS_IN_STATUS 
ELSE ??

Open in new window

testergirlAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mish33Connect With a Mentor Commented:
Fold multiple rows into one:

select id, min(au_time) from tbl group by id

and then use your formula.
0
All Courses

From novice to tech pro — start learning today.