FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

/****** Object:  Table [dbo].[ralog]    Script Date: 13-10-2025 13:28:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ralog](
	[UserID] [varchar](50) NOT NULL,
	[PDate] [datetime] NOT NULL,
	[IOFlag] [varchar](10) NULL,
	[MacNo] [varchar](15) NULL,
	[UpdSts] [varchar](1) NULL,
	[UDate] [datetime] NULL,
	[isManual] [varchar](1) NULL
) ON [PRIMARY]
GO

GO
INSERT [dbo].[ralog] ([UserID], [PDate], [IOFlag], [MacNo], [UpdSts], [UDate], [isManual]) VALUES (N'1103', CAST(N'2025-08-02T02:03:30.000' AS DateTime), N'I', N'01', NULL, NULL, NULL)
GO
INSERT [dbo].[ralog] ([UserID], [PDate], [IOFlag], [MacNo], [UpdSts], [UDate], [isManual]) VALUES (N'1103', CAST(N'2025-08-02T02:03:26.000' AS DateTime), N'O', N'01', NULL, NULL, NULL)
GO
INSERT [dbo].[ralog] ([UserID], [PDate], [IOFlag], [MacNo], [UpdSts], [UDate], [isManual]) VALUES (N'1103', CAST(N'2025-08-03T02:04:26.000' AS DateTime), N'O', N'01', NULL, NULL, NULL)
GO

GO
/****** Object:  Table [dbo].[Pay_tra_shiftnote]    Script Date: 13-10-2025 13:32:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pay_tra_shiftnote](
	[Comp_code] [char](4) NOT NULL,
	[Emp_no] [char](5) NOT NULL,
	[Shift_dt] [date] NOT NULL,
	[Shift_code] [char](1) NOT NULL,
	[In_time] [datetime] NULL,
	[Out_time] [datetime] NULL,
	[OT_Hours] [time](7) NULL,
	[Remarks] [varchar](50) NULL,
	[PDate1] [datetime] NULL,
	[PDate2] [datetime] NULL,
	[PDate3] [datetime] NULL,
	[PDate4] [datetime] NULL,
	[PDate5] [datetime] NULL,
	[PDate6] [datetime] NULL,
	[PDate_count] [int] NULL,
 CONSTRAINT [PK_Pay_tra_shiftnote] PRIMARY KEY CLUSTERED 
(
	[Comp_code] ASC,
	[Emp_no] ASC,
	[Shift_dt] ASC,
	[Shift_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Pay_tra_shiftnote] ([Comp_code], [Emp_no], [Shift_dt], [Shift_code], [In_time], [Out_time], [OT_Hours], [Remarks], [PDate1], [PDate2], [PDate3], [PDate4], [PDate5], [PDate6], [PDate_count]) VALUES (N'YJAT', N'1103 ', CAST(N'2025-08-01' AS Date), N'A', NULL, NULL, NULL, N'', NULL, NULL, NULL, NULL, NULL, NULL, 1)
GO
INSERT [dbo].[Pay_tra_shiftnote] ([Comp_code], [Emp_no], [Shift_dt], [Shift_code], [In_time], [Out_time], [OT_Hours], [Remarks], [PDate1], [PDate2], [PDate3], [PDate4], [PDate5], [PDate6], [PDate_count]) VALUES (N'YJAT', N'1103 ', CAST(N'2025-08-02' AS Date), N'B', NULL, NULL, NULL, N'', CAST(N'2025-08-02T17:20:43.000' AS DateTime), NULL, NULL, NULL, NULL, NULL, 1)
GO
INSERT [dbo].[Pay_tra_shiftnote] ([Comp_code], [Emp_no], [Shift_dt], [Shift_code], [In_time], [Out_time], [OT_Hours], [Remarks], [PDate1], [PDate2], [PDate3], [PDate4], [PDate5], [PDate6], [PDate_count]) VALUES (N'YJAT', N'1103 ', CAST(N'2025-08-03' AS Date), N'W', NULL, NULL, NULL, N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[Pay_tra_shiftnote] ([Comp_code], [Emp_no], [Shift_dt], [Shift_code], [In_time], [Out_time], [OT_Hours], [Remarks], [PDate1], [PDate2], [PDate3], [PDate4], [PDate5], [PDate6], [PDate_count]) VALUES (N'YJAT', N'1103 ', CAST(N'2025-08-04' AS Date), N'A', NULL, NULL, NULL, N'', CAST(N'2025-08-04T07:33:05.000' AS DateTime), CAST(N'2025-08-04T17:29:28.000' AS DateTime), NULL, NULL, NULL, NULL, 2)

GO
/****** Object:  Table [dbo].[Pay_tra_swipedata]    Script Date: 13-10-2025 13:33:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pay_tra_swipedata](
	[Comp_code] [char](4) NOT NULL,
	[Emp_no] [char](5) NOT NULL,
	[Swipe_ID] [nvarchar](16) NOT NULL,
	[Swipe_date] [date] NOT NULL,
	[Swipe_time] [time](7) NOT NULL,
	[Shift_date] [date] NULL,
	[Shift_code] [char](1) NULL,
	[Swipe_machine] [varchar](2) NULL,
	[Swipe_code] [char](1) NOT NULL,
	[Remarks] [nvarchar](30) NULL,
	[User_entry] [char](1) NULL,
	[PDate] [datetime] NULL,
	[Shiftnote_updated] [char](1) NULL,
 CONSTRAINT [PK_Pay_tra_swipedata_1] PRIMARY KEY CLUSTERED 
(
	[Comp_code] ASC,
	[Emp_no] ASC,
	[Swipe_ID] ASC,
	[Swipe_date] ASC,
	[Swipe_time] ASC,
	[Swipe_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [YJATDB]
GO
/****** Object:  Table [dbo].[Pay_mas_employee]    Script Date: 13-10-2025 13:56:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pay_mas_employee](
	[Comp_code] [char](4) NOT NULL,
	[Emp_no] [nvarchar](5) NOT NULL,
	[Emp_name] [varchar](50) NULL,
	[Empcate_code] [char](3) NULL,
	[Dept_code] [varchar](5) NULL,
	[Desig_code] [nvarchar](7) NULL,
	[Grade_code] [nvarchar](3) NULL,
	[Sex] [char](1) NULL,
	[Marital_status] [char](1) NULL,
	[Dir_Indir] [char](1) NULL,
	[Paystr_code] [nvarchar](10) NULL,
	[Parent_empno] [nvarchar](5) NULL,
	[Birth_place] [varchar](25) NULL,
	[Spouce_name] [varchar](25) NULL,
	[FH_Name] [nvarchar](25) NULL,
	[FH_Relation] [char](1) NULL,
	[DOB] [date] NULL,
	[DOW] [date] NULL,
	[DOJ] [date] NULL,
	[DOC] [date] NULL,
	[DOL] [date] NULL,
	[DOL_Reason] [nvarchar](25) NULL,
	[PF_avail] [char](1) NULL,
	[PF_no] [varchar](20) NULL,
	[PF_DOJ] [date] NULL,
	[ESI_avail] [char](1) NULL,
	[ESI_no] [numeric](10, 0) NULL,
	[LTA_Eligible] [char](1) NULL,
	[Pay_mode] [char](1) NULL,
	[Bank_code] [nvarchar](5) NULL,
	[Bank_acno] [nvarchar](16) NULL,
	[IFSC_Code] [nchar](11) NULL,
	[Bank_Branch] [varchar](50) NULL,
	[Photo_path] [nvarchar](50) NULL,
	[Personal_Email] [nvarchar](50) NULL,
	[Corporate_Email] [nvarchar](50) NULL,
	[Phone_no] [nvarchar](10) NULL,
	[Landline] [nvarchar](15) NULL,
	[PAN] [nvarchar](12) NULL,
	[Swipe_ID] [nvarchar](50) NULL,
	[Punching_Exception] [char](1) NULL,
	[Old_EmpID] [nvarchar](12) NULL,
	[UDFField1] [varchar](50) NULL,
	[Payslip_communication] [char](1) NULL,
	[LoanAdvance_eligible] [char](1) NULL,
	[Bonus_Applicable] [char](1) NULL,
	[OT_Rate] [numeric](10, 2) NULL,
	[SubDept_code] [varchar](5) NULL,
	[Sub_Dept_code] [nvarchar](5) NULL,
	[FAX_No] [nvarchar](15) NULL,
 CONSTRAINT [PK_Pay_mas_employee] PRIMARY KEY CLUSTERED 
(
	[Comp_code] ASC,
	[Emp_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Pay_mas_employee] ADD  CONSTRAINT [DF_Pay_mas_employee_Punching_Exception]  DEFAULT ('N') FOR [Punching_Exception]
GO
GO
INSERT [dbo].[Pay_mas_employee] ([Comp_code], [Emp_no], [Emp_name], [Empcate_code], [Dept_code], [Desig_code], [Grade_code], [Sex], [Marital_status], [Dir_Indir], [Paystr_code], [Parent_empno], [Birth_place], [Spouce_name], [FH_Name], [FH_Relation], [DOB], [DOW], [DOJ], [DOC], [DOL], [DOL_Reason], [PF_avail], [PF_no], [PF_DOJ], [ESI_avail], [ESI_no], [LTA_Eligible], [Pay_mode], [Bank_code], [Bank_acno], [IFSC_Code], [Bank_Branch], [Photo_path], [Personal_Email], [Corporate_Email], [Phone_no], [Landline], [PAN], [Swipe_ID], [Punching_Exception], [Old_EmpID], [UDFField1], [Payslip_communication], [LoanAdvance_eligible], [Bonus_Applicable], [OT_Rate], [SubDept_code], [Sub_Dept_code], [FAX_No]) VALUES (N'YJAT', N'1103', N'SURESH B', N'Stf', N'12002', N'GM - PH', N'', N'M', N' ', N' ', N'Stf', N'', N'', N'', N'BALASUBRAMANIAN S', N'F', CAST(N'1977-06-02' AS Date), CAST(N'1900-01-01' AS Date), CAST(N'2011-03-30' AS Date), CAST(N'2011-09-30' AS Date), CAST(N'1900-01-01' AS Date), N'', N'Y', N'100102562431', CAST(N'1900-01-01' AS Date), N'N', CAST(0 AS Numeric(10, 0)), NULL, N'B', N'HDFC', N'7501000036708', N'           ', N'', N'~/upload/5aa2d2eb-b1b4-4896-8472-4c036fc1814c.png', N'', N'', N'', N'', N'APSPS3040M', N'1103', N'N', N'', N'', N' ', N' ', N'Y', CAST(0.00 AS Numeric(10, 2)), N'', N'', N'')

DECLARE @QRY NVARCHAR(4000)

Declare @old_user varchar(100)
Declare @pdate_index int=1
Declare @Emp_no varchar(100) 
Declare @PDATE datetime
declare @sqlstatement nvarchar(4000)

insert into Pay_tra_swipedata(Comp_code, Emp_no, Swipe_ID, Swipe_date, Swipe_time, Shift_date, Shift_code, Swipe_machine, Swipe_code,PDate)

select 'YJAT',isnull((b.Emp_no),''),(a.USERID),a.PDATE,cast(a.PDATE as time(7)) PTIME,null,null,a.MacNo,'' I_O,a.PDATE
from dbo.ralog a left join Pay_mas_employee b on b.Comp_code='YJAT' and  a.USERID COLLATE DATABASE_DEFAULT = b.Swipe_id where a.PDATE>='2025-08-01 00:00:00'  and a.MacNo='01' and a.pdate > (select isnull(max(pdate),'1900-01-01') from Pay_tra_swipedata where Swipe_machine='01')
and b.Emp_no is not null order by a.PDATE desc

insert into Pay_tra_swipedata(Comp_code, Emp_no, Swipe_ID, Swipe_date, Swipe_time, Shift_date, Shift_code, Swipe_machine, Swipe_code,PDate)

select 'YJAT',isnull((b.Emp_no),''),(a.USERID),a.PDATE,cast(a.PDATE as time(7)) PTIME,null,null,a.MacNo,'' I_O,a.PDATE
from dbo.ralog a left join Pay_mas_employee b on b.Comp_code='YJAT' and  a.USERID COLLATE DATABASE_DEFAULT = b.Swipe_id where a.PDATE>='2025-08-01 00:00:00'  and a.MacNo='02' and a.pdate > (select isnull(max(pdate),'1900-01-01') from Pay_tra_swipedata where Swipe_machine='02')
and b.Emp_no is not null order by a.PDATE desc

insert into Pay_tra_swipedata(Comp_code, Emp_no, Swipe_ID, Swipe_date, Swipe_time, Shift_date, Shift_code, Swipe_machine, Swipe_code,PDate)

select 'YJAT',isnull((b.Emp_no),''),(a.USERID),a.PDATE,cast(a.PDATE as time(7)) PTIME,null,null,a.MacNo,'' I_O,a.PDATE
from dbo.ralog a left join Pay_mas_employee b on b.Comp_code='YJAT' and  a.USERID COLLATE DATABASE_DEFAULT = b.Swipe_id where a.PDATE>='2025-08-01 00:00:00'  and a.MacNo='03' and a.pdate > (select isnull(max(pdate),'1900-01-01') from Pay_tra_swipedata where Swipe_machine='03')
and b.Emp_no is not null order by a.PDATE desc

set @sqlstatement = 'Declare  users_cursor CURSOR FOR SELECT Emp_no ,PDATE FROM Pay_tra_swipedata where isnull(Shift_date,'''') ='''' order by Emp_no,PDate'

exec sp_executesql @sqlstatement

OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @Emp_no,@PDATE

WHILE @@FETCH_STATUS = 0
BEGIN

if @old_user=@Emp_no
begin
set @pdate_index =@pdate_index %6+1
set @old_user=@Emp_no
end
else
begin
set @pdate_index =1
set @old_user=@Emp_no

end

declare @sn_shiftcode nvarchar(1)='',@sn_shift_date nvarchar(10)=''
--print @sn_shiftcode +':'+@sn_shift_date
declare @Ptime nvarchar(5)=cast(cast(@PDATE as time(7)) as varchar(5));
declare @SDate nvarchar(10)
declare @SCode nvarchar(10)

if (@Ptime>'05:00' and @Ptime<'13:30')
begin
set @SDate=converT(Varchar(10),@PDATE,120)
set @SCode='A'
end
else if (@Ptime>'13:30' and @Ptime<'23:00')
begin
set @SDate=converT(Varchar(10),@PDATE,120)
set @SCode='B'
end
else if (@Ptime>'23:00' and @Ptime<'23:59')
begin
set @SDate=converT(Varchar(10),dateadd(dd,0,@PDATE),120)
set @SCode='C'
end
else if (@Ptime>'00:00' and @Ptime<'05:00')
begin
set @SDate=converT(Varchar(10),dateadd(dd,-1,@PDATE),120)
set @SCode='C'
end
if @PDATE='2025-08-05 02:10:54.000'
begin
print '2:'+@SCode+':'+@SDate;
end
--print @SDate
select @sn_shiftcode =Shift_code,@sn_shift_date =Shift_Dt from Pay_tra_shiftnote where Emp_no =@Emp_no and Shift_Dt=@SDate and Shift_code=@SCode
--print @sn_shiftcode +':'+@sn_shift_date +':emptyornot'
   if(@sn_shiftcode ='')
begin
--print @sn_shiftcode +':'+@sn_shift_date +':emptyornot2'
if @SCode='C'
begin
--set @SCode='A'
print 'aaaa'
set @SCode='B'
set @SDate=converT(Varchar(10),@SDate,120)
end
else if @SCode='B'
begin
--set @SCode='C'
set @SCode='A'
set @SDate=converT(Varchar(10),dateadd(dd,0,@SDate),120)
end
else if @SCode='A'
begin
--set @SCode='B'
set @SCode='C'
set @SDate=converT(Varchar(10),dateadd(dd,-1,@SDate),120)
--if (@Ptime>'00:00' and @Ptime<'08:30')
--begin
-- set @SCode='C'
-- set @SDate=converT(Varchar(10),dateadd(dd,-1,@PDATE),120)
--end
end

if @PDATE='2025-08-05 02:10:54.000'
begin
print '3:'+@SCode+':'+@SDate;
end
end

select @sn_shiftcode =Shift_code,@sn_shift_date =Shift_Dt from Pay_tra_shiftnote where Emp_no =@Emp_no  and Shift_Dt=@SDate and Shift_code=@SCode

if(@sn_shiftcode ='')
begin
--print @sn_shiftcode +':'+@sn_shift_date +':emptyornot3'
if @SCode='B'
begin
--set @SCode='A'
--set @SDate=converT(Varchar(10),dateadd(dd,-1,@PDATE),120)
set @SCode='A'
set @SDate=converT(Varchar(10),dateadd(dd,0,@SDate),120)
end
else if @SCode='A'
begin
set @SCode='C'
set @SDate=converT(Varchar(10),dateadd(dd,-1,@SDate),120)
end
else if @SCode='C'
begin
set @SCode='B'
set @SDate=converT(Varchar(10),dateadd(dd,0,@SDate),120)
end
end

Declare @tmp_pdate123456 datetime=null

select @tmp_pdate123456=PDATE6 from Pay_tra_shiftnote where Emp_no =@Emp_no  and Shift_Dt=@SDate-- and Shift_code=@SCode
if (@tmp_pdate123456 is null)
set @pdate_index=6

select @tmp_pdate123456=PDATE5 from Pay_tra_shiftnote where Emp_no =@Emp_no and Shift_Dt=@SDate-- and Shift_code=@SCode
if (@tmp_pdate123456 is null)
set @pdate_index=5

select @tmp_pdate123456=PDATE4 from Pay_tra_shiftnote where Emp_no =@Emp_no  and Shift_Dt=@SDate-- and Shift_code=@SCode
if (@tmp_pdate123456 is null)
set @pdate_index=4

select @tmp_pdate123456=PDATE3 from Pay_tra_shiftnote where Emp_no =@Emp_no  and Shift_Dt=@SDate-- and Shift_code=@SCode
if (@tmp_pdate123456 is null)
set @pdate_index=3

select @tmp_pdate123456=PDATE2 from Pay_tra_shiftnote where Emp_no =@Emp_no  and Shift_Dt=@SDate-- and Shift_code=@SCode
if (@tmp_pdate123456 is null)
set @pdate_index=2

select @tmp_pdate123456=PDATE1 from Pay_tra_shiftnote where Emp_no =@Emp_no  and Shift_Dt=@SDate-- and Shift_code=@SCode
if (@tmp_pdate123456 is null)
set @pdate_index=1

Print 'C'

set @QRY='update Pay_tra_shiftnote set PDate_count='''+cast(@pdate_index as varchar(10))+''',PDate'+cast(@pdate_index as varchar(10))+'='''+convert(varchar(19),@PDATE,120)+''' where Comp_Code=''YJAT'' and Emp_no  = '''+@Emp_no+''' and Shift_dt='''+@SDate+''' --and Shift_code=case when '''+cast(cast(@PDATE as time(7)) as varchar(5))+'''>''07:30'' and '''+cast(cast(@PDATE as time(7)) as varchar(5))+'''<''17:30'' then ''A'' when ('''+cast(cast(@PDATE as time(7)) as varchar(5))+'''>''16:30'' and '''+cast(cast(@PDATE as time(7)) as varchar(5))+'''<''23:59'') or ('''+cast(cast(@PDATE as time(7)) as varchar(5))+'''>''00:00'' and '''+cast(cast(@PDATE as time(7)) as varchar(5))+'''<''02:30'') then ''B'' when ('''+cast(cast(@PDATE as time(7)) as varchar(5))+'''>''23:30'' and '''+cast(cast(@PDATE as time(7)) as varchar(5))+'''<''23:59'') or ('''+cast(cast(@PDATE as time(7)) as varchar(5))+'''>''00:00'' and '''+cast(cast(@PDATE as time(7)) as varchar(5))+'''<''08:30'') then ''C'' end '
--print @QRY
exec sp_executesql @QRY
Print 'B'

set @QRY='update Pay_tra_swipedata set Shift_date='''+@SDate+''',Shift_Code='''+@SCode+''' where PDate='''+convert(varchar(19),@PDATE,120)+''' and isnull(Shift_Date,'''') ='''''
exec sp_executesql @QRY

FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @Emp_no,@PDATE
END
CLOSE users_cursor
DEALLOCATE users_cursor

set @pdate_index =1
set @old_user=''
Print 'A'
set @sqlstatement = 'Update Pay_tra_shiftnote set In_Time=Pdate1, Out_time= Case when PDate_Count=2 then Pdate2 when PDate_Count=3 then Pdate3 when PDate_Count=4 then Pdate4 when PDate_Count=5 then Pdate5 when PDate_Count=6 then Pdate6 end where In_Time is Null and Out_time is Null '

exec sp_executesql @sqlstatement

select * from Pay_tra_shiftnote

Comp_code	Emp_no	Shift_dt	Shift_code	In_time	Out_time	OT_Hours	PDate1	PDate2	PDate3	PDate4	PDate5	PDate6	PDate_count
YJAT	1103	2025-08-01	A	2025-08-02 02:03:26.000	2025-08-02 02:03:30.000	NULL	2025-08-02 02:03:26.000	2025-08-02 02:03:30.000	NULL	NULL	NULL	NULL	2
YJAT	1103	2025-08-02	B	2025-08-02 17:20:43.000	2025-08-03 02:04:26.000	NULL	2025-08-02 17:20:43.000	2025-08-03 02:04:26.000	NULL	NULL	NULL	NULL	2
YJAT	1103	2025-08-03	W	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
YJAT	1103	2025-08-04	A	2025-08-04 07:33:05.000	2025-08-04 17:29:28.000	NULL	2025-08-04 07:33:05.000	2025-08-04 17:29:28.000	NULL	NULL	NULL	NULL	2

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
Msg 911, Level 16, State 1, Server 5880f9e7a40d, Line 5
Database 'YJATDB' does not exist. Make sure that the name is entered correctly.

(1 rows affected)

(3 rows affected)

(0 rows affected)

(0 rows affected)
aaaa
C

(1 rows affected)
B

(1 rows affected)
aaaa
C

(1 rows affected)
B

(1 rows affected)
aaaa
C

(1 rows affected)
B

(1 rows affected)
A

(4 rows affected)

(4 rows affected)