@harishgg1
Am checking the dates(startdate and enddate) if it is fallen with in the range then it should update if it is fallen outside the range then insert new records.
Assume that have in table already startdate = 2020-04-02 and enddate = 2020-04-10
If I want to insert new record by passing @startdate = 2020-04-01 and @enddate = 2020-04-09 it should check the existing dates where @enddate(2020-04-09) is falling within the existing enddate ( 2020-04-10 ) so it should update.
Similarly as go on as per the input in previous post.
hi
i came up with sample data and results ..
hope this helps 
drop table #SampleData
go
create table #SampleData
(
startDate date ,
endDate date,
updating int
)
go
-----------------------------------------------------------
declare @startdate date = '2020-04-01'
declare @enddate date = '2020-04-09'
declare @update int =0
insert into #SampleData select '2020-04-02','2020-04-10',111
select 'sample data',* from #SampleData
-----------------------------------------------------------
select @update = 1
from #SampleData
where @startdate >= startdate or @enddate <= enddate
if @update =1 select 'Updating' else select 'Inserting'
if @update = 0
insert into #SampleData select @startdate , @enddate ,111
else if @update =1
update #SampleData set updating = 5555
where @startdate >= startdate or @enddate <= enddate
select 'after insert or update',* from #SampleData
go
----------------------------------------------------------
@harishgg1,
startdate = 2020-04-02 and enddate = 2020-04-10 already exists i made it startdate = 2020-04-01 and enddate = 2020-04-09 it worked.
but when i want to insert the new record startdate = 2020-04-11 and enddate = 2020-04-20 it should insert but it is updating to existing record.
please explain properly what you are trying to do !!
i am not able to understand what you are saying !!!
this is a very very very very very simple thing to resolve !!!
please refer below is my input, your query works for only half of the scenario
if i pass input startdate = 2020-04-21 and enddate = 2020-04-23 instead of inserting new record it is updating to existing records. please let me know if still my explanation doesn't make understand you ?
| 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
how about this !! hope this helps !! ..
DROP TABLE #sample;
GO
CREATE TABLE #sample
(
startdate DATE
, enddate DATE
);
GO
SET DATEFORMAT DMY;
INSERT INTO #sample SELECT '2/4/2020', '10/4/2020';
INSERT INTO #sample SELECT '1/4/2020', '9/4/2020';
INSERT INTO #sample SELECT '3/4/2020', '11/4/2020';
INSERT INTO #sample SELECT '10/4/2020', '20/4/2020';
INSERT INTO #sample SELECT '21/4/2020', '23/4/2020';
INSERT INTO #sample SELECT '22/4/2020', '25/4/2020';
INSERT INTO #sample SELECT '25/4/2020', '30/4/2020';
GO
DECLARE @start_date DATE = '2020-04-21';
DECLARE @end_date DATE = '2020-04-23';
SELECT
*
, CASE WHEN @start_date BETWEEN startdate
AND enddate
OR @end_date BETWEEN startdate
AND enddate THEN 'Update'
ELSE 'Insert'
END AS PERFORM
FROM
#sample;
GO
hi vajeed
as we discussed on cell phone .. please see merge statement with changes
Please let me know if this works
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 BETWEEN= source.startdate AND source.EndDate
OR Target.EndDate BETWEEN= source.startdate AND 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;
@harishgg1,
Thank you for talking with me
solution provided by you is working for scenarios am still testing it on if it again stuck in any of the input will let you know.
