0

I have a table such as below which holds a list of transactions by date, location and device. There are gaps in the transaction record for most devices.

    date                    location               device                 Transaction
----------------------- ---------------------- ---------------------- ----------------------
2020-09-04 00:00:00.000 101                    62                     462
2020-09-04 00:00:00.000 101                    62                     463
2020-09-04 00:00:00.000 101                    62                     464
2020-09-04 00:00:00.000 101                    62                     465
2020-09-04 00:00:00.000 101                    62                     471
2020-09-04 00:00:00.000 101                    62                     472
2020-09-04 00:00:00.000 101                    62                     473
2020-09-04 00:00:00.000 101                    63                     300
2020-09-04 00:00:00.000 101                    63                     301
2020-09-04 00:00:00.000 101                    63                     302
2020-09-04 00:00:00.000 101                    63                     303
2020-09-04 00:00:00.000 101                    63                     304
2020-09-04 00:00:00.000 101                    63                     305
2020-09-04 00:00:00.000 101                    63                     306
2020-09-04 00:00:00.000 101                    63                     311
2020-09-04 00:00:00.000 101                    63                     312
2020-09-04 00:00:00.000 101                    63                     313

I'm trying to write a query which returns the missing transactions along with the date,location and device. The output I need is here

date                    location               device                 Transaction
----------------------- ---------------------- ---------------------- ----------------------
2020-09-04 00:00:00.000 101                    62                     466
2020-09-04 00:00:00.000 101                    62                     467
2020-09-04 00:00:00.000 101                    62                     468
2020-09-04 00:00:00.000 101                    62                     469
2020-09-04 00:00:00.000 101                    62                     470
2020-09-04 00:00:00.000 101                    63                     307
2020-09-04 00:00:00.000 101                    63                     308
2020-09-04 00:00:00.000 101                    63                     309
2020-09-04 00:00:00.000 101                    63                     310
Thom A
  • 88,727
  • 11
  • 45
  • 75
Mark Edwards
  • 9
  • 1
  • This is a classic "islands and gaps" problem. Here's an article that will help: https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/ – Grant Fritchey Oct 06 '20 at 11:34
  • I've read that article previously and also looked at numerous other articles. I've not seen anything anywhere where the dataset being worked on contains essentially multiple discreet sequences. I've been unable to modify other solutions to work which is why I have raised a new question. – Mark Edwards Oct 06 '20 at 12:04

0 Answers0