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

 

[sellal]

I didn't put the automate formulas, imagine a table with more than one million rows until now and number of products is about 10, Excel has its limit about number of rows.
It's just to make sure the idea is understood.

I hope the people of this site add link to share file(s).

Thanks for your help

 

[Kyle123]

I'll update the SQL but I'm guessing that the date ranges aren't fixed? It's not trivial to get the output you want in SQL and you'll need to put all the date groupings in manually so it's crucial you understand the query

 

[sellal]

Ok, thanks

 

[sellal]

I forgot to tell you our weekend is on friday and saturday so you will understand my absence during these two days.

So, if we won't see each other this day again I wish you a nice weekend.

 

[sellal]

Hi everybody,

Thanks for helping me, actually I was viven and idea about my steps to calculate the average, if we ignore the column Num_Costumer the table should be lake this :

Name_product ordered by name of product then purchase_day ordered by day of purchase, so for product 1 the average duration = sum(durations for product1)/number of durations for product1), but for the last purchase the duration = 31/05/2014 - 30/04/2014 and if the la last purchase > 31/05/2014 the duration = empty (this cell is empty).

The table in this link will give help : https://onedrive.live.com/redir?resid=8952CE5DAEF23B26!114&authkey=!AMZ1j14eckTDXaA&ithint=file,xlsx

Thanks again

 

[sellal]

Hi everybody,

Here is an ambitious try as I am not developer.


SELECT Name_product, Purchase_Day
CASE
when cur.Name_product = prev.Name_product
AND cur.Purchase_Day <= '31/05/2014' then cur.Purchase_Day - prev.Purchase_Day as Durarion
when cur.Name_product <> prev.Name_product
AND cur.Purchase_Day > '31/05/2014' then "" as Duration
else '31/05/2014' - prev.purchase_Day as Duration
END
FROM Sales
ORDER BY Name_product, Purchase_Day


Of course it is false but it's a try, this is without Average duration.

Thanks

 

[Kyle123]

Do you understand the SQL I posted? If not, start there