SQL Query for In/Out time attendance with null row

2019-05-21 03:53发布

站内文章 / SQL-Server
49 0 0

问题:

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

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:

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')

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

回答1:

You are looking for left join and gap and islands:

Check it at SQL Fiddle

Query 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 |


标签： sql sql-server gaps-and-islands
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~