Help with a datediff calc

Question

Post reply

Help with a datediff calc

Paul Morris-1011726

SSCommitted

Points: 1879
More actions
December 22, 2009 at 8:52 am

#207668

I am trying to get the datediff in days for multiple rows but getting NULL returns. I know where the issue is but have no idea how to correct.
Here is the code:
CREATE TABLE #DaysinStatus
(
casesk int,
pickdate datetime,
rownum int
)
INSERT INTO #DaysinStatus
SELECT case_sk, pick_date, ROW_NUMBER() OVER (ORDER BY cp.pick_date) AS row
FROM case_pick AS cp
WHERE (group_code = 'cstatu') AND (case_sk = 34568)
ORDER BY pick_date
SELECT pickdate, rownum, datediff(day,
(SELECT pickdate
FROM #DaysinStatus
WHERE #DaysinStatus.rownum = #DaysinStatus.rownum + 1),
pickdate) AS StatusDays
FROM #DaysinStatus AS d
Select * from #DaysinStatus
DROP TABLE #DaysinStatus
This is what the above code returns:
pickdate rownumStatusDays
NULL 1NULL
2009-01-28 00:00:00.0002NULL
2009-03-16 13:07:41.1073NULL
2009-06-16 14:07:46.1834NULL
I believe this part of the datediff is the issue:
WHERE #DaysinStatus.rownum = #DaysinStatus.rownum + 1)
Any help would be great...Thanks!

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Try changing your subquery from

SELECT pickdate

FROM #DaysinStatus

WHERE #DaysinStatus.rownum = #DaysinStatus.rownum + 1

to

SELECT a.pickdate

FROM #DaysinStatus a

WHERE #DaysinStatus.rownum = a.rownum + 1

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 2

thanks for the quick reply... but didn't work...

#DaysinStatus.rownum couldn't be bound...

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 3

I got it with the above help... Had to change #DaysinStatus.rownum to d.rownum

Thanks!!!

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 4

Dang thought I was done....

One more thing I need to reslove.

I have some "pickdates" from #DaysinStatus on the first row that may be NULL. How can I SET the value for NULLs to '112009'? I would need to plug it in here:

INSERT INTO #DaysinStatus

SELECT case_sk, pick_date, ROW_NUMBER() OVER (ORDER BY cp.pick_date) AS row

FROM case_pick AS cp

WHERE (group_code = 'cstatu') AND (case_sk = 34568)

ORDER BY pick_date

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Use ISNULL or COALESCE

SELECT case_sk, COALESCE(pick_date,'112009') AS pick_date, ROW_NUMBER() OVER (ORDER BY cp.pick_date) AS row

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 6

Thanks Mark.

had to make a change. Gave me an error:

Msg 242, Level 16, State 3, Line 9

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The statement has been terminated.

(0 row(s) affected)

So changed '112009' to '2009-01-1 00:00:00.000'. And it is reurning what I expected.

Thanks again for the quick responses and help!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

SELECT pickdate,

rownum,

datediff(day, nextd.pickdate, d.pickdate) AS StatusDays

FROM #DaysinStatus AS d

LEFT JOIN #DaysinStatus AS nextd ON nextd.rownum = d.rownum + 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 8

Thanks for all the help.... but seems management changed their minds... again... grrrr..

I modified the code a bit based on above so here is the current code:

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() OVER (ORDER BY cp.pick_date) AS row

FROM case_pick AS cp INNER JOIN

group_items AS gi ON cp.item_sk = gi.item_sk

WHERE (cp.group_code = 'cstatu') AND (case_sk = @case_sk)

ORDER BY pick_date

SELECT d.pickdate, d.status, d.rownum,

StatusDays =

CASE

WHEN d.rownum <> MAX(d.rownum) THEN DATEDIFF(day, d.pickdate, nextd.pickdate)

ELSE DATEDIFF(day, d.pickdate, GETDATE())

END

FROM #DaysinStatus AS d LEFT JOIN

#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1

The issue is that I need to also calculate the last row based against the day report is run... When I use max(rownum) with CASE I get the following error:

Column '#DaysinStatus.pickdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried GROUP BY but that didn't work....

As always any help would be great... thanks in advance!!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Hi Paul

Any chance of setting up some sample data for this? Say 12 rows, with just the columns which are referenced in your queries?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 10

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

Date Status Days in Status

1/1/2009New Case 27

1/28/2009Released 47

3/16/2009Treating 92

6/16/2009Submitted

What I need is to calc the last cell based on the day this is run (this is a sub report in Report Services). I have tried using CASE, WHILE and IF but can't get any to work (more likely due to my inexperience).

Let me know if that is enough.... might be able to make up some data if needed...

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 11

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() OVER (ORDER BY cp.pick_date) AS row

FROM case_pick AS cp INNER JOIN

group_items AS gi ON cp.item_sk = gi.item_sk

WHERE (cp.group_code = 'cstatu') AND (case_sk = @case_sk)

ORDER BY pick_date

SELECT d.pickdate, d.status, d.rownum, DATEDIFF(day, d.pickdate, nextd.pickdate) AS StatusDays

FROM #DaysinStatus AS d LEFT JOIN

#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 12

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 correctly to help you better help me? or how can I better provide the info/data?

Greg Snidow SSCoach Points: 16108 More actions · Answer 13

Paul Morris-1011726 (12/23/2009)
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 correctly to help you better help me? or how can I better provide the info/data?

Paul, since you asked... A couple of things I see without even trying to copy and paste anything in QA are 1) although you posted the code you used to create your temp table, and it might be meaningful to you, it references two objects, 'case_pick' and 'group_items', that are meaningless to the rest of us. Are they tables? Views? what is their structure? What is the data in them? 2) The scant sample data is not in readily consumable format. You said you read Jeff's article right? Now, go back and read the entire thread, and see if you think someone totally ignorant of your data structure would be able to provide an answer. What happens when you copy and paste the CREATE statement you provided? I guarantee if you follow Jeff's good advice, people will be able to help you. Now, I'm not meaning to pick on you, but you did ask. Also, go and look at some of the other posts and see which ones have had positive results.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 14

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 get the correct info together.

again thanks