Solved

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

Posted on 2008-10-10
1
293 Views
Last Modified: 2012-05-05
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
Comment
Question by:testergirl
1 Comment
 
LVL 15

Accepted Solution

by:
mish33 earned 500 total points
ID: 22688280
Fold multiple rows into one:

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

and then use your formula.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 2 43
MySQL left join performance 4 30
Can Unique column have more than one Null? 8 43
Updating a table from a temp table 4 25
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
A short film showing how OnPage and Connectwise integration works.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now