7 Replies Latest reply: Feb 18, 2013 12:50 PM by A Sheppard RSS

    Calculated Dimension or Expression or Script to show previous date to current date

      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.

        • Re: Calculated Dimension or Expression or Script to show previous date to current date
          Gysbert Wassenaar

          max({<ChangeType={'Name'}>} ChangeDate) will get you the last change. If you want the change before that try max({<ChangeType={'Name'}>} ChangeDate,2)

          • Re: Calculated Dimension or Expression or Script to show previous date to current date

            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.

            • Re: Calculated Dimension or Expression or Script to show previous date to current date

              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