SQLTeam.com | Weblogs | Forums

If date fall with in the date need to delete and insert new date

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)

Did you try to use Merge?

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

Hi Mike,

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;

select @designationid as designationid,@startdate as startdate,@enddate as enddate,@regularhrs as regularhts,GETDATE() as _todat ,@statusflag as sflag

And use these aliases further down and not @.

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;

when i run the above script am getting the below error.

Msg 207, Level 16, State 1, Line 13
Invalid column name 'designationid'.

change to ?
target.EmpID= source.designationid

I think you should try to keep naming convention easy to follow. 1 to 1.

Thank you Mike and yosiasz , works well !!

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.

designationid startdate enddate regularhours statusflag
1 4/1/2020 4/9/2020 3 1
1 4/2/2020 4/10/2020 6 1

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.

@mike01,

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?

Thanks in advance,

anyone there need help for the above !!

hi

you need to change your SQL ( please check if this works )

select @reccount=COUNT(*) from @SetupWorkingHrs
where @StartDate>=startdate or @EndDate<=enddate and Empid=1

Thanks @harishgg1 for the approach.

i have checked your query it doesnt work.

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;

why not

change
and Target.StartDate <= source.startdate
and target.EndDate >= source.enddate

to

and Target.StartDate >= source.startdate
and target.EndDate <= source.enddate

slight typo in the "To" part

and (Target.StartDate >= source.startdate
or target.EndDate <= source.enddate)

thanks Mike :+1:

please find my input below i want query to perform as per the given input.

designationID startdate enddate RegularHrs perform
2 2/4/2020 10/4/2020 5 insert
2 1/4/2020 9/4/2020 6 update
2 3/4/2020 11/4/2020 5 update
2 10/4/2020 20/4/2020 5 update
2 21/4/2020 23/4/2020 8 insert
2 22/4/2020 25/4/2020 8 update
2 25/4/2020 30/4/2020 6 update

hi vajeed

can you please explain .. completely .. not able to make sense !!

or else i can remote desktop
we can chat LIVE

and finish it off VERY QUICKLY !!!

Lot of time waste back and forth going on..