Hi all,i have '2020-04-01' and '2020-04-10' as startdate and enddate in table @SetupWorkingHrs already
if again i want to insert the same date or with in date's(like '2020-04-02' to '2020-04-09')
then it should delete the row and insert new row. how can i do that ?
declare @SetupWorkingHrs table
(
workinghrsid int identity(1,1),
Empid int,
Startdate date,
enddate date,
regularHrs int
)
insert into @SetupWorkingHrs(Empid,Startdate,enddate,regularHrs)
values(1,'2020-04-01','2020-04-10',8)
declare @startdate date = '2020-04-02'
declare @enddate date= '2020-04-04'
declare @reccount int
select @reccount=COUNT(*) from @SetupWorkingHrs
where StartDate>=@startdate and EndDate<=@enddate and Empid=1
select @reccount as '@reccount'
if @reccount > 1
begin
delete from @SetupWorkingHrs
where StartDate>=@startdate and EndDate<=@enddate and Empid=1
end
insert into @SetupWorkingHrs(Empid,Startdate,enddate,regularHrs)
values(1,'2020-04-02','2020-04-09',7)
declare @SetupWorkingHrs table
(
workinghrsid int identity(1,1),
Empid int,
Startdate date,
enddate date,
regularHrs int
)
insert into @SetupWorkingHrs(Empid,Startdate,enddate,regularHrs)
values(1,'2020-04-01','2020-04-10',8)
Merge into @SetupWorkingHrs as target
using (select 1 as EmpID, '2020-04-02' as StartDate,'2020-04-09' as EndDate, 7 as regularHrs) as source
on target.EmpID = source.EmpID
and Target.StartDate <= source.StartDate
and target.EndDate >= source.EndDate
when not matched then
insert (EmpID, StartDate, EndDate, regularHrs)
values (source.EmpID, source.StartDate, source.EndDate, source.regularHrs)
when matched then
update set StartDate = source.StartDate,
EndDate = source.EndDate,
regularHrs = source.regularHrs;
Select top 100 * from @SetupWorkingHrs
Thanks for the reply. what if the data is coming from the parameters we have check and insert into the real table which has same design as @SetupWorkingHrs shown in my previous post?
means i am planning to write sp where i used to declare parameters through which i pass date @designation,@startdate,@enddate,@regularhrs,@status
at that point of time how i can modify the above query?
i tried the below may be this is not the way to write it
declare @startdate date = '2020-04-02'
declare @designationid int = 1
declare @enddate date = '2020-04-04'
declare @regularhrs int = 6
declare @status int = 1
Merge into tblWorkHours as target
using (select @designationid,@startdate,@enddate,@regularhrs,GETDATE(),@statusflag ) as source
on target.FK_EmpDesignation = source.@designationid
and Target.StartDate <= source.@startdate
and target.EndDate >= source.@enddate
when not matched then
insert (EmpID, StartDate, EndDate, regularHrs)
values (source.@designationid, source.@startdate, source.@startdate, source.@startdate)
when matched then
update set StartDate = source.@startdate,
EndDate = source.@startdate,
RegularHours = source.@startdate;
Your code is wrong. You need to alias the columns and then do merge. Also, please check your code as you are putting startdate in all the columns
declare @startdate date = '2020-04-02'
declare @designationid int = 1
declare @enddate date = '2020-04-04'
declare @regularhrs int = 6
declare @status int = 1
Merge into tblWorkHours as target
using (select @designationid as designationid,
@startdate as StartDate,
@enddate as EndDate,
@regularhrs as regularhrs,
@statusflag as StatusFlag) as source
on target.designationid = source.designationid
and Target.StartDate <= source.startdate
and target.EndDate >= source.enddate
when not matched then
insert (EmpID, StartDate, EndDate, regularHrs)
values (source.designationid, source.startdate, source.enddate, source.regularHrs)
when matched then
update set StartDate = source.startdate,
EndDate = source.enddate,
RegularHours = source.regularHrs;
Hi ,
i have tested for all scenario it worked for me but i stuck with this below input.
declare @startdate date = '2020-04-02'
declare @designationid int = 1
declare @enddate date = '2020-04-10'
declare @regularhrs int = 6
declare @status int = 1
below is the records i have already in the DB since 2nd record date is fall under the 1st record i was expecting to update 1st record instead of inserting new row.
That is not how you stated your issue. The second one falls OUTSIDE of the date ranges because 4/10 is not between 4/1 and 4/9. Do you need to check if only 1 date is between the existing dates? Can you provide more information because the code provided is doing what you asked for. You need to clarify what you are asking for.
1.The second one falls OUTSIDE of the date ranges because 4/10 is not between 4/1 and 4/9?
-->yes , you are right but the first one falls inside the range(4/2). Any one of the date that is @startdate or @enddate if it is falling within the existing date then it should update/overwrite it.
2.Do you need to check if only 1 date is between the existing dates?
-->no, i have to check both date's(@startdate and @enddate) if any of the date is falling under the existing date's then it should update else it should insert new records.
Hope it may help you , please let me know if the above statements is not clear?
i believe the problem with below highlighted condition
Merge into tblWorkHours as target
using (select @designationid as designationid, @startdate as StartDate, @enddate as EndDate, @regularhrs as regularhrs, @statusflag as StatusFlag) as source
on target.designationid = source.designationid and Target.StartDate <= source.startdate and target.EndDate >= source.enddate
when not matched then
insert (EmpID, StartDate, EndDate, regularHrs)
values (source.designationid, source.startdate, source.enddate, source.regularHrs)
when matched then
update set StartDate = source.startdate,
EndDate = source.enddate,
RegularHours = source.regularHrs;