Difference of values from the same column

12 Aug 2005 8:59 AM

mlsrinivas

Hi,

Can anybody help me in finding the difference of values
(value1-value2,...) from the same column of a table.

Suppose, OrderDate column of Orders table has values like
07/01/05,07/05/05,07/08/05,07/12/05. How to find the no of days between
two order dates using a query?

What I am trying to do is, to find the frequency with which the orders
are getting placed by a customer. Though the no of days between any two
dates need not be same, I want to take average of no of days between
two orders so that, it can be considered as avg frequency of orders for
a customer.

Any sort of help is appreciated.

Thanks
Srinivas

12 Aug 2005 9:02 AM

Uri Dimant

Use DATEDIFF() system function.For more details please refer to the BOL

<mlsrini***@gmail.com> wrote in message
Show quote

news:1123837159.780460.215190@f14g2000cwb.googlegroups.com...
> Hi,
>
> Can anybody help me in finding the difference of values
> (value1-value2,...) from the same column of a table.
>
> Suppose, OrderDate column of Orders table has values like
> 07/01/05,07/05/05,07/08/05,07/12/05. How to find the no of days between
> two order dates using a query?
>
> What I am trying to do is, to find the frequency with which the orders
> are getting placed by a customer. Though the no of days between any two
> dates need not be same, I want to take average of no of days between
> two orders so that, it can be considered as avg frequency of orders for
> a customer.
>
> Any sort of help is appreciated.
>
> Thanks
> Srinivas
>

12 Aug 2005 9:13 AM

Jens SÃ¼ÃŸmeyer

To help you with that more information than this you gave use is necessary,
because a customer could also place several orders at one day which would
effect the result in a respective way. So please post some ddl to help you to
get the correct result.

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"mlsrini***@gmail.com" wrote:

> Hi,
>
> Can anybody help me in finding the difference of values
> (value1-value2,...) from the same column of a table.
>
> Suppose, OrderDate column of Orders table has values like
> 07/01/05,07/05/05,07/08/05,07/12/05. How to find the no of days between
> two order dates using a query?
>
> What I am trying to do is, to find the frequency with which the orders
> are getting placed by a customer. Though the no of days between any two
> dates need not be same, I want to take average of no of days between
> two orders so that, it can be considered as avg frequency of orders for
> a customer.
>
> Any sort of help is appreciated.
>
> Thanks
> Srinivas
>
>

12 Aug 2005 9:25 AM

David Portas

Try this (untested):

SELECT customer,
DATEDIFF(DAY,MIN(orderdate), MAX(orderdate))
/ CAST(COUNT(*) AS REAL) AS avg_days_between_orders
FROM Orders
GROUP BY customer

If you need more help, please post DDL and sample data.

--
David Portas
SQL Server MVP
--

12 Aug 2005 10:32 AM

mlsrinivas

Hi David,

Thanks alot. Thanks for giving me the idea. It worked fine for my
requirement. Thanks for your time.

Srinivas