Thank you Gysbert,
I think I am doing something wrong because I keep getting "// Error in calculated dimension"; here is exactly what I typed:
I also tried
What am I missing?
The actual fields are:
ReviewID : (bigint)
ReviewHistory.ChangeTypeID : (varchar(5))
ReviewHistory.ChangeDate : (datetime)
ReviewID : 58432
ReviewHistory.ChangeTypeID : EMAIL
ReviewHistory.ChangeDate : 2011-09-24 00:00:00.000
The problem is you're trying to use it as a calculated dimension instead of as an expression. I assumed you wanted to know the last (or before last) date of a name change. But is what you're really trying to do is calculate DaysSinceLastChange? That's probably easier calculated in the script. Something like this:
ReviewHistory.ChangeTypeID as ChType,
ReviewHistory.ChangeDate as ChDate
if(ReviewID&ChType=previous(ReviewID&ChType),interval(ChDate-previous(ChDate))) as DaysSinceLastChange
resident Temp order by ReviewID,ChType,ChDate;
Drop Table Temp;
I hope all the parentheses match....
Thank you very much; the script works. However, it is returning a date/time value not a count of days. When I use Num() I get a long decimal number; when I use Num#() I get a hour value. When I use 'dd' in the interval step it returns 0, even when it has been several days. I am going to have to do some further research; but I think it may have to do with using a DENSE_RANK() type of command on the SQL side to produce a better view to report off of.
If I don't get it figured out by tomorrow; I'll mark the question as answered as you did give me several options to try.
Thank you for your help Gysbert. I am taking another route because I couldn't get this to work properly; not from your instruction, but from the dataset. There are some hidden factors that I need to consider, otherwise I get recursive joins and bad data.
However, I will be using your techniques for other things in the future.
For anyone who was curios; I am going to be using a view based on the results from the following query; which is where I got a dataset to model after:
DROP TABLE #_Temp1
;WITH Changes_CTE AS
SELECT ReviewID, [ReviewHistory.ChangeTypeID], [ReviewHistory.ChangeDate],
ROW_NUMBER() OVER (PARTITION BY ReviewID,[ReviewHistory.ChangeTypeID] ORDER BY [ReviewHistory.ChangeDate]) AS SEQUENCE
SELECT b1.ReviewID, b1.[ReviewHistory.ChangeTypeID], b1.[ReviewHistory.ChangeDate],b1.SEQUENCE
FROM Changes_CTE b1
LEFT OUTER JOIN Changes_CTE b2 ON b1.ReviewID = b2.ReviewID
AND b1.Sequence = b2.Sequence + 1
WHERE (b1.ReviewID <> b2.ReviewID
OR b1.[ReviewHistory.ChangeTypeID] <> b2.[ReviewHistory.ChangeTypeID]
OR b2.[ReviewHistory.ChangeTypeID] IS NULL)
b1.ReviewID, b1.[ReviewHistory.ChangeTypeID], b1.[ReviewHistory.ChangeDate],b1.SEQUENCE
ORDER BY b1.[ReviewHistory.ChangeDate] DESC
T2.[ReviewHistory.ChangeDate] as PriorChangeDate,
DATEDIFF(dd,T1.[ReviewHistory.ChangeDate],T2.[ReviewHistory.ChangeDate]) as DayDiffFromPriorChange
FROM #_Temp1 T1
INNER JOIN (SELECT * FROM #_Temp1) T2
T1.ReviewID = T2.ReviewID
AND T1.[ReviewHistory.ChangeTypeID] = T2.[ReviewHistory.ChangeTypeID]
T2.Sequence = T1.Sequence - 1