Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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

0
testergirl
Asked:
testergirl
1 Solution
 
mish33Commented:
Fold multiple rows into one:

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

and then use your formula.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now