Remove From My Forums

Rows to Cols based on date split (MS SQL)

Question

User-582711651 posted

Hi Friends,

Help me how to transpose Row to cols based on the date,

my Table is

USE [HRMDB_BS]
GO
/****** Object:  Table [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN]    Script Date: 9/27/2019 12:15:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN](
	[HR_IDT] [int] IDENTITY(1,1) NOT NULL,
	[HR_Supervisor_ID] [int] NOT NULL,
	[HR_Approver_ID] [int] NOT NULL,
	[HR_EmployeeCode] [int] NOT NULL,
	[HR_TAB_LogAttd_MarkTypeID] [int] NOT NULL,
	[HR_TAB_LogAttd_MarkedOn] [date] NOT NULL,
	[HR_Record_Remarks] [varchar](100) NULL,
	[HR_Record_Posted_ByUserId] [int] NOT NULL,
	[HR_Record_Posted_TimeStamp] [datetime] NOT NULL,
	[HR_Record_Modified_ByUserId] [int] NULL,
	[HR_Record_Modified_TimeStamp] [datetime] NULL,
	[HR_Record_Deleted_ByUserId] [int] NULL,
	[HR_Record_Deleted_TimeStamp] [datetime] NULL,
	[HR_System_IP_Addr] [varchar](100) NULL,
	[HR_RecordActiveId] [int] NOT NULL,
 CONSTRAINT [PK_HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] PRIMARY KEY CLUSTERED 
(
	[HR_EmployeeCode] ASC,
	[HR_TAB_LogAttd_MarkedOn] ASC,
	[HR_RecordActiveId] 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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ON 

GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (40, 414, 0, 2206, 1, CAST(N'2019-09-01' AS Date), N'0', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (48, 414, 0, 2206, 3, CAST(N'2019-09-02' AS Date), N'1', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (56, 414, 0, 2206, 1, CAST(N'2019-09-03' AS Date), N'0', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (9, 414, 0, 2206, 1, CAST(N'2019-09-24' AS Date), N'0', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (1, 414, 0, 2206, 3, CAST(N'2019-09-26' AS Date), N'1', 414, CAST(N'2019-09-26 23:23:44.060' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (32, 414, 0, 2206, 1, CAST(N'2019-09-27' AS Date), N'0', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (17, 414, 0, 2206, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (25, 414, 0, 2206, 2, CAST(N'2019-09-29' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:51.733' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (41, 414, 0, 3725, 2, CAST(N'2019-09-01' AS Date), N'0', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (49, 414, 0, 3725, 2, CAST(N'2019-09-02' AS Date), N'1', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (57, 414, 0, 3725, 2, CAST(N'2019-09-03' AS Date), N'0', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (10, 414, 0, 3725, 1, CAST(N'2019-09-24' AS Date), N'0', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (2, 414, 0, 3725, 2, CAST(N'2019-09-26' AS Date), N'1', 414, CAST(N'2019-09-26 23:23:44.060' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (33, 414, 0, 3725, 3, CAST(N'2019-09-27' AS Date), N'2', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (18, 414, 0, 3725, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (42, 414, 0, 4453, 2, CAST(N'2019-09-01' AS Date), N'0', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (50, 414, 0, 4453, 2, CAST(N'2019-09-02' AS Date), N'1', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (58, 414, 0, 4453, 2, CAST(N'2019-09-03' AS Date), N'0', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (11, 414, 0, 4453, 1, CAST(N'2019-09-24' AS Date), N'0', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (3, 414, 0, 4453, 1, CAST(N'2019-09-26' AS Date), N'1', 414, CAST(N'2019-09-26 23:23:44.060' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (34, 414, 0, 4453, 3, CAST(N'2019-09-27' AS Date), N'4', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (19, 414, 0, 4453, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (26, 414, 0, 4453, 2, CAST(N'2019-09-29' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:51.733' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (43, 414, 0, 4454, 3, CAST(N'2019-09-01' AS Date), N'1', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (51, 414, 0, 4454, 3, CAST(N'2019-09-02' AS Date), N'5', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (59, 414, 0, 4454, 2, CAST(N'2019-09-03' AS Date), N'0', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (12, 414, 0, 4454, 1, CAST(N'2019-09-24' AS Date), N'0', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (4, 414, 0, 4454, 1, CAST(N'2019-09-26' AS Date), N'1', 414, CAST(N'2019-09-26 23:23:44.060' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (35, 414, 0, 4454, 1, CAST(N'2019-09-27' AS Date), N'4', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (20, 414, 0, 4454, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (27, 414, 0, 4454, 1, CAST(N'2019-09-29' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:51.733' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (44, 414, 0, 5782, 3, CAST(N'2019-09-01' AS Date), N'4', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (52, 414, 0, 5782, 3, CAST(N'2019-09-02' AS Date), N'2', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (60, 414, 0, 5782, 1, CAST(N'2019-09-03' AS Date), N'0', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (13, 414, 0, 5782, 1, CAST(N'2019-09-24' AS Date), N'0', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (5, 414, 0, 5782, 1, CAST(N'2019-09-26' AS Date), N'1', 414, CAST(N'2019-09-26 23:23:44.060' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (36, 414, 0, 5782, 1, CAST(N'2019-09-27' AS Date), N'4', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (21, 414, 0, 5782, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (28, 414, 0, 5782, 1, CAST(N'2019-09-29' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:51.733' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (45, 414, 0, 6514, 3, CAST(N'2019-09-01' AS Date), N'3', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (53, 414, 0, 6514, 3, CAST(N'2019-09-02' AS Date), N'1', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (61, 414, 0, 6514, 1, CAST(N'2019-09-03' AS Date), N'0', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (14, 414, 0, 6514, 3, CAST(N'2019-09-24' AS Date), N'1', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (6, 414, 0, 6514, 1, CAST(N'2019-09-26' AS Date), N'1', 414, CAST(N'2019-09-26 23:23:44.060' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (37, 414, 0, 6514, 1, CAST(N'2019-09-27' AS Date), N'4', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (22, 414, 0, 6514, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (29, 414, 0, 6514, 1, CAST(N'2019-09-29' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:51.733' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (46, 414, 0, 6730, 2, CAST(N'2019-09-01' AS Date), N'3', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (54, 414, 0, 6730, 3, CAST(N'2019-09-02' AS Date), N'3', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (62, 414, 0, 6730, 1, CAST(N'2019-09-03' AS Date), N'0', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (15, 414, 0, 6730, 2, CAST(N'2019-09-24' AS Date), N'1', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (7, 414, 0, 6730, 2, CAST(N'2019-09-26' AS Date), N'1', 414, CAST(N'2019-09-26 23:23:44.060' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (38, 414, 0, 6730, 1, CAST(N'2019-09-27' AS Date), N'4', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (23, 414, 0, 6730, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (30, 414, 0, 6730, 1, CAST(N'2019-09-29' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:51.733' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (47, 414, 0, 6875, 1, CAST(N'2019-09-01' AS Date), N'3', 414, CAST(N'2019-09-27 10:31:14.907' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (55, 414, 0, 6875, 3, CAST(N'2019-09-02' AS Date), N'4', 414, CAST(N'2019-09-27 10:31:59.493' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (63, 414, 0, 6875, 3, CAST(N'2019-09-03' AS Date), N'3', 414, CAST(N'2019-09-27 10:32:25.033' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (16, 414, 0, 6875, 3, CAST(N'2019-09-24' AS Date), N'2', 414, CAST(N'2019-09-26 23:47:51.110' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (8, 414, 0, 6875, 2, CAST(N'2019-09-26' AS Date), N'0', 414, CAST(N'2019-09-26 23:34:43.777' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (39, 414, 0, 6875, 1, CAST(N'2019-09-27' AS Date), N'4', 414, CAST(N'2019-09-27 10:24:58.450' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (24, 414, 0, 6875, 1, CAST(N'2019-09-28' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:13.700' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] ([HR_IDT], [HR_Supervisor_ID], [HR_Approver_ID], [HR_EmployeeCode], [HR_TAB_LogAttd_MarkTypeID], [HR_TAB_LogAttd_MarkedOn], [HR_Record_Remarks], [HR_Record_Posted_ByUserId], [HR_Record_Posted_TimeStamp], [HR_Record_Modified_ByUserId], [HR_Record_Modified_TimeStamp], [HR_Record_Deleted_ByUserId], [HR_Record_Deleted_TimeStamp], [HR_System_IP_Addr], [HR_RecordActiveId]) VALUES (31, 414, 0, 6875, 1, CAST(N'2019-09-29' AS Date), N'0', 414, CAST(N'2019-09-27 00:13:51.733' AS DateTime), NULL, NULL, NULL, NULL, N'000.000.000.00', 1)
GO
SET IDENTITY_INSERT [dbo].[HRM_TAB_USER_LOGIN_ATTDC_MST_TXN] OFF
GO

I tried, I can get output, but I can't column data as per the date order ref., my SP

 WITH CTE_R AS   (SELECT 
									 Right('000000' + CONVERT(NVARCHAR, A.HR_Emp_Code), 6) AS EMP_ID  
									,UPPER(B.HR_EmployeeName+' - '+C.HRM_AlphaCode) [Name_Desig]
									,(CASE WHEN [D].HR_TAB_LogAttd_MarkTypeID=1 THEN 'P' WHEN [D].HR_TAB_LogAttd_MarkTypeID=2 THEN 'A' WHEN [D].HR_TAB_LogAttd_MarkTypeID=3 THEN 'N' END)[Status] 

									,(CASE	WHEN [D].[HR_RECORD_REMARKS]=1 THEN 'U/S'
											WHEN [D].[HR_RECORD_REMARKS]=2 THEN 'BRN'
											WHEN [D].[HR_RECORD_REMARKS]=3 THEN 'ABS'
											WHEN [D].[HR_RECORD_REMARKS]=4 THEN 'NIF'
									        WHEN [D].[HR_RECORD_REMARKS]=5 THEN 'N/B'
											WHEN [D].[HR_RECORD_REMARKS]=6 THEN 'RES'
											ELSE '' END) [Rmks]
									 
									,HR_Record_Posted_TimeStamp [TxnDt]
									,HR_TAB_LogAttd_MarkedOn	[TXN_D]		                                                                                                                                    
									,ROW_NUMBER() Over(Partition by A.HR_Emp_Code,B.HR_EmployeeName,C.HRM_AlphaCode Order by (select null)) [Rn]
									FROM 
									
									HRM_EMPLOYEE_ALLOCATION_TXN			(NOLOCK)[A] INNER JOIN
									HRM_EMP_OFFER_GENERATION_TXN		(NOLOCK)[B] ON A.HR_Emp_Code			= B.HR_EmployeeCode AND B.HR_Record_ActiveId <>0  left join
									HRM_DESIGNATION_MASTER				(NOLOCK)[C] ON C.HRM_DesignationId		= B.HR_DesignationID LEFT JOIN 
						            HRM_TAB_USER_LOGIN_ATTDC_MST_TXN	(NOLOCK)[D] ON A.HR_Emp_Code			= D.HR_EmployeeCode AND D.HR_RecordActiveId=1 AND HR_TAB_LogAttd_MarkedOn BETWEEN DATEADD(day,-5, @i_Txn_Date) AND @i_Txn_Date    
									
									WHERE A.HR_Emp_Supervisor1_ID=@i_SUPRIVISOR_CODE AND HR_TAB_LogAttd_MarkedOn BETWEEN DATEADD(day,-5, @i_Txn_Date) AND @i_Txn_Date  --AND [B].HR_DesignationID IN (12,13,14,23,24,25,26,86,51)	
									)
									
									
									  SELECT 
									  EMP_ID																				 ,Name_Desig										[NameDes]
									 ,Max(Case when rn=1 then (CONVERT(CHAR(5), [TxnDt], 108)) ELSE NULL END) [Txn_D1]
									 ,Max(Case when rn=1 then [Status] else null  end)	[Stat_D1]
									 ,Max(Case when rn=1 then [Rmks] else null  end)	[Rmks_D1]
									 ,Max(Case when rn=2 then (CONVERT(CHAR(5), [TxnDt], 108)) ELSE NULL END) [Txn_D2]
									 ,Max(Case when rn=2 then [Status] else null  end)	[Stat_D2] 
									 ,Max(Case when rn=2 then [Rmks] else null  end)	[Rmks_D2]
									 ,Max(Case when rn=3 then (CONVERT(CHAR(5), [TxnDt], 108)) ELSE NULL END) [Txn_D3]
									 ,Max(Case when rn=3 then [Status] else null  end)	[Stat_D3]
									 ,Max(Case when rn=3 then [Rmks] else null  end)	[Rmks_D3]
									 ,Max(Case when rn=4 then (CONVERT(CHAR(5), [TxnDt], 108)) ELSE NULL END) [Txn_D4]
									 ,Max(Case when rn=4 then [Status] else null  end)	[Stat_D4] 
									 ,Max(Case when rn=4 then [Rmks] else null  end)	[Rmks_D4] 
									 ,Max(Case when rn=5 then (CONVERT(CHAR(5), [TxnDt], 108)) ELSE NULL END) [Txn_D5]
									 ,Max(Case when rn=5 then [Status] else null  end)	[Stat_D5] 
									 ,Max(Case when rn=5 then [Rmks] else null  end)	[Rmks_D5] 
									 
									FROM CTE_R WHERE TXN_D=@i_Txn_Date
									GROUP BY EMP_ID,Name_Desig,TXN_D  		
I
 want to show the report perfectly without any mistake, please correct where I did mistake.
Thanks in advance. 
CNN Aypn.

Friday, September 27, 2019 7:14 AM

All replies

0

Sign in to vote
User-719153870 posted

Hi CNN Aypn,

ayyappan.CNN

FROM HRM_EMPLOYEE_ALLOCATION_TXN (NOLOCK)[A] INNER JOIN HRM_EMP_OFFER_GENERATION_TXN (NOLOCK)[B] ON A.HR_Emp_Code = B.HR_EmployeeCode AND B.HR_Record_ActiveId <>0 left join HRM_DESIGNATION_MASTER (NOLOCK)[C] ON C.HRM_DesignationId = B.HR_DesignationID LEFT JOIN HRM_TAB_USER_LOGIN_ATTDC_MST_TXN (NOLOCK)[D] ON A.HR_Emp_Code = D.HR_EmployeeCode AND D.HR_RecordActiveId=1 AND HR_TAB_LogAttd_MarkedOn BETWEEN DATEADD(day,-5, @i_Txn_Date) AND @i_Txn_Date

Seems your sp involves several tables and since you only provided the HRM_TAB_USER_LOGIN_ATTDC_MST_TXN one that we can't reproduce any problem or even any output.

And the sp itself is also not complete i think.

ayyappan.CNN
but I can't column data as per the date order ref.

I suggest that you could share more table structure and data, a screenshot of your output will be even greater. Most of all, this "column data as per the date order ref" is also confusing, maybe the output will help us understand but a more detailed description will be more helpful.

Best Regard,

Yang Shen
Monday, October 14, 2019 9:09 AM

Asked by:

Rows to Cols based on date split (MS SQL)

Question

All replies

ayyappan.CNN

ayyappan.CNN