Average between 3 direct dates in sqlserver bank?

Question

What is the most efficient way to calculate the average of dates in the same column by the sqlserver database? I need to take the date of the last 3 sales of a customer, and return in days to know if that customer buys, for example, every 30 days, 60 in 60 etc.

I did the following query:

SELECT TOP 3 data 
FROM dbo.tab_venda ven INNER JOIN 
dbo.tab_item_venda iven ON iven.num_venda = ven.num_venda 
WHERE ven.cod_pessoa = 737 AND iven.cod_produto = 2111 
ORDER BY ven.data DESC 

who returns me:

2015-08-10 00:00:00.000 
2015-07-10 00:00:00.000 
2015-06-03 00:00:00.000

Answer 1

The SQL below performs the requested operation. It averages days between the dates of the last three purchases, called here A,B,C. Being A the oldest of the three and C the most recent of the three.

select
    client_report_id,   
    datediff(dd,A,B) DIFF_A_B,
    datediff(dd,B,C) DIFF_B_C,
    (datediff(dd,A,B) + datediff(dd,B,C))/2 media,
    A,
    B,
    C
from    
(
    SELECT  
        client_report_id,
        max(case when rownum = 3 then received_date end) A,
        max(case when rownum = 2 then received_date end) B,
        max(case when rownum = 1 then received_date end) C
    FROM
     (
        SELECT  
            ROW_NUMBER() OVER(PARTITION BY client_report_id ORDER BY client_report_id,received_date desc) AS rownum, 
            client_report_id,
            received_date
        FROM
            receipt
    ) A
    WHERE A.rownum IN (1,2,3)
    group by client_report_id
) B
WHERE A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL

Note that this SQL performs the calculation for all database records, not just for a specific one, as shown in the question.

Its return consists of the Client ID and 6 fields. The necessary ones are only the client id and the media field. But I’ve left the others to make it clear.

For it to work, it is assumed that the customer has made at least 3 purchases. If he does less than that, then it will not appear.

Note that although the name of the table and fields does not reflect the question, it is very simple to change it, since it is only a table (Receipt) and two fields (client_report_id and received_date).

Answer 2

See if with the AVG (Average - Medium) works.

SELECT TOP 3 
    CONVERT(DATETIME, AVG(CONVERT(FLOAT, data))) as [MediaData] 
FROM 
    dbo.tab_venda ven 
INNER JOIN 
    dbo.tab_item_venda iven ON iven.num_venda = ven.num_venda 
WHERE 
    ven.cod_pessoa = 737 AND iven.cod_produto = 2111 
ORDER BY 
    ven.data 
DESC

Answer 3

Assuming your table contains a primary key:

select 
  sum(c.diff)/2 as total
from vendas v1
inner join (select top 3 id, data from vendas order by data desc) v2
  on v2.id = v1.id-1
cross apply (
  select datediff(d,v2.data,v1.data) as diff
) c

See example working on Sqlfiddle.