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