SQL Query to find out time difference in a table

I have the below Purchase receipt table with these rows

Order #    Receipt #          DateTime
1233           2345                2018-04-04 13:21:55.153
1233           2346                2018-04-04 13:22:01.410
1067           2834                2018-04-03 12:18:01.410
1067           2835                2018-04-03 12:18:52.410
1067           2839                2018-04-03 12:21:52.410

I need to find out the receipt numbers using the SQL query to list the rows generated less than the one-minute time difference for the same purchase orders.
The output should be like this
Order #   Receipt #
1233         2345
1233         2346
1067         2834
1067         2835

the above receipts generated  for the same order # less than one-minute time difference. Please let me know how to find out using the SQL query.
Varshini SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Are you using MySQL version 8 ? (new features make this easier)
Varshini SAuthor Commented:
sorry i am SQL Server 2008 R2
PortletPaulEE Topic AdvisorCommented:
select
*
from (
    select t.Order#, t.Receipt#, t.datetime dt1, ca.dt2
         , count(*) over(partition by t.Order#, ca.dt2) as cnt
    from mytable t
    cross apply (
        select min(datetime) dt2
        from mytable
        where datetime >= dateadd(minute,-1,t.datetime) --and datetime < t.datetime
        ) ca
    ) d
where cnt > 1

Open in new window

result:
+---+--------+----------+-------------------------+-------------------------+-----+
|   | Order# | Receipt# |           dt1           |           dt2           | cnt |
+---+--------+----------+-------------------------+-------------------------+-----+
| 1 |   1067 |     2834 | 2018-04-03 12:18:01.410 | 2018-04-03 12:18:01.410 |   2 |
| 2 |   1067 |     2835 | 2018-04-03 12:18:52.410 | 2018-04-03 12:18:01.410 |   2 |
| 3 |   1233 |     2345 | 2018-04-04 13:21:55.153 | 2018-04-04 13:21:55.153 |   2 |
| 4 |   1233 |     2346 | 2018-04-04 13:22:01.410 | 2018-04-04 13:21:55.153 |   2 |
+---+--------+----------+-------------------------+-------------------------+-----+

CREATE TABLE mytable(
   Order#    INTEGER  NOT NULL
  ,Receipt#  INTEGER  NOT NULL
  ,DateTime VARCHAR(35) NOT NULL
);
INSERT INTO mytable(Order#,Receipt#,DateTime) VALUES (1233,2345,'2018-04-04 13:21:55.153');
INSERT INTO mytable(Order#,Receipt#,DateTime) VALUES (1233,2346,'2018-04-04 13:22:01.410');
INSERT INTO mytable(Order#,Receipt#,DateTime) VALUES (1067,2834,'2018-04-03 12:18:01.410');
INSERT INTO mytable(Order#,Receipt#,DateTime) VALUES (1067,2835,'2018-04-03 12:18:52.410');
INSERT INTO mytable(Order#,Receipt#,DateTime) VALUES (1067,2839,'2018-04-03 12:21:52.410');

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I think the cross apply subselect ca  needs another condition to filter for order#:
    cross apply (
        select min(datetime) dt2
        from mytable
        where datetime >= dateadd(minute,-1,t.datetime) --and datetime < t.datetime
        and order# = t.order#
        ) ca

Open in new window

PortletPaulEE Topic AdvisorCommented:
Yes. Good pickup Qlemo..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.