Convert excel formula into SQL Access or SQL Server

[sellal]

I need to convert the Excel formula into SQL Access or SQL server formula

First : The formula in column Duration is :
IF(AND(A2=A3;$E$1>=B3);B3-B2;SI(AND(A2=A3;$E$1<B3);"";IF(AND(A2<>A3;$E$1<=B2);"";$E$1-B2)))

E1 is : 31/05/2014

Num_ customer	Purchase_ day	Name_ Product	Duration	31/05/2014
957071417	22/04/2014	Product1
957074848	15/04/2014	Product1
958020505	21/04/2014	Product1
958022300	25/04/2014	Product1
958023030	30/04/2014	Product1
958630888	02/05/2014	Product2
958630888	03/05/2014	Product2
958630888	03/05/2014	Product2
958640915	01/05/2014	Product2
958640915	02/05/2014	Product2
958640915	03/05/2014	Product2
958644254	30/04/2014	Product2
958644440	03/05/2014	Product2
958646919	01/05/2014	Product2
958646919	02/05/2014	Product2
958678805	03/05/2014	Product2
958704045	07/04/2014	Product3
958704061	05/04/2014	Product3
958704061	25/04/2014	Product3

<tbody>
</tbody>

Second : The formula to calculate Average duration in the cell F4 is :
D is the column of Duration
AVERAGEIFS($D:$D;$D:$D;"<>""";$C:$C;$F4)

	Average Duration (days)
Product	To	To	To	To	To
31/05/2014	30/06/2014	31/07/2014	31/08/2014	06/09/2014
Product1
Product2
Product3

<tbody>
</tbody>

Thanks in advance

 

[Joe4]

<b style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">First : The formula in column Duration is :
IF(AND(A2=A3;$E$1>=B3);B3-B2;SI(AND(A2=A3;$E$1<b3);"";if(and(a2<>A3;$E$1<=B2);"";$E$1-B2)))</b3);"";if(and(a2<></b>

Welcome to the Board!
At first, I was confused with the "SI" function, as I have never seen that in all my years of Excel programming. But a Google search indicates that is "IF" in another language.

So here is what you need to know:

The Access equiavlent of IF is IIF, and it is structured the same way.
See: MS Access: iif Function

Access has an AND function too, but it is structured a bit different than Excel's AND function. You list each condition, separated be the AND.
Here is a similar one with IIF and AND I helped someone with some time ago: http://www.mrexcel.com/forum/microsoft-access/71636-iif-statement-access.html

See if you can convert it, and if not, post back with your attempt.

 

[sellal]

Hi Joe4,

Thanks Joe4 for your help but the meaning of formula IF(AND(A2=A3;$E$1>=B3);B3-B2;IF(AND(A2=A3;$E$1<B3);"";IF(AND(A2<>A3;$E$1<=B2);"";$E$1-B2))) is:

We have a table with three columns : Num_customer , Purchase_day , Name_Product

IF(AND(A2=A3;$E$1>=B3);B3-B2: (if the first value of the column Num_customer= second value of the same column and the value of E1 which is the day 31/05/2014 bigger than or equal to the second value of the column Purchase_day ) then (B3-B2: the second value of the column Purchase_day minus the first value of the same column) if not

IF(AND(A2=A3;$E$1<B3) : (if the first value of the column Num_customer= second value of the same column and the value of E1 which is the day 31/05/2014 less than the second value of the column Purchase_day ) then empty (nothing) if not

IF(AND(A2<>A3;$E$1<=B2) : (if the first value of the column Num_customer different from the second value of the same column and the value of E1 which is the day 31/05/2014 less than or equal to the first value of the column Purchase_day ) then empty (nothing) if not

$E$1-B2 : the value of E1 which is the day 31/05/2014 minus the first value of the column Purchase_day

By the way the Duration here is until the day 31/05/2014 = $E$1 (fixed day) and same thing if we will calculated until the end of each month.


Thanks again for helping me

 

[Kyle123]

In words, what are you trying to find out? Typically one does not "convert" formulas to SQL, SQL is set and row based, not column based which means that you accomplish things in different ways.

To be explicit, one row in SQL doesn't "know" what's in the next row.

If you let us know what you are trying to do, we can make some suggestions - as a guide as to what we want, pretend someone who doesn't know any excel formulas or sql is asking you to find something out and then post that

 

[sellal]

Hi Kyle123 and everybody ,

I have data concerning sale of several products, columns are three : Num_customer, Purchase_day and Name_product.

I need to calculate with SQL the average duration (number of days) to purchase again the same product among the products

As in example, one customer can buy the same product many times, so the " Num_Customer " we will see it repeated several times at different days " Purchase_day " for the same customer, so here we can have Duration for one Customer , and finally Average Duration to buy the same product by the different customers at the end of each month the reason to see the monthly evolution.

I tried to post the question as it is done in Excel, I hope I am enough clarify

Thanks a lot for your help and being patient.

 

[Kyle123]

Are you wanting the results to show customer ID as well or just the Product averages?

 

[Kyle123]

This is TSQL (SQL Server) I have no idea how you'd do it in Access. Working example http://sqlfiddle.com/#!6/508e4/14/0

You'd want something like this:

CREATE TABLE SALES(
    CustId int,
    PurchaseDate datetime,
    Product varchar(50)
  )
 
INSERT INTO SALES VALUES(957071417,'2014-04-22','Product1')
INSERT INTO SALES VALUES(957074848,'2014-04-15','Product1')
INSERT INTO SALES VALUES(958020505,'2014-04-21','Product1')
INSERT INTO SALES VALUES(958022300,'2014-04-25','Product1')
INSERT INTO SALES VALUES(958023030,'2014-04-30','Product1')
INSERT INTO SALES VALUES(958630888,'2014-05-02','Product2')
INSERT INTO SALES VALUES(958630888,'2014-05-03','Product2')
INSERT INTO SALES VALUES(958630888,'2014-05-03','Product2')
INSERT INTO SALES VALUES(958640915,'2014-05-01','Product2')
INSERT INTO SALES VALUES(958640915,'2014-05-02','Product2')
INSERT INTO SALES VALUES(958640915,'2014-05-03','Product2')
INSERT INTO SALES VALUES(958644254,'2014-04-30','Product2')
INSERT INTO SALES VALUES(958644440,'2014-05-03','Product2')
INSERT INTO SALES VALUES(958646919,'2014-05-01','Product2')
INSERT INTO SALES VALUES(958646919,'2014-05-02','Product2')
INSERT INTO SALES VALUES(958678805,'2014-05-03','Product2')
INSERT INTO SALES VALUES(958704045,'2014-04-07','Product3')
INSERT INTO SALES VALUES(958704061,'2014-04-05','Product3')
INSERT INTO SALES VALUES(958704061,'2014-04-25','Product3')
 
with orders as (
	select Row_number() over (partition by custId, Product order by PurchaseDate) as RN,
	purchaseDate, product, custId
	FROM sales
)
 
SELECT
  cur.CustId,
  cur.product,
  AVG(convert(decimal(18,6),DATEDIFF(DD,prev.purchaseDate, cur.purchaseDate))) as AvgDaysDifference 
FROM orders cur
INNER JOIN orders prev
    ON cur.product = prev.product
    AND cur.CustId = prev.CustId
    AND cur.rn = prev.rn + 1
GROUP BY   
  cur.CustId,
  cur.product

Is that close to what you're after?

If you want without customer Ids:

SELECT
  cur.product,
  AVG(convert(decimal(18,6),DATEDIFF(DD,prev.purchaseDate, cur.purchaseDate))) as AvgDaysDifference 
FROM orders cur
INNER JOIN orders prev
    ON cur.product = prev.product
    AND cur.CustId = prev.CustId
    AND cur.rn = prev.rn + 1
GROUP BY   
  cur.product

 

[sellal]

I want it without customer Ids
Thanks a lot Kyle123 it's really great but I want it (average duration) as I said at the end at each month to see the monthly evolution (may be a customer has not bought again a same product at the same seller until yesterday we will say the average duration for him for this product is the some of all durations /number of durations (the last duration = day of yesterday minus the last purchase_day). yesterday here the end of each month.

I hope I was enough clear

 

[Kyle123]

but I want it (average duration) as I said at the end at each month to see the monthly evolution

No, you rambled and gave about 4 requirements and no, you aren't clear enough

How can you show this:

average duration for him for this product is the some of all durations /number of durations

Without customer Ids? When a purchase interval spans months, which months should the average be in?

I've got no idea what you actually want.

From your sample data, please work out what the answers should be and either upload them in a workbook to a file sharing site or post them here

 

[sellal]

I am really sorry Kyle123,
In Excel to calculate the average duration of all customers so without customer Ids we have first to calculate the duration of each customer then after the average duration of the all durations.
If we take one customer : he will buy the same product many times so for the second times the Duration will be the second purchase day minus the first and the second Duration will be the third purchase day minus the second and so on until yesterday not yet bought the Duration here will be yesterday minus the last purchase day so for him the average duration is the sum of all durations/number of durations, and the average durations for all customers for the same product is the sum of all durations to buy again the same product/number of durations to by the same product.

But what I need to show is the average duration to buy again the same product , no matter which customer is it is the average you know.

I wanted to post the file but there isn't a place where to post.

Thanks again