SQL query case: Search for incomplete data _ Cartesian product in a continuous date

From: http://hi.baidu.com/wangzhiqing999/blog/item/8db9453dafb73bfc3b87ce51.html
Test Table and test data

Create Table testdatacheck (
Id varchar (5), -- device ID
Dates datetime -- Date
)
Insert into testdatacheck values ('device 1', '2017-10-01 00:00:00 ');
Insert into testdatacheck values ('device 1', '2017-10-02 00:00:00 ');
Insert into testdatacheck values ('device 1', '2017-10-03 00:00:00 ');
Insert into testdatacheck values ('device 1', '2017-10-04 00:00:00 ');
Insert into testdatacheck values ('device 1', '2017-10-06 00:00:00 ');
Insert into testdatacheck values ('device 1', '2017-10-07 00:00:00 ');
Insert into testdatacheck values ('device 1', '2017-10-08 00:00:00 ');
Insert into testdatacheck values ('device 1', '2017-10-09 00:00:00 ');
Insert into testdatacheck values ('device 2', '2017-10-01 00:00:00 ');
Insert into testdatacheck values ('device 2', '2017-10-02 00:00:00 ');
Insert into testdatacheck values ('device 2', '2017-10-03 00:00:00 ');
Insert into testdatacheck values ('device 2', '2017-10-07 00:00:00 ');
Insert into testdatacheck values ('device 2', '2017-10-08 00:00:00 ');
Insert into testdatacheck values ('device 2', '2017-10-09 00:00:00 ');
Insert into testdatacheck values ('device 2', '2017-10-10 00:00:00 ');
Requirements

Retrieve records missing from all devices between and
Ideas

First, a 10-day date sequence is required.
Then, a 10-day device arrangement and combination are required.
Finally, you can use an permutation and combination to join the target table to retrieve the missing data.
Implementation

Step 1: Create a date sequence
Currently, 1-10 is used to store date changes. At last, the date is processed when it is associated with the primary table.
Select top 10
Row_number () over (order by (select 0) as no
From SYS. Tables
No
--------------------
1
2
3
4
5
6
7
8
9
10
Step 2: Date and device arrangement and combination
Select
All_id.id,
All_day_no.no
From
(
Select top 10
Row_number () over (order by (select 0) as no
From SYS. Tables
) As all_day_no,
(
Select distinct
ID
From
Testdatacheck
) As all_id
ID No
-------------------------
Device 1 1
Device 2 1
Device 1 2
Device 2 2
Device 1 3
Device 2 3
Device 1 4
Device 2 4
Device 1 5
Device 2 5
Device 1 6
Device 2 6
Device 1 7
Device 2 7
Device 1 8
Device 2 8
Device 1 9
Device 2 9
Device 1 10
Device 2 10
Step 3 arrange the association between the combination and the target table
Select
Convert (datetime, '2017-09-30 00:00:00 ') + alltestdatacheck. No as date,
Alltestdatacheck. ID
From
(
Select
All_id.id,
All_day_no.no
From
(
Select top 10
Row_number () over (order by (select 0) as no
From SYS. Tables
) As all_day_no,
(
Select distinct
ID
From
Testdatacheck
) As all_id
) Alltestdatacheck
Left join testdatacheck
On (alltestdatacheck. ID = testdatacheck. ID
And convert (datetime, '2017-09-30 00:00:00 ') + alltestdatacheck. No = testdatacheck. Dates)
Where
Testdatacheck. dates is null
Execution result

Date ID
----------------------------
2010-10-04 00:00:00. 000 device 2
00:00:00. 000 device 1
2010-10-05 00:00:00. 000 device 2
2010-10-06 00:00:00. 000 device 2
2010-10-10 00:00:00. 000 device 1