SQL Query to find out time difference in a table

Varshini S
Varshini S used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Are you using MySQL version 8 ? (new features make this easier)

Author

Commented:
sorry i am SQL Server 2008 R2
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Yes. Good pickup Qlemo..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial