Calculate the average between 3 dates directly in sqlserver database?

4

What is the most efficient way to calculate the average of dates in the same column by sqlserver bank? I need to get 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've done 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 

that returns me:

2015-08-10 00:00:00.000 
2015-07-10 00:00:00.000 
2015-06-03 00:00:00.000
    
asked by anonymous 13.08.2015 / 21:33

3 answers

1

The SQL below performs the requested operation. It averages days between the dates of the last three purchases, called here from A, B, C. A being the oldest of the three and C being 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 bank records, not just a specific one, such as the one displayed in the question.

The return of it consists of the Customer ID and 6 fields. Required are just the client id and the media field. But I left the others to make clear the account being made.

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

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

    
13.08.2015 / 22:07
0

See if AVG (Average) 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
    
13.08.2015 / 21:56
0

Starting from the principle that 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 in SQLFiddle .

    
14.08.2015 / 00:52