Copy Code code as follows:
---1. Average sales wait time
---has a sales table with two columns for the sale date and the customer, and now requires an SQL statement to implement the calculation
--average number of days between two purchases per customer
--assuming that the same person does not buy two times a day
CREATE TABLE Sales
(
CustName varchar (TEN) is not NULL,
Saledate datetime NOT NULL
)
Go
Insert Sales
Select ' John ', ' 2010-1-1 ' union
Select ' John ', ' 2010-11-1 ' union
Select ' John ', ' 2011-1-1 ' union
Select ' Harry ', ' 2010-2-1 ' union
Select ' Harry ', ' 2010-4-1 ' union
Select ' Dick ', ' 2010-1-1 ' union
Select ' Dick ', ' 2010-5-1 ' union
Select ' Dick ', ' 2010-9-1 ' union
Select ' Dick ', ' 2011-1-1 ' union
Select ' Zhao Liu ', ' 2010-1-1 ' union
Select ' Money path ', ' 2010-1-1 ' union
Select ' Money path ', ' 2011-3-1 ' union
Select ' John ', ' 2011-9-1 '
Go
Select Custname,datediff (D,min (saledate), Max (saledate))/(COUNT (*)-1) as Avgday
From sales
GROUP BY CustName
Having Count (*) >1
Go
Select Custname,case when Count (*) >1 then DATEDIFF (D,min (saledate), Max (saledate))/(COUNT (*)-1)
Else DATEDIFF (D,min (saledate), Max (saledate) end)
As Avgday
From sales
GROUP BY CustName
--having Count (*) >1
Go
DROP TABLE Sales
