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

[TABLE="width: 539"]
<tbody>[TR]
[TD]Num_
customer
[/TD]
[TD]Purchase_
day
[/TD]
[TD] Name_
Product
[/TD]
[TD]Duration
[/TD]
[TD]31/05/2014
[/TD]
[/TR]
[TR]
[TD]957071417
[/TD]
[TD]22/04/2014
[/TD]
[TD]Product1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]957074848
[/TD]
[TD]15/04/2014
[/TD]
[TD]Product1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958020505
[/TD]
[TD]21/04/2014
[/TD]
[TD]Product1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958022300
[/TD]
[TD]25/04/2014
[/TD]
[TD]Product1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958023030
[/TD]
[TD]30/04/2014
[/TD]
[TD]Product1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958630888
[/TD]
[TD]02/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958630888
[/TD]
[TD]03/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958630888
[/TD]
[TD]03/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958640915
[/TD]
[TD]01/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958640915
[/TD]
[TD]02/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958640915
[/TD]
[TD]03/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958644254
[/TD]
[TD]30/04/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958644440
[/TD]
[TD]03/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958646919
[/TD]
[TD]01/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958646919
[/TD]
[TD]02/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958678805
[/TD]
[TD]03/05/2014
[/TD]
[TD]Product2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958704045
[/TD]
[TD]07/04/2014
[/TD]
[TD]Product3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958704061
[/TD]
[TD]05/04/2014
[/TD]
[TD]Product3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]958704061
[/TD]
[TD]25/04/2014
[/TD]
[TD]Product3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)[TABLE="width: 532"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 5"]Average Duration (days)
[/TD]
[/TR]
[TR]
[TD]Product
[/TD]
[TD]To
[/TD]
[TD]To
[/TD]
[TD]To
[/TD]
[TD]To
[/TD]
[TD]To
[/TD]
[/TR]
[TR]
[TD]31/05/2014
[/TD]
[TD]30/06/2014
[/TD]
[TD]31/07/2014
[/TD]
[TD]31/08/2014
[/TD]
[TD]06/09/2014
[/TD]
[/TR]
[TR]
[TD]Product1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Product2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Product3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


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