Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have a report request to give the difference in days where the report will show how many days it has been since the last event.
Example:
ID Name ChangeDate ChangeType DaysSinceLastChange
99 John 01/01/2011 Name 365
99 John 01/14/2011 Address 364
99 John 01/01/2010 Name 365
99 John 01/15/2010 Address 379
99 John 01/01/2009 Name 0
99 John 01/01/2009 Address 0
In other words, I need to know when the last time the ChangeType of "Name" changed before the current record I am on.
I am not sure how to approach this in the most effective way, and am open to suggestions.
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:
Temp:
Load
ReviewID,
ReviewHistory.ChangeTypeID as ChType,
ReviewHistory.ChangeDate as ChDate
from ...mysource...;
Result:
Load
ReviewID,
ChType,
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....
max({<ChangeType={'Name'}>} ChangeDate) will get you the last change. If you want the change before that try max({<ChangeType={'Name'}>} ChangeDate,2)
Thank you Gysbert,
I think I am doing something wrong because I keep getting "// Error in calculated dimension"; here is exactly what I typed:
=max({<ReviewHistory.ChangeTypeID={'EMAIL'}>}ReviewHistory.ChangeDate)
I also tried
=max({<[ReviewHistory.ChangeTypeID]={'EMAIL'}>}[ReviewHistory.ChangeDate])
What am I missing?
The actual fields are:
ReviewID : (bigint)
ReviewHistory.ChangeTypeID : (varchar(5))
ReviewHistory.ChangeDate : (datetime)
Ex. values:
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:
Temp:
Load
ReviewID,
ReviewHistory.ChangeTypeID as ChType,
ReviewHistory.ChangeDate as ChDate
from ...mysource...;
Result:
Load
ReviewID,
ChType,
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.
The interval should work, but if you try floor(ChDate-previous(ChDate)) or floor(ChDate)-floor(previous(ChDate))
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
FROM {ReviewHistoryView}
)
SELECT b1.ReviewID, b1.[ReviewHistory.ChangeTypeID], b1.[ReviewHistory.ChangeDate],b1.SEQUENCE
INTO #_Temp1
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)
GROUP BY
b1.ReviewID, b1.[ReviewHistory.ChangeTypeID], b1.[ReviewHistory.ChangeDate],b1.SEQUENCE
ORDER BY b1.[ReviewHistory.ChangeDate] DESC
;
SELECT t1.ReviewID,T1.[ReviewHistory.ChangeTypeID],
T2.[ReviewHistory.ChangeDate] as PriorChangeDate,
DATEDIFF(dd,T1.[ReviewHistory.ChangeDate],T2.[ReviewHistory.ChangeDate]) as DayDiffFromPriorChange
FROM #_Temp1 T1
INNER JOIN (SELECT * FROM #_Temp1) T2
ON
T1.ReviewID = T2.ReviewID
AND T1.[ReviewHistory.ChangeTypeID] = T2.[ReviewHistory.ChangeTypeID]
WHERE
T2.Sequence = T1.Sequence - 1
ORDER BY
T1.[ReviewHistory.ChangeTypeID],T1.[ReviewHistory.ChangeDate] desc