calculate recency & absolute deviation for a dataset

General
N/A

calculate recency & absolute deviation for a dataset

Hi,

please check the following dataset

customerid  purchasedate 

1                      5/9/2013

1                      5/20/2013   

1                       5/29/2013

2                       6/8/2013

I need to calculate resultset like

"customerid"  "recency (max date - min date i.e. #days)"    "deviation from last purchase date in dataset"

1                     20 (5/29/2013 - 5/9/2013)                              30 (6/8/2013 - 5/9/2013)

I can't use max (date) here as it will always give me for the grouping column & not teh abslute max date. is there a way to achieve this ?

Tags (3)
2 REPLIES
N/A

Re: calculate recency & absolute deviation for a dataset

Nest the aggregate in an OLAP function:

SELECT
customerid,
MAX(purchasedate) - MIN(purchasedate),
MAX(MAX(purchasedate)) OVER () - MAX(purchasedate)
FROM tab
GROUP BY 1

Dieter

N/A

Re: calculate recency & absolute deviation for a dataset

Thanks Dieter,

this works perfect.  since I wanted to calculate deviation from absolute, I used -

MAX(MAX(purchasedate)) OVER () - MAX(purchasedate)

If I understand correct, the use of window function over() overrides and considers the entire dataset obtained from teh table tab, right ?

Thanks again for helping me out.