Determine how old an item is.

[tqeonline]

So i have been given the task of "Defect Aging" basically telling how old a defect is.

So say you are given:

Defect DateOpened
1 10/1/2008
2 10/2/2008
3 10/3/2008
4 10/4/2008
5 10/5/2008
6 10/6/2008

I need the data to be returned like this:

Defect DateOpened DaysOld
1 10/1/2008 5
2 10/2/2008 4
3 10/3/2008 3
4 10/4/2008 2
5 10/5/2008 1
6 10/6/2008 0

What this intern does is creates a chart that has defect again.

Would it just be a datediff on the date and now?



- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

SELECT [BG_DETECTED_IN_RCYC]
	  ,[BG_BUG_ID]
      ,[BG_STATUS]
	  ,[BG_DETECTION_DATE]
	  ,DateDiff(Day, [BG_DETECTION_DATE],GetDate()) as Age
FROM [default_bsd_qa_db].[dbo].[BUG]

This does it... Simple!

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[markros]

Try

select Defect, DateOpened, datediff(day,DateOpened, max(DateOpened) over ()) as DaysOld

(or try to do in two steps if you're using SQL Server 2000 by first getting max(DateOpened) into a variable.)

PluralSight Learning Library