December 28, 2009 at 2:47 pm
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... Thanks for your help!!
For those interested here is the final code:
IF OBJECT_ID('TempDB..#DaysinStatus','u') IS NOT NULL
DROP TABLE #DaysinStatus
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 = 34568)
ORDER BY pick_date
SELECT d.pickdate, d.status, d.rownum, DATEDIFF(day, d.pickdate, ISNULL(nextd.pickdate,GETDATE())) AS StatusDays
FROM #DaysinStatus AS d LEFT JOIN
#DaysinStatus as nextd ON nextd.rownum = d.rownum + 1
Guess my only other question is, Does SSRS drop temp tables after execution or do I need to add the following:
DROP TABLE #DaysinStatus
December 28, 2009 at 3:18 pm
Paul Morris-1011726 (12/28/2009)
Guess my only other question is, Does SSRS drop temp tables after execution or do I need to add the following:DROP TABLE #DaysinStatus
It will be dropped when your current session ends.
And thanks for clearing the mud. I was beginning to think I need to find a new hobby.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply