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

 

[Kyle123]

Which is exactly what the code I've given you does.

What is not at all clear is how you want to break this down into months - as I mentioned, you need to upload it to a file sharing site and post a link or do as you did before and just put the results directly into your post

 

[sellal]

I remember the data I pasted above.

 

[Kyle123]

Without you posting your desired output, I cannot help you.

I wish you the best of luck and hope someone else can

 

[Joe4]

You can post screen images using the tools mentioned here: http://www.mrexcel.com/forum/about-board/508133-attachments.html

You could also posts files up to a file sharing site and provide a link to it.

 

[sellal]

Be patient kyle123 I will find a way how to post the file see you

 

[sellal]

Hi Kyle123 and everybody ,

Here is the link for the Data : copy and paste it

https://onedrive.live.com/edit.aspx...relation=4958964c-5bf6-42eb-b8af-1505077bc876

Thanks for your help

 

[Kyle123]

One drive tells me I don't have permission to view it

 

[sellal]

Try this:

https://onedrive.live.com/redir?resid=8952ce5daef23b26!114&authkey=!AG_hg0pt48Huqlg&ithint=file,xlsx

 

[sellal]

I hope you could reach the table

 

[Kyle123]

I could, but the logic isn't clear - the formulas aren't consistent.

I'll have a look when I can set some time aside