Difference between dates in different rows...

Hi all,

I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)

...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...

At the moment, I have the following query that I think is on the right track:
…
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
…

However, this gives me too much data:
…
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
…

So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I’d like to do this in a single query.

Thanks for any help you have to offer,

Regards,

Stephen.

SQL Server 2005:

SELECT a.CustomerID, a.OrderDate as Highdate, b.OrderDate as LowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs

FROM (SELECT CustomerID, OrderDate, ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) as RowNum FROM dbo.Orders) a

INNER JOIN (SELECT CustomerID, OrderDate, (ROW_Number() OVER (Partition By CustomerID ORDER BY OrderDate) -1)as RowNumMinusOne

FROM dbo.Orders) b ON a.CustomerID=b.CustomerId AND a.RowNum=b.RownumMinusOne

|||

SQL Server 2000:

SELECT a.CustomerID, a.OrderDate as HighDate, b.OrderDate as lowDate, DATEDIFF(day, a.OrderDate, b.OrderDate) AS Diffs FROM (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T.CustomerID and OrderDate < T.OrderDate ) + 1 as Rank1

from Orders as T ) a INNER JOIN (SELECT CustomerID, OrderDate, (select count(*) From Orders where CustomerID = T1.CustomerID and OrderDate < T1.OrderDate ) as Rank2

from Orders as T1 ) b ON b.CustomerID=a.CustomerID and a.Rank1=b.Rank2

ORDER BY a.CustomerID, a.OrderDate

|||

You're an absolute star! Just what I was after. My head was starting to spin trying to figure this one out.

Thank you for your help!

Regards,

Stephen.

sql

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

difference between 2 columns key and a third-party column with Identity

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,

iTaY.

Natural Key should always be prefered. Assuming that OrderId is the ID which is printed or somewhere used in your order process, this represents the natural key. CustorderID is a artificial key which is not used any further in your process and just there to identify the unique row.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens comments, I would highly discourage allowing a key field to be updated.

|||

Thanks for your time guys, very helpful !

|||Well, expanding Arnie while Contradicting Jens, I'd say that using an Identity column as pk does naturally prevents a key change on DB level.

From indexing point of view, it's likely that scanning a one-column index would be faster, as more index nodes can fit in a page, thus less disk reads should be performed.

From application point of view - it's a lot easier to use an Id column for almost any operation. It allows you to use natural comparing and serializing (ToString) rather than the need to serialize the key somehow, and implement HashCode (which many implement badly) thus simplifying the code, thus, again, increasing maintainability.

just my 0.02￡|||

I do not see the point contradicting me ? I just said, that you should prefer having a "natural" key like the Invoice number rather than an identity column.

diff. between named pipe & TCP-IP

Hi
Is there any diff. between Named Pipe& TCP-IP protocol from performance
point of view there by I can force all the users connecting to server with
TCP-IP proctocol only .
Regards
Ajay RengunthwarFrom BOL:
Named Pipes vs. TCP/IP Sockets
In a fast local area network (LAN) environment, Transmission Control
Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are
comparable in terms of performance. However, the performance difference
between the TCP/IP Sockets and Named Pipes clients becomes apparent with
slower networks, such as across wide area networks (WANs) or dial-up
networks. This is because of the different ways the interprocess
communication (IPC) mechanisms communicate between peers.
For named pipes, network communications are typically more interactive. A
peer does not send data until another peer asks for it using a read command.
A network read typically involves a series of peek named pipes messages
before it begins to read the data. These can be very costly in a slow
network and cause excessive network traffic, which in turn affects other
network clients.
It is also important to clarify if you are talking about local pipes or
network pipes. If the server application is running locally on the computer
running an instance of Microsoft® SQL ServerT 2000, the local Named Pipes
protocol is an option. Local named pipes runs in kernel mode and is
extremely fast.
For TCP/IP Sockets, data transmissions are more streamlined and have less
overhead. Data transmissions can also take advantage of TCP/IP Sockets
performance enhancement mechanisms such as windowing, delayed
acknowledgements, and so on, which can be very beneficial in a slow network.
Depending on the type of applications, such performance differences can be
significant.
TCP/IP Sockets also support a backlog queue, which can provide a limited
smoothing effect compared to named pipes that may lead to pipe busy errors
when you are attempting to connect to SQL Server.
In general, sockets are preferred in a slow LAN, WAN, or dial-up network,
whereas named pipes can be a better choice when network speed is not the
issue, as it offers more functionality, ease of use, and configuration
options.
"AJAY R" <dba_pune@.hotmail.com> wrote in message
news:er$fIigRDHA.3132@.tk2msftngp13.phx.gbl...
> Hi
> Is there any diff. between Named Pipe& TCP-IP protocol from performance
> point of view there by I can force all the users connecting to server with
> TCP-IP proctocol only .
> Regards
> Ajay Rengunthwar
>
>

Developer Edition Installation - Named Instance can't be opened

I am trying to install the Developer's Edition of SQL SERVER and I would lik
e to create a named instance.
The installation appears to work OK but I can't register the NAMED instance
in Enterprise Manager. The LOCAL instance is working OK and is registered in
Enterprise Manager.
Each time I try to register the NAMED instance I get the error SQL SERVER do
es not exist or access denied. ConnectionOpen(Connect()).
I see the instance existing in my SQL SERVER installation directory.
I am installing this in Windows XP
Any help is appreciated.
jimThis may be basic but are you trying to register it using the following
name:
<machinename\instancename>.
If you are and it is still failing. Look at the SQL Server errorlog form
that instance and verify that it is listening on shared memory, TCP/IP and
named pipes. If you are trying to register it on the server itself shared
memory is used by default. Trying regiistering it uisng the IP address and
port number that SQL Server is listening on.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I corrected the problem. I needed to clear my registry entries for a prior i
nstallation of INSTANCE.
thanks for your help

Developer Edition Installation - Named Instance can't be opened

I am trying to install the Developer's Edition of SQL SERVER and I would like to create a named instance.
The installation appears to work OK but I can't register the NAMED instance in Enterprise Manager. The LOCAL instance is working OK and is registered in Enterprise Manager.
Each time I try to register the NAMED instance I get the error SQL SERVER does not exist or access denied. ConnectionOpen(Connect()).
I see the instance existing in my SQL SERVER installation directory.
I am installing this in Windows XP
Any help is appreciated.
jim
This may be basic but are you trying to register it using the following
name:
<machinename\instancename>.
If you are and it is still failing. Look at the SQL Server errorlog form
that instance and verify that it is listening on shared memory, TCP/IP and
named pipes. If you are trying to register it on the server itself shared
memory is used by default. Trying regiistering it uisng the IP address and
port number that SQL Server is listening on.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I corrected the problem. I needed to clear my registry entries for a prior installation of INSTANCE.
thanks for your help

Developer Edition Installation - Named Instance can't be opened

I am trying to install the Developer's Edition of SQL SERVER and I would like to create a named instance
The installation appears to work OK but I can't register the NAMED instance in Enterprise Manager. The LOCAL instance is working OK and is registered in Enterprise Manager
Each time I try to register the NAMED instance I get the error SQL SERVER does not exist or access denied. ConnectionOpen(Connect())
I see the instance existing in my SQL SERVER installation directory.
I am installing this in Windows X
Any help is appreciated
jimDid you do an advanced install?
>--Original Message--
>I am trying to install the Developer's Edition of SQL
SERVER and I would like to create a named instance.
>The installation appears to work OK but I can't register
the NAMED instance in Enterprise Manager. The LOCAL
instance is working OK and is registered in Enterprise
Manager.
>Each time I try to register the NAMED instance I get the
error SQL SERVER does not exist or access denied.
ConnectionOpen(Connect()).
>I see the instance existing in my SQL SERVER installation
directory.
>I am installing this in Windows XP
>Any help is appreciated.
>jim
>.
>|||This may be basic but are you trying to register it using the following
name:
<machinename\instancename>.
If you are and it is still failing. Look at the SQL Server errorlog form
that instance and verify that it is listening on shared memory, TCP/IP and
named pipes. If you are trying to register it on the server itself shared
memory is used by default. Trying regiistering it uisng the IP address and
port number that SQL Server is listening on.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I corrected the problem. I needed to clear my registry entries for a prior installation of INSTANCE
thanks for your help