当日期不在table1中时，SQL查询空行的出入考勤 [英] SQL Query for In/Out time attendance with null row when date not in table1

问题描述

我有一个带有以下示例输出的两个表. tb1进入时间，tb2退出时间

I have a two table with the below sample output. tb1 is in time and tb2 is out time

如果tb2中的行不在tb2中或tb1中的行不在tb1中，我想构建一个查询以使用空值获得以下结果:

I want to build a query to achieve the below results with null values if row in tb2 not in tb2 or row in tb1 not in tb1:

如果table2中的OINDX不在table1中或者OINDX在table1中而不在table2中，并且如果日期不在两个日期之间的日期中，则我需要获取null

i need get null if OINDX in table2 and not in table1 or IINDX in table1 and not in table2 and if date not in dates between two date get null row with date only like this photo

此代码创建两个表以尝试代码并为我提供帮助

this code to create two tables to try code and help me

  CREATE TABLE [dbo].[TIMEIN](
       [IINDX] [int] NULL,
       [USERID] [int] NULL,
       [Date] [date] NULL,
       [CHECKTIME] [datetime] NULL,
       [CHECKTYPE] [varchar](1) NULL
   ) ON [PRIMARY]
   INSERT [dbo].[TIMEIN]  VALUES (1, 60, CAST(N'2018-02-07' AS Date), CAST(N'2018-02-07T06:58:48.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (3, 60, CAST(N'2018-02-08' AS Date), CAST(N'2018-02-08T06:01:09.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (6, 60, CAST(N'2018-02-09' AS Date), CAST(N'2018-02-09T06:57:43.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (8, 60, CAST(N'2018-02-10' AS Date), CAST(N'2018-02-10T06:34:28.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (10, 60, CAST(N'2018-02-11' AS Date), CAST(N'2018-02-11T05:59:38.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (12, 60, CAST(N'2018-02-12' AS Date), CAST(N'2018-02-12T06:02:14.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (14, 60, CAST(N'2018-02-13' AS Date), CAST(N'2018-02-13T06:00:25.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (16, 60, CAST(N'2018-02-14' AS Date), CAST(N'2018-02-14T06:01:59.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (18, 60, CAST(N'2018-02-15' AS Date), CAST(N'2018-02-15T06:01:56.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (20, 60, CAST(N'2018-02-22' AS Date), CAST(N'2018-02-22T13:58:31.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (22, 60, CAST(N'2018-02-23' AS Date), CAST(N'2018-02-23T18:57:01.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (24, 60, CAST(N'2018-02-24' AS Date), CAST(N'2018-02-24T21:54:01.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (26, 60, CAST(N'2018-02-25' AS Date), CAST(N'2018-02-25T21:53:58.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (28, 60, CAST(N'2018-02-26' AS Date), CAST(N'2018-02-26T22:03:14.000' AS DateTime), N'I')
   INSERT [dbo].[TIMEIN]  VALUES (30, 60, CAST(N'2018-02-27' AS Date), CAST(N'2018-02-27T21:57:51.000' AS DateTime), N'I')

   CREATE TABLE [dbo].[TIMEOUT](
       [OINDX] [int] NULL,
       [USERID] [int] NULL,
       [Date] [date] NULL,
       [CHECKTIME] [datetime] NULL,
       [CHECKTYPE] [varchar](1) NULL
   ) ON [PRIMARY]
   INSERT [dbo].[TIMEOUT]  VALUES (1, 60, CAST(N'2018-02-07' AS Date), CAST(N'2018-02-07T15:59:32.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (3, 60, CAST(N'2018-02-08' AS Date), CAST(N'2018-02-08T15:00:32.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (4, 60, CAST(N'2018-02-08' AS Date), CAST(N'2018-02-08T15:00:34.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (6, 60, CAST(N'2018-02-09' AS Date), CAST(N'2018-02-09T19:00:03.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (8, 60, CAST(N'2018-02-10' AS Date), CAST(N'2018-02-10T15:31:16.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (10, 60, CAST(N'2018-02-11' AS Date), CAST(N'2018-02-11T15:01:47.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (12, 60, CAST(N'2018-02-12' AS Date), CAST(N'2018-02-12T15:03:06.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (14, 60, CAST(N'2018-02-13' AS Date), CAST(N'2018-02-13T15:01:40.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (16, 60, CAST(N'2018-02-14' AS Date), CAST(N'2018-02-14T15:00:34.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (18, 60, CAST(N'2018-02-15' AS Date), CAST(N'2018-02-15T15:02:55.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (20, 60, CAST(N'2018-02-22' AS Date), CAST(N'2018-02-22T22:20:42.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (22, 60, CAST(N'2018-02-24' AS Date), CAST(N'2018-02-24T06:03:39.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (24, 60, CAST(N'2018-02-25' AS Date), CAST(N'2018-02-25T07:04:37.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (26, 60, CAST(N'2018-02-26' AS Date), CAST(N'2018-02-26T07:00:16.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (28, 60, CAST(N'2018-02-27' AS Date), CAST(N'2018-02-27T07:04:08.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (30, 60, CAST(N'2018-02-27' AS Date), CAST(N'2018-02-27T21:58:48.000' AS DateTime), N'O')
   INSERT [dbo].[TIMEOUT]  VALUES (31, 60, CAST(N'2018-02-28' AS Date), CAST(N'2018-02-28T07:00:18.000' AS DateTime), N'O')

我使用此代码获取两个datw之间的列表日期

i use this code to get list dates between two datw

DECLARE @Date1 DATE = '2018-02-07'; DECLARE @Date2 DATE = '2018-02-28';
SELECT DATEADD(DAY,number,@Date1) [Date] FROM master..spt_values
WHERE type = 'P' AND DATEADD(DAY,number,@Date1) <= @Date2

推荐答案

您正在寻找左连接，间隙和孤岛:

You are looking for left join and gap and islands:

在 SQL小提琴

查询1 :

;WITH e1(n) AS (
  select 1 from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0)) t(n)
), g as (
  SELECT ROW_NUMBER() OVER (ORDER BY n10.n) AS n
  FROM
  e1 n10
  CROSS JOIN e1 n100
  CROSS JOIN e1 n1000
  CROSS JOIN e1 n10000
),
gd1 as (
  select dateadd(day, n, CAST(N'2018-01-01' AS Date)  ) as d
  from g 
),
gd as (
  select d
  from gd1
  where d not in (select [date] from TIMEIN union select [date] from TIMEOUT )  
  and d between CAST(N'2018-02-07' AS Date) and CAST(N'2018-03-01' AS Date)
)
select TIMEIN.*, TIMEOUT.*, gd.*
from gd
full outer join TIMEOUT on TIMEOUT.[date] = gd.d
full outer join TIMEIN on TIMEOUT.OINDX = TIMEIN.IINDX
order by coalesce( TIMEIN.date, TIMEOUT.date, d)

结果 :

Results:

|  IINDX | USERID |       Date |            CHECKTIME | CHECKTYPE |  OINDX | USERID |       Date |            CHECKTIME | CHECKTYPE |          d |
|--------|--------|------------|----------------------|-----------|--------|--------|------------|----------------------|-----------|------------|
|      1 |     60 | 2018-02-07 | 2018-02-07T06:58:48Z |         I |      1 |     60 | 2018-02-07 | 2018-02-07T15:59:32Z |         O |     (null) |
|      3 |     60 | 2018-02-08 | 2018-02-08T06:01:09Z |         I |      3 |     60 | 2018-02-08 | 2018-02-08T15:00:32Z |         O |     (null) |
| (null) | (null) |     (null) |               (null) |    (null) |      4 |     60 | 2018-02-08 | 2018-02-08T15:00:34Z |         O |     (null) |
|      6 |     60 | 2018-02-09 | 2018-02-09T06:57:43Z |         I |      6 |     60 | 2018-02-09 | 2018-02-09T19:00:03Z |         O |     (null) |
|      8 |     60 | 2018-02-10 | 2018-02-10T06:34:28Z |         I |      8 |     60 | 2018-02-10 | 2018-02-10T15:31:16Z |         O |     (null) |
|     10 |     60 | 2018-02-11 | 2018-02-11T05:59:38Z |         I |     10 |     60 | 2018-02-11 | 2018-02-11T15:01:47Z |         O |     (null) |
|     12 |     60 | 2018-02-12 | 2018-02-12T06:02:14Z |         I |     12 |     60 | 2018-02-12 | 2018-02-12T15:03:06Z |         O |     (null) |
|     14 |     60 | 2018-02-13 | 2018-02-13T06:00:25Z |         I |     14 |     60 | 2018-02-13 | 2018-02-13T15:01:40Z |         O |     (null) |
|     16 |     60 | 2018-02-14 | 2018-02-14T06:01:59Z |         I |     16 |     60 | 2018-02-14 | 2018-02-14T15:00:34Z |         O |     (null) |
|     18 |     60 | 2018-02-15 | 2018-02-15T06:01:56Z |         I |     18 |     60 | 2018-02-15 | 2018-02-15T15:02:55Z |         O |     (null) |
| (null) | (null) |     (null) |               (null) |    (null) | (null) | (null) |     (null) |               (null) |    (null) | 2018-02-16 |
| (null) | (null) |     (null) |               (null) |    (null) | (null) | (null) |     (null) |               (null) |    (null) | 2018-02-17 |
| (null) | (null) |     (null) |               (null) |    (null) | (null) | (null) |     (null) |               (null) |    (null) | 2018-02-18 |
| (null) | (null) |     (null) |               (null) |    (null) | (null) | (null) |     (null) |               (null) |    (null) | 2018-02-19 |
| (null) | (null) |     (null) |               (null) |    (null) | (null) | (null) |     (null) |               (null) |    (null) | 2018-02-20 |
| (null) | (null) |     (null) |               (null) |    (null) | (null) | (null) |     (null) |               (null) |    (null) | 2018-02-21 |
|     20 |     60 | 2018-02-22 | 2018-02-22T13:58:31Z |         I |     20 |     60 | 2018-02-22 | 2018-02-22T22:20:42Z |         O |     (null) |
|     22 |     60 | 2018-02-23 | 2018-02-23T18:57:01Z |         I |     22 |     60 | 2018-02-24 | 2018-02-24T06:03:39Z |         O |     (null) |
|     24 |     60 | 2018-02-24 | 2018-02-24T21:54:01Z |         I |     24 |     60 | 2018-02-25 | 2018-02-25T07:04:37Z |         O |     (null) |
|     26 |     60 | 2018-02-25 | 2018-02-25T21:53:58Z |         I |     26 |     60 | 2018-02-26 | 2018-02-26T07:00:16Z |         O |     (null) |
|     28 |     60 | 2018-02-26 | 2018-02-26T22:03:14Z |         I |     28 |     60 | 2018-02-27 | 2018-02-27T07:04:08Z |         O |     (null) |
|     30 |     60 | 2018-02-27 | 2018-02-27T21:57:51Z |         I |     30 |     60 | 2018-02-27 | 2018-02-27T21:58:48Z |         O |     (null) |
| (null) | (null) |     (null) |               (null) |    (null) |     31 |     60 | 2018-02-28 | 2018-02-28T07:00:18Z |         O |     (null) |
| (null) | (null) |     (null) |               (null) |    (null) | (null) | (null) |     (null) |               (null) |    (null) | 2018-03-01 |

这篇关于当日期不在table1中时，SQL查询空行的出入考勤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！