CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Icemanind
Icemanind

Reputation: 48686

Returning rows that have a one hour difference in date

I have a table called Payment that looks like this:

---------------------------------------------
|PaymentId  |InvoiceId  |Created            |
|-----------|-----------|-------------------|
|10032      |184432     |7/10/2013 11:52am  |
|10033      |184458     |7/10/2013 1:53pm   |
|10034      |184412     |7/12/2013 12:02pm  |
|10035      |184412     |7/12/2013 12:03pm  |
|10036      |184413     |7/13/2013 2:15pm   |
|10037      |184413     |7/13/2013 3:20pm   |
---------------------------------------------

What I am trying to do is create a SQL statement that will return all rows that have the same InvoiceId and the Created DateTime field have a difference greater than an hour.

So in my example above, I want PaymentId's 10036 and 10037 to be returned because they share the same InvoiceId and the Created field is more then an hour apart.

PaymentId's 10034 and 10035 would not be returned because even though they share the same invoiceid, they are not an hour or more apart.

PaymentId's 10032 and 10033 would not be returned because even though they are more then an hour apart, they do not share the same InvoiceId.

Any help on writing this would be appreciated!

I am using SQL Server 2008.

Upvotes: 1

Views: 61

Answers (2)

Sonam
Sonam

Reputation: 3466

You can try this:

 select A.* from payment a 
 join payment1 b on a.InvoiceId=b.InvoiceId and datediff(hh,b.Created,a.created)>=1
 union
 select A.* from payment a 
 join payment1 b on a.InvoiceId=b.InvoiceId and datediff(hh,a.Created,b.created)>=1

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

SELECT  PaymentID
      , InvoiceID
      , Created
FROM    Payment p
WHERE   EXISTS ( SELECT InvoiceID
                 FROM   Payment p2
                 WHERE  p.InvoiceID = p2.InvoiceID
                 GROUP BY InvoiceID
                 HAVING DATEDIFF(Hour, MIN(Created), MAX(Created)) > 1 )

Upvotes: 1

Related Questions