Looks good... let me play with it to see for sure.

I do have a question, so as to understand this better... What is happening in this piece of code:

;WITH years...

1999 will be the farthest back I need to go. That is in stone. The plan is to set...

I am in the process of modifiying to protect the innocent 🙂

I could scale back but there might be a need for more test data.... I will start with 25 rows and if more is needed, I will just have to...

hmmm seems I am the one that is missing the obvious....

not sure what I did last week but the ISNULL with GETDATE() does work.

Sorry for all the confusion......

I removed the ISNULL and GETDATE() part of the code since it wasn't working the way I needed.

SELECT

d.pickdate,

d.status,

d.rownum,

DATEDIFF(day, d.pickdate, ISNULL(nextd.pickdate,GETDATE())) AS StatusDays ...

OK. Took some time this morning to add a few more rows of test data. Below is the test data, creating the test data tables and the code I...

No rush on this... Like i said this is just for extra credit 🙂

I will put together something Monday.

Maybe I am not being clear... I will be more so...

What I need it to do is return the datediff(day, <last row pickdate>, getdate()) for the last cell.

What yours returns is the datediff between the first row pickdate and getdate...

LOL must have posted within a few seconds of you....

appreciate you trolling here... not that it is a big deal. This is more for 'extra credit' for the powers that...

OK let me give the big picture: This is for a subreport that will return the days a case is in a particular status with the final number of days...

Thanks Greg for the feedback... Constructive criticism is always welcome. I am still new to this and I find the folks here very helpful. Let me see if I can...

hmmm ... I was reading Jeff Moden's article about Forum Etiquette: How to post data/code on a forum to get the best help and was wondering, am providing the information...

opps should add I used this code to get the above data....

CREATE TABLE #DaysinStatus

(

casesk int,

pickdate datetime,

status varchar(30),

rownum int

)

INSERT INTO #DaysinStatus

SELECT case_sk, COALESCE(pick_date, '2009-01-1 00:00:00.000'), gi.description,

ROW_NUMBER()...

Typically this only returns 2 to 6 rows but here is sample:

Date Status ...