如何根据SQL中的推理列格式化数据

发布于 2025-01-23 19:34:38 字数 25992 浏览 0 评论 0原文

我有两个桌子 1）活动表（主表） 2）历史表

结构：活动表：

CREATE TABLE [dbo].[CheckInCheckOutActivity](
    [CheckInCheckOutId] [int] IDENTITY(1,1) NOT NULL,
    [TimeTicketId] [int] NULL,
    [EmployeeNbr] [varchar](50) NULL,
    [NetWorkNbr] [varchar](50) NULL,
    [Reason] [varchar](100) NULL,
    [Type] [varchar](50) NULL,
    [InsertTime] [datetime] NULL,
    [UpdateTime] [datetime] NULL,
    [UserTime] [datetime] NULL,
    [TimeTicketDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [CheckInCheckOutId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

历史表：

CREATE TABLE [dbo].[CheckInCheckOutHistoryDetails](
    [CheckInCheckoutHistoryId] [int] IDENTITY(1,1) NOT NULL,
    [CheckinCheckOutId] [int] NULL,
    [UserTime] [nvarchar](50) NULL,
    [InsertTime] [datetime] NULL,
    [Type] [nvarchar](50) NULL,
    [Reason] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
    [CheckInCheckoutHistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

活动表：

  +-------------------+--------------+-------------+------------+------------------------------+-------------+-------------------------+-------------------------+-------------------------+-------------------------+
| CheckInCheckOutId | TimeTicketId | EmployeeNbr | NetWorkNbr |            Reason            |    Type     |       InsertTime        |       UpdateTime        |        UserTime         |     TimeTicketDate      |
+-------------------+--------------+-------------+------------+------------------------------+-------------+-------------------------+-------------------------+-------------------------+-------------------------+
|               352 |      5492822 |    10020168 |   71018655 | Initial Check In             | Checked In  | 2022-02-18 08:08:21.847 | 2022-02-18 08:19:49.097 | 2022-02-18 06:15:00.000 | 2022-02-18 00:00:00.000 |
|               353 |      5492822 |    10020168 |   71018655 | Check Out For Meal Break     | Checked Out | 2022-02-18 08:17:31.883 | 2022-02-18 08:17:55.870 | 2022-02-18 08:00:00.000 | 2022-02-18 00:00:00.000 |
|               354 |      5492822 |    10020168 |   71018655 | Check In After Meal Break    | Checked In  | 2022-02-18 08:19:05.657 | 2022-02-18 08:19:30.110 | 2022-02-18 08:30:00.000 | 2022-02-18 00:00:00.000 |
|               355 |      5492822 |    10020168 |   71018655 | Check Out For Change Network | Checked Out | 2022-02-18 08:21:52.623 | 2022-02-18 08:22:23.370 | 2022-02-18 09:30:00.000 | 2022-02-18 00:00:00.000 |
+-------------------+--------------+-------------+------------+------------------------------+-------------+-------------------------+-------------------------+-------------------------+-------------------------+

2）历史表：通过传递此查询，我们将从历史表中获得USTIME和插入时间

select * from CheckInCheckOutHistoryDetails where CheckinCheckOutId=353 

+--------------------------+-------------------+---------------------+-------------------------+-------------+------------------------------+
| CheckInCheckoutHistoryId | CheckinCheckOutId |      UserTime       |       InsertTime        |    Type     |            Reason            |
+--------------------------+-------------------+---------------------+-------------------------+-------------+------------------------------+
|                      669 |               352 | Feb 18 2022  6:03AM | 2022-02-18 08:09:11.430 | Checked In  | Initial Check In             |
|                      671 |               352 | Feb 18 2022  6:15AM | 2022-02-18 08:19:49.097 | Checked In  | Initial Check In             |
|                      670 |               353 | Feb 18 2022  8:00AM | 2022-02-18 08:17:55.870 | Checked Out | Check Out For Meal Break     |
|                      672 |               354 | Feb 18 2022  8:30AM | 2022-02-18 08:19:30.110 | Checked In  | Check In After Meal Break    |
|                      673 |               355 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Checked Out | Check Out For Change Network |
|                      674 |               355 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Checked Out | Check Out For Change Network |
+--------------------------+-------------------+---------------------+-------------------------+-------------+------------------------------+

我的交叉加入查询：

 select yy.Checkincheckouthistoryid,yy.UserTime,yy.InsertTime,yy.Reason
   --,yy.EmployeeNbr,yy.NetWorkNbr,yy.TimeTicketDate
   from
    (
         select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,EmployeeNbr,NetWorkNbr,TimeTicketDate
          from CheckInCheckOutHistoryDetails CH
          inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
          where CH.CheckinCheckOutId in(
         select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
        
    )yy

交叉加入查询的结果：

+--------------------------+---------------------+-------------------------+------------------------------+
| Checkincheckouthistoryid |      UserTime       |       InsertTime        |            Reason            |
+--------------------------+---------------------+-------------------------+------------------------------+
|                      669 | Feb 18 2022  6:03AM | 2022-02-18 08:09:11.430 | Initial Check In             |
|                      670 | Feb 18 2022  8:00AM | 2022-02-18 08:17:55.870 | Check Out For Meal Break     |
|                      671 | Feb 18 2022  6:15AM | 2022-02-18 08:19:49.097 | Initial Check In             |
|                      672 | Feb 18 2022  8:30AM | 2022-02-18 08:19:30.110 | Check In After Meal Break    |
|                      673 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Check Out For Change Network |
|                      674 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Check Out For Change Network |
+--------------------------+---------------------+-------------------------+------------------------------+

这是我需要数据的所有原因：

[Initial Check In] ,[Check Out For Meal Break],[Check In After Meal Break],[First Meal Break Alert],[Check In After First Meal Break],[Second Meal Break Alert],[Check In After Second Meal Break],[Check Out For Change Network],[Check in After Change Network],[Check Out For Personal Time],[Check In After Personal Time],[Check Out For End Of Shift]

结果预期格式：

| [Initial Check In_UsertTime]  [Initial Check In_SysTime][Check Out For Meal Break UserTime] [Check Out For Meal Break SysTime][Check In After Meal BreakUserTime] [Check In After Meal BreakSysTime][First Meal Break Alert UserTime] [First Meal Break Alert SysTime][Check In After First Meal Break UserTime] [Check In After First Meal Break SysTime][Second Meal Break Alert UserTime] [Second Meal Break Alert SysTime][Check In After Second Meal Break UserTime ] [Check In After Second Meal Break SysTime ][Check Out For Change Network UserTime] [Check Out For Change Network SysTime][Check in After Change Network UserTime] [Check in After Change Network SysTime] [Check Out For Personal Time UserTime] [Check Out For Personal Time SysTime][Check In After Personal Time UserTime] [Check In After Personal Time SysTime][Check Out For End Of Shift UserTime] [Check Out For End Of Shift SysTime]                       

    | Feb 18 2022  6:03AM            2022-02-18 08:09:11.430 |  |   Feb 18 2022  8:00AM |   |  |  2022-02-18 08:17:55.870 |  |  |  |    Feb 18 2022  8:30AM |  |  |           2022-02-18 08:19:30.110 |          |   null |  |  |  |  |  |            | null |  |  |  |  |  |            | null |  |  |  |  |  |  |  |          |  |  null |  |  |  |  |  |  |  |  |  |   null |  |  |  |  |  |  |            |    null |  |  |  |                |  | null |  |  |  |  |  |  |  |  |          |  |    null |  |  |  |  |               |  null |  |  |  |  |  |  |  |  |  |  |  | null |  |  |  |  |  |  |  |  |    null |  |  |  |  |  |  |                |  null |  |  |  |  |  |             |  |   null |  |  |  |  |  |  |  |  |  |  null |  |  |  |  |  |              |    null |  |  |  |  |  |  |  |  |  null |  |  |  |  |  |  |                     |   null |  |  |  |  |                  |  | null

注意：如果在那里。是一个用户时间和系统时间，出于相同的原因，在下一行中，我们需要显示该数据

当我使用Pivot时，我不会按预期获得结果：

我的枢轴查询：

select kk.InitalCheckin_UserTime,kk.Checkoutformealbreak_UserTime,kk.CheckInAfterMealBreak_UserTime,kk.FirstMealBreakAlert_UserTime,kk.CheckInAfterFirstMealBreak_UserTime,kk.SecondMealBreakAlert_UserTime,kk.CheckInAfterSecondMealBreak_UserTime,kk.CheckOutForChangeNetwork_UserTime,kk.CheckinAfterChangeNetwork_UserTime,kk.CheckOutForPersonalTime_UserTime,kk.CheckInAfterPersonalTime_UserTime,kk.CheckOutForEndOfShift_UserTime
 ,
 rr.InitalCheckin_SysTime,rr.Checkoutformealbreak_SysTime,rr.CheckInAfterMealBreak_SysTime,rr.FirstMealBreakAlert_SysTime,rr.CheckInAfterFirstMealBreak_SysTime,rr.SecondMealBreakAlert_SysTime,rr.CheckInAfterSecondMealBreak_SysTime,rr.CheckOutForChangeNetwork_SysTime,rr.CheckinAfterChangeNetwork_SysTime,rr.CheckOutForPersonalTime_SysTime,rr.CheckInAfterPersonalTime_SysTime,rr.CheckOutForEndOfShift_SysTime
, rr.checkindate
 from
(

select

InitalCheckin_UserTime=[Initial Check In],
Checkoutformealbreak_UserTime=[Check Out For Meal Break]
,CheckInAfterMealBreak_UserTime=[Check In After Meal Break]
,FirstMealBreakAlert_UserTime=[First Meal Break Alert],
CheckInAfterFirstMealBreak_UserTime=[Check In After First Meal Break],
SecondMealBreakAlert_UserTime=[Second Meal Break Alert],
CheckInAfterSecondMealBreak_UserTime=[Check In After Second Meal Break],
CheckOutForChangeNetwork_UserTime=[Check Out For Change Network],
CheckinAfterChangeNetwork_UserTime=[Check in After Change Network],
CheckOutForPersonalTime_UserTime=[Check Out For Personal Time],
CheckInAfterPersonalTime_UserTime=[Check In After Personal Time],
CheckOutForEndOfShift_UserTime=[Check Out For End Of Shift],
checkindate = getdate()

from
(

   select ss.Checkincheckouthistoryid,ss.UserTime,ss.InsertTime,ss.Reason,ss.Reason1
   from
    (
         select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,CH.Reason as Reason1
          from CheckInCheckOutHistoryDetails CH
          inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
          where CH.CheckinCheckOutId in(
         select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
        
    )ss 
)d
        pivot
        (
         max(UserTime) for Reason in ([Initial Check In] ,[Check Out For Meal Break],[Check In After Meal Break],[First Meal Break Alert],[Check In After First Meal Break],[Second Meal Break Alert],[Check In After Second Meal Break],[Check Out For Change Network],[Check in After Change Network],[Check Out For Personal Time],[Check In After Personal Time],[Check Out For End Of Shift]) 

        ) As pivasd
        

) as kk  left outer join 

(
select 
InitalCheckin_SysTime=[Initial Check In],
Checkoutformealbreak_SysTime=[Check Out For Meal Break],
CheckInAfterMealBreak_SysTime=[Check In After Meal Break],
FirstMealBreakAlert_SysTime=[First Meal Break Alert],
CheckInAfterFirstMealBreak_SysTime=[Check In After First Meal Break],
SecondMealBreakAlert_SysTime=[Second Meal Break Alert],
CheckInAfterSecondMealBreak_SysTime=[Check In After Second Meal Break],
CheckOutForChangeNetwork_SysTime=[Check Out For Change Network],
CheckinAfterChangeNetwork_SysTime=[Check in After Change Network],
CheckOutForPersonalTime_SysTime=[Check Out For Personal Time],
CheckInAfterPersonalTime_SysTime=[Check In After Personal Time],
CheckOutForEndOfShift_SysTime=[Check Out For End Of Shift],
checkindate=GETDATE()

--EmployeeNbr,NetWorkNbr,TimeTicketDate




from
(
   select yy.Checkincheckouthistoryid,yy.UserTime,yy.InsertTime,yy.Reason,yy.Reason1
   --,yy.EmployeeNbr,yy.NetWorkNbr,yy.TimeTicketDate
   from
    (
         select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,CH.Reason as Reason1,EmployeeNbr,NetWorkNbr,TimeTicketDate
          from CheckInCheckOutHistoryDetails CH
          inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
          where CH.CheckinCheckOutId in(
         select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
        
    )yy 
)c

   
        pivot
        (
         max(InsertTime) for Reason1 in ([Initial Check In] ,[Check Out For Meal Break],[Check In After Meal Break],[First Meal Break Alert],[Check In After First Meal Break],[Second Meal Break Alert],[Check In After Second Meal Break],[Check Out For Change Network],[Check in After Change Network],[Check Out For Personal Time],[Check In After Personal Time],[Check Out For End Of Shift])
        ) As piva
        


)  as rr on kk.checkindate = rr.checkindate
group by 
kk.InitalCheckin_UserTime,kk.Checkoutformealbreak_UserTime,kk.CheckInAfterMealBreak_UserTime,kk.FirstMealBreakAlert_UserTime,kk.CheckInAfterFirstMealBreak_UserTime,kk.SecondMealBreakAlert_UserTime,kk.CheckInAfterSecondMealBreak_UserTime,kk.CheckOutForChangeNetwork_UserTime,kk.CheckinAfterChangeNetwork_UserTime,kk.CheckOutForPersonalTime_UserTime,kk.CheckInAfterPersonalTime_UserTime,kk.CheckOutForEndOfShift_UserTime
,
rr.InitalCheckin_SysTime,rr.Checkoutformealbreak_SysTime,rr.CheckInAfterMealBreak_SysTime,rr.FirstMealBreakAlert_SysTime,rr.CheckInAfterFirstMealBreak_SysTime,rr.SecondMealBreakAlert_SysTime,rr.CheckInAfterSecondMealBreak_SysTime,rr.CheckOutForChangeNetwork_SysTime,rr.CheckinAfterChangeNetwork_SysTime,rr.CheckOutForPersonalTime_SysTime,rr.CheckInAfterPersonalTime_SysTime,rr.CheckOutForEndOfShift_SysTime
,rr.checkindate

枢轴查询的结果：

+------------------------+-------------------------------+--------------------------------+------------------------------+-------------------------------------+-------------------------------+--------------------------------------+-----------------------------------+------------------------------------+----------------------------------+-----------------------------------+--------------------------------+-------------------------+------------------------------+-------------------------------+-----------------------------+------------------------------------+------------------------------+-------------------------------------+----------------------------------+-----------------------------------+---------------------------------+----------------------------------+-------------------------------+-------------------------+
| InitalCheckin_UserTime | Checkoutformealbreak_UserTime | CheckInAfterMealBreak_UserTime | FirstMealBreakAlert_UserTime | CheckInAfterFirstMealBreak_UserTime | SecondMealBreakAlert_UserTime | CheckInAfterSecondMealBreak_UserTime | CheckOutForChangeNetwork_UserTime | CheckinAfterChangeNetwork_UserTime | CheckOutForPersonalTime_UserTime | CheckInAfterPersonalTime_UserTime | CheckOutForEndOfShift_UserTime |  InitalCheckin_SysTime  | Checkoutformealbreak_SysTime | CheckInAfterMealBreak_SysTime | FirstMealBreakAlert_SysTime | CheckInAfterFirstMealBreak_SysTime | SecondMealBreakAlert_SysTime | CheckInAfterSecondMealBreak_SysTime | CheckOutForChangeNetwork_SysTime | CheckinAfterChangeNetwork_SysTime | CheckOutForPersonalTime_SysTime | CheckInAfterPersonalTime_SysTime | CheckOutForEndOfShift_SysTime |       checkindate       |
+------------------------+-------------------------------+--------------------------------+------------------------------+-------------------------------------+-------------------------------+--------------------------------------+-----------------------------------+------------------------------------+----------------------------------+-----------------------------------+--------------------------------+-------------------------+------------------------------+-------------------------------+-----------------------------+------------------------------------+------------------------------+-------------------------------------+----------------------------------+-----------------------------------+---------------------------------+----------------------------------+-------------------------------+-------------------------+
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | 2022-02-18 08:22:23.370          | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | 2022-02-18 08:19:30.110       | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | 2022-02-18 08:17:55.870      | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:09:11.430 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:19:49.097 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | 2022-02-18 08:22:23.370          | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | 2022-02-18 08:19:30.110       | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | 2022-02-18 08:17:55.870      | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:09:11.430 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:19:49.097 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |

+------------------------+-------------------------------+--------------------------------+------------------------------+-------------------------------------+-------------------------------+--------------------------------------+-----------------------------------+------------------------------------+----------------------------------+-----------------------------------+--------------------------------+-------------------------+------------------------------+-------------------------------+-----------------------------+------------------------------------+------------------------------+-------------------------------------+----------------------------------+-----------------------------------+---------------------------------+----------------------------------+-------------------------------+-------------------------+

我没有得到上述查询所预期的结果我得到的25行数据不准确，我只需要5行数据

原文

I am having Two Tables
1)Activity Table (main table)
2)History Table

Table Structure:
Activity Table:

CREATE TABLE [dbo].[CheckInCheckOutActivity](
    [CheckInCheckOutId] [int] IDENTITY(1,1) NOT NULL,
    [TimeTicketId] [int] NULL,
    [EmployeeNbr] [varchar](50) NULL,
    [NetWorkNbr] [varchar](50) NULL,
    [Reason] [varchar](100) NULL,
    [Type] [varchar](50) NULL,
    [InsertTime] [datetime] NULL,
    [UpdateTime] [datetime] NULL,
    [UserTime] [datetime] NULL,
    [TimeTicketDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [CheckInCheckOutId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

History Table:

CREATE TABLE [dbo].[CheckInCheckOutHistoryDetails](
    [CheckInCheckoutHistoryId] [int] IDENTITY(1,1) NOT NULL,
    [CheckinCheckOutId] [int] NULL,
    [UserTime] [nvarchar](50) NULL,
    [InsertTime] [datetime] NULL,
    [Type] [nvarchar](50) NULL,
    [Reason] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
    [CheckInCheckoutHistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Activity Table:

  +-------------------+--------------+-------------+------------+------------------------------+-------------+-------------------------+-------------------------+-------------------------+-------------------------+
| CheckInCheckOutId | TimeTicketId | EmployeeNbr | NetWorkNbr |            Reason            |    Type     |       InsertTime        |       UpdateTime        |        UserTime         |     TimeTicketDate      |
+-------------------+--------------+-------------+------------+------------------------------+-------------+-------------------------+-------------------------+-------------------------+-------------------------+
|               352 |      5492822 |    10020168 |   71018655 | Initial Check In             | Checked In  | 2022-02-18 08:08:21.847 | 2022-02-18 08:19:49.097 | 2022-02-18 06:15:00.000 | 2022-02-18 00:00:00.000 |
|               353 |      5492822 |    10020168 |   71018655 | Check Out For Meal Break     | Checked Out | 2022-02-18 08:17:31.883 | 2022-02-18 08:17:55.870 | 2022-02-18 08:00:00.000 | 2022-02-18 00:00:00.000 |
|               354 |      5492822 |    10020168 |   71018655 | Check In After Meal Break    | Checked In  | 2022-02-18 08:19:05.657 | 2022-02-18 08:19:30.110 | 2022-02-18 08:30:00.000 | 2022-02-18 00:00:00.000 |
|               355 |      5492822 |    10020168 |   71018655 | Check Out For Change Network | Checked Out | 2022-02-18 08:21:52.623 | 2022-02-18 08:22:23.370 | 2022-02-18 09:30:00.000 | 2022-02-18 00:00:00.000 |
+-------------------+--------------+-------------+------------+------------------------------+-------------+-------------------------+-------------------------+-------------------------+-------------------------+

2)History Table:
By Passing this Query we will be getting Usertime and Insertime from the History Table

select * from CheckInCheckOutHistoryDetails where CheckinCheckOutId=353 

+--------------------------+-------------------+---------------------+-------------------------+-------------+------------------------------+
| CheckInCheckoutHistoryId | CheckinCheckOutId |      UserTime       |       InsertTime        |    Type     |            Reason            |
+--------------------------+-------------------+---------------------+-------------------------+-------------+------------------------------+
|                      669 |               352 | Feb 18 2022  6:03AM | 2022-02-18 08:09:11.430 | Checked In  | Initial Check In             |
|                      671 |               352 | Feb 18 2022  6:15AM | 2022-02-18 08:19:49.097 | Checked In  | Initial Check In             |
|                      670 |               353 | Feb 18 2022  8:00AM | 2022-02-18 08:17:55.870 | Checked Out | Check Out For Meal Break     |
|                      672 |               354 | Feb 18 2022  8:30AM | 2022-02-18 08:19:30.110 | Checked In  | Check In After Meal Break    |
|                      673 |               355 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Checked Out | Check Out For Change Network |
|                      674 |               355 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Checked Out | Check Out For Change Network |
+--------------------------+-------------------+---------------------+-------------------------+-------------+------------------------------+

My Cross Join Query:

 select yy.Checkincheckouthistoryid,yy.UserTime,yy.InsertTime,yy.Reason
   --,yy.EmployeeNbr,yy.NetWorkNbr,yy.TimeTicketDate
   from
    (
         select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,EmployeeNbr,NetWorkNbr,TimeTicketDate
          from CheckInCheckOutHistoryDetails CH
          inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
          where CH.CheckinCheckOutId in(
         select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
        
    )yy

Result Of Cross Join Query:

+--------------------------+---------------------+-------------------------+------------------------------+
| Checkincheckouthistoryid |      UserTime       |       InsertTime        |            Reason            |
+--------------------------+---------------------+-------------------------+------------------------------+
|                      669 | Feb 18 2022  6:03AM | 2022-02-18 08:09:11.430 | Initial Check In             |
|                      670 | Feb 18 2022  8:00AM | 2022-02-18 08:17:55.870 | Check Out For Meal Break     |
|                      671 | Feb 18 2022  6:15AM | 2022-02-18 08:19:49.097 | Initial Check In             |
|                      672 | Feb 18 2022  8:30AM | 2022-02-18 08:19:30.110 | Check In After Meal Break    |
|                      673 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Check Out For Change Network |
|                      674 | Feb 18 2022  9:30AM | 2022-02-18 08:22:23.370 | Check Out For Change Network |
+--------------------------+---------------------+-------------------------+------------------------------+

This are all the Reason for which i need the Data:

[Initial Check In] ,[Check Out For Meal Break],[Check In After Meal Break],[First Meal Break Alert],[Check In After First Meal Break],[Second Meal Break Alert],[Check In After Second Meal Break],[Check Out For Change Network],[Check in After Change Network],[Check Out For Personal Time],[Check In After Personal Time],[Check Out For End Of Shift]

The Result Expected Format:

| [Initial Check In_UsertTime]  [Initial Check In_SysTime][Check Out For Meal Break UserTime] [Check Out For Meal Break SysTime][Check In After Meal BreakUserTime] [Check In After Meal BreakSysTime][First Meal Break Alert UserTime] [First Meal Break Alert SysTime][Check In After First Meal Break UserTime] [Check In After First Meal Break SysTime][Second Meal Break Alert UserTime] [Second Meal Break Alert SysTime][Check In After Second Meal Break UserTime ] [Check In After Second Meal Break SysTime ][Check Out For Change Network UserTime] [Check Out For Change Network SysTime][Check in After Change Network UserTime] [Check in After Change Network SysTime] [Check Out For Personal Time UserTime] [Check Out For Personal Time SysTime][Check In After Personal Time UserTime] [Check In After Personal Time SysTime][Check Out For End Of Shift UserTime] [Check Out For End Of Shift SysTime]                       

    | Feb 18 2022  6:03AM            2022-02-18 08:09:11.430 |  |   Feb 18 2022  8:00AM |   |  |  2022-02-18 08:17:55.870 |  |  |  |    Feb 18 2022  8:30AM |  |  |           2022-02-18 08:19:30.110 |          |   null |  |  |  |  |  |            | null |  |  |  |  |  |            | null |  |  |  |  |  |  |  |          |  |  null |  |  |  |  |  |  |  |  |  |   null |  |  |  |  |  |  |            |    null |  |  |  |                |  | null |  |  |  |  |  |  |  |  |          |  |    null |  |  |  |  |               |  null |  |  |  |  |  |  |  |  |  |  |  | null |  |  |  |  |  |  |  |  |    null |  |  |  |  |  |  |                |  null |  |  |  |  |  |             |  |   null |  |  |  |  |  |  |  |  |  |  null |  |  |  |  |  |              |    null |  |  |  |  |  |  |  |  |  null |  |  |  |  |  |  |                     |   null |  |  |  |  |                  |  | null

Note: If there is a user time and SystemTime for same reason then in next Row we need to display that data

When I Use Pivot I am Not getting the Result as Expected:

My Pivot Query:

select kk.InitalCheckin_UserTime,kk.Checkoutformealbreak_UserTime,kk.CheckInAfterMealBreak_UserTime,kk.FirstMealBreakAlert_UserTime,kk.CheckInAfterFirstMealBreak_UserTime,kk.SecondMealBreakAlert_UserTime,kk.CheckInAfterSecondMealBreak_UserTime,kk.CheckOutForChangeNetwork_UserTime,kk.CheckinAfterChangeNetwork_UserTime,kk.CheckOutForPersonalTime_UserTime,kk.CheckInAfterPersonalTime_UserTime,kk.CheckOutForEndOfShift_UserTime
 ,
 rr.InitalCheckin_SysTime,rr.Checkoutformealbreak_SysTime,rr.CheckInAfterMealBreak_SysTime,rr.FirstMealBreakAlert_SysTime,rr.CheckInAfterFirstMealBreak_SysTime,rr.SecondMealBreakAlert_SysTime,rr.CheckInAfterSecondMealBreak_SysTime,rr.CheckOutForChangeNetwork_SysTime,rr.CheckinAfterChangeNetwork_SysTime,rr.CheckOutForPersonalTime_SysTime,rr.CheckInAfterPersonalTime_SysTime,rr.CheckOutForEndOfShift_SysTime
, rr.checkindate
 from
(

select

InitalCheckin_UserTime=[Initial Check In],
Checkoutformealbreak_UserTime=[Check Out For Meal Break]
,CheckInAfterMealBreak_UserTime=[Check In After Meal Break]
,FirstMealBreakAlert_UserTime=[First Meal Break Alert],
CheckInAfterFirstMealBreak_UserTime=[Check In After First Meal Break],
SecondMealBreakAlert_UserTime=[Second Meal Break Alert],
CheckInAfterSecondMealBreak_UserTime=[Check In After Second Meal Break],
CheckOutForChangeNetwork_UserTime=[Check Out For Change Network],
CheckinAfterChangeNetwork_UserTime=[Check in After Change Network],
CheckOutForPersonalTime_UserTime=[Check Out For Personal Time],
CheckInAfterPersonalTime_UserTime=[Check In After Personal Time],
CheckOutForEndOfShift_UserTime=[Check Out For End Of Shift],
checkindate = getdate()

from
(

   select ss.Checkincheckouthistoryid,ss.UserTime,ss.InsertTime,ss.Reason,ss.Reason1
   from
    (
         select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,CH.Reason as Reason1
          from CheckInCheckOutHistoryDetails CH
          inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
          where CH.CheckinCheckOutId in(
         select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
        
    )ss 
)d
        pivot
        (
         max(UserTime) for Reason in ([Initial Check In] ,[Check Out For Meal Break],[Check In After Meal Break],[First Meal Break Alert],[Check In After First Meal Break],[Second Meal Break Alert],[Check In After Second Meal Break],[Check Out For Change Network],[Check in After Change Network],[Check Out For Personal Time],[Check In After Personal Time],[Check Out For End Of Shift]) 

        ) As pivasd
        

) as kk  left outer join 

(
select 
InitalCheckin_SysTime=[Initial Check In],
Checkoutformealbreak_SysTime=[Check Out For Meal Break],
CheckInAfterMealBreak_SysTime=[Check In After Meal Break],
FirstMealBreakAlert_SysTime=[First Meal Break Alert],
CheckInAfterFirstMealBreak_SysTime=[Check In After First Meal Break],
SecondMealBreakAlert_SysTime=[Second Meal Break Alert],
CheckInAfterSecondMealBreak_SysTime=[Check In After Second Meal Break],
CheckOutForChangeNetwork_SysTime=[Check Out For Change Network],
CheckinAfterChangeNetwork_SysTime=[Check in After Change Network],
CheckOutForPersonalTime_SysTime=[Check Out For Personal Time],
CheckInAfterPersonalTime_SysTime=[Check In After Personal Time],
CheckOutForEndOfShift_SysTime=[Check Out For End Of Shift],
checkindate=GETDATE()

--EmployeeNbr,NetWorkNbr,TimeTicketDate




from
(
   select yy.Checkincheckouthistoryid,yy.UserTime,yy.InsertTime,yy.Reason,yy.Reason1
   --,yy.EmployeeNbr,yy.NetWorkNbr,yy.TimeTicketDate
   from
    (
         select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,CH.Reason as Reason1,EmployeeNbr,NetWorkNbr,TimeTicketDate
          from CheckInCheckOutHistoryDetails CH
          inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
          where CH.CheckinCheckOutId in(
         select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
        
    )yy 
)c

   
        pivot
        (
         max(InsertTime) for Reason1 in ([Initial Check In] ,[Check Out For Meal Break],[Check In After Meal Break],[First Meal Break Alert],[Check In After First Meal Break],[Second Meal Break Alert],[Check In After Second Meal Break],[Check Out For Change Network],[Check in After Change Network],[Check Out For Personal Time],[Check In After Personal Time],[Check Out For End Of Shift])
        ) As piva
        


)  as rr on kk.checkindate = rr.checkindate
group by 
kk.InitalCheckin_UserTime,kk.Checkoutformealbreak_UserTime,kk.CheckInAfterMealBreak_UserTime,kk.FirstMealBreakAlert_UserTime,kk.CheckInAfterFirstMealBreak_UserTime,kk.SecondMealBreakAlert_UserTime,kk.CheckInAfterSecondMealBreak_UserTime,kk.CheckOutForChangeNetwork_UserTime,kk.CheckinAfterChangeNetwork_UserTime,kk.CheckOutForPersonalTime_UserTime,kk.CheckInAfterPersonalTime_UserTime,kk.CheckOutForEndOfShift_UserTime
,
rr.InitalCheckin_SysTime,rr.Checkoutformealbreak_SysTime,rr.CheckInAfterMealBreak_SysTime,rr.FirstMealBreakAlert_SysTime,rr.CheckInAfterFirstMealBreak_SysTime,rr.SecondMealBreakAlert_SysTime,rr.CheckInAfterSecondMealBreak_SysTime,rr.CheckOutForChangeNetwork_SysTime,rr.CheckinAfterChangeNetwork_SysTime,rr.CheckOutForPersonalTime_SysTime,rr.CheckInAfterPersonalTime_SysTime,rr.CheckOutForEndOfShift_SysTime
,rr.checkindate

Result of the Pivot Query:

+------------------------+-------------------------------+--------------------------------+------------------------------+-------------------------------------+-------------------------------+--------------------------------------+-----------------------------------+------------------------------------+----------------------------------+-----------------------------------+--------------------------------+-------------------------+------------------------------+-------------------------------+-----------------------------+------------------------------------+------------------------------+-------------------------------------+----------------------------------+-----------------------------------+---------------------------------+----------------------------------+-------------------------------+-------------------------+
| InitalCheckin_UserTime | Checkoutformealbreak_UserTime | CheckInAfterMealBreak_UserTime | FirstMealBreakAlert_UserTime | CheckInAfterFirstMealBreak_UserTime | SecondMealBreakAlert_UserTime | CheckInAfterSecondMealBreak_UserTime | CheckOutForChangeNetwork_UserTime | CheckinAfterChangeNetwork_UserTime | CheckOutForPersonalTime_UserTime | CheckInAfterPersonalTime_UserTime | CheckOutForEndOfShift_UserTime |  InitalCheckin_SysTime  | Checkoutformealbreak_SysTime | CheckInAfterMealBreak_SysTime | FirstMealBreakAlert_SysTime | CheckInAfterFirstMealBreak_SysTime | SecondMealBreakAlert_SysTime | CheckInAfterSecondMealBreak_SysTime | CheckOutForChangeNetwork_SysTime | CheckinAfterChangeNetwork_SysTime | CheckOutForPersonalTime_SysTime | CheckInAfterPersonalTime_SysTime | CheckOutForEndOfShift_SysTime |       checkindate       |
+------------------------+-------------------------------+--------------------------------+------------------------------+-------------------------------------+-------------------------------+--------------------------------------+-----------------------------------+------------------------------------+----------------------------------+-----------------------------------+--------------------------------+-------------------------+------------------------------+-------------------------------+-----------------------------+------------------------------------+------------------------------+-------------------------------------+----------------------------------+-----------------------------------+---------------------------------+----------------------------------+-------------------------------+-------------------------+
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | 2022-02-18 08:22:23.370          | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | 2022-02-18 08:19:30.110       | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | 2022-02-18 08:17:55.870      | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:09:11.430 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | NULL                           | NULL                         | NULL                                | NULL                          | NULL                                 | Feb 18 2022  9:30AM               | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:19:49.097 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | 2022-02-18 08:22:23.370          | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | NULL                         | 2022-02-18 08:19:30.110       | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | NULL                    | 2022-02-18 08:17:55.870      | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:09:11.430 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |
| NULL                   | NULL                          | Feb 18 2022  8:30AM            | NULL                         | NULL                                | NULL                          | NULL                                 | NULL                              | NULL                               | NULL                             | NULL                              | NULL                           | 2022-02-18 08:19:49.097 | NULL                         | NULL                          | NULL                        | NULL                               | NULL                         | NULL                                | NULL                             | NULL                              | NULL                            | NULL                             | NULL                          | 2022-04-25 06:59:06.163 |

+------------------------+-------------------------------+--------------------------------+------------------------------+-------------------------------------+-------------------------------+--------------------------------------+-----------------------------------+------------------------------------+----------------------------------+-----------------------------------+--------------------------------+-------------------------+------------------------------+-------------------------------+-----------------------------+------------------------------------+------------------------------+-------------------------------------+----------------------------------+-----------------------------------+---------------------------------+----------------------------------+-------------------------------+-------------------------+

I am not getting the result as expected by above query
I am getting 25 rows of data which are not accurate I only need 5 rows of data

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吻安 2025-01-30 19:34:38

您可以使用标量功能获取所需的列。例如：

CREATE FUNCTION base.function1 (@Checkincheckouthistoryid int)
RETURNS datetime
as begin
declare @dateTime datetime = (select UsertTime from 
from
(
     select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,EmployeeNbr,NetWorkNbr,TimeTicketDate
      from CheckInCheckOutHistoryDetails CH
      inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
      where CH.CheckinCheckOutId in(
     select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
    
)yy where 
Checkincheckouthistoryid = @Checkincheckouthistoryid )
end
GO

然后将其用作无处可访问“ CheckInCheckouthistoryId”的列

You can use the scalar functions to get the required columns. for example:

CREATE FUNCTION base.function1 (@Checkincheckouthistoryid int)
RETURNS datetime
as begin
declare @dateTime datetime = (select UsertTime from 
from
(
     select CH.CheckInCheckoutHistoryId,CH.UserTime,CH.InsertTime,CH.Reason,EmployeeNbr,NetWorkNbr,TimeTicketDate
      from CheckInCheckOutHistoryDetails CH
      inner join CheckInCheckOutActivity CM on CH.CheckinCheckOutId = CM.CheckinCheckOutId and CH.Reason = CM.Reason
      where CH.CheckinCheckOutId in(
     select CheckinCheckOutId from CheckInCheckOutActivity where EmployeeNbr=10020168 and NetWorkNbr=71018655 and TimeTicketDate='2022-02-18 00:00:00.000')
    
)yy where 
Checkincheckouthistoryid = @Checkincheckouthistoryid )
end
GO

then use it as a column everywhere you access to "Checkincheckouthistoryid"

回复收藏 0 原文

~没有更多了~