Hi, i am using two insert into statment in single stored procedure, this stored procedure will execute automatically, For first insert into statment i am getting all rows but for second insert into statment i am receiving zero rows because of the condition where P_Date NOT IN (SELECT P_Date from Tbl_EmpPunchingDetail) . Can anyone guide me that i should get only one row for each date for each Pin_Code without repeatation when executing stored procdeure multiple times for second insert into Statement????
Create proc [dbo].[sp_UpdateEmpPunchingDetailTbl]
as
begin
--First insert statement
INSERT INTO Tbl_EmpPunchingDetail
select
Pin_Code as Pin_Code,
P_Date as P_Date,
IN1 as IN1,
OUT1 as OUT1
from
[Zultime].[dbo].[TIME_SHEET]
where P_Date NOT IN (SELECT P_Date from Tbl_EmpPunchingDetail)
AND P_Date <= dateadd(day,datediff(day,1,GETDATE()),0)
--Second insert statement
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
declare @P_Date table
(
[P_Date] date
)
insert into @P_Date
select distinct [P_Date]
FROM [Zultime].[dbo].[TIME_SHEET]
where [P_Date] BETWEEN CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)+8),@mydate),101)
AND CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-22),@mydate),101)
INSERT INTO Tbl_EmpPunchingDetail
select
[Pin_Code], P_Date, '07:30' as [IN1], '16:30' as [OUT1]
FROM [AttendanceCorrection].[dbo].[Tbl_FMOEmp]
cross join @P_Date
where
P_Date NOT IN (SELECT P_Date from Tbl_EmpPunchingDetail )
AND
P_Date <= dateadd(day,datediff(day,1,GETDATE()),0)
end