I have a table Absent
Id sid Startdate enddate Reasonid classid Acyearid schoolid
1 1 5/1/2013 8/1/2013 1 1 1 1
2 1 10/1/2013 12/1/2013 2 1 1 1
3 2 12/12/2012 20/12/2012 1 1 1 1
4 2 5/1/2013 7/1/2013 3 1 1 1
Case 1
When user insert a row
Id sid Startdate enddate Reasonid classid Acyearid schoolid
1 1 3/1/2013 6/1/2013 2 1 1 1
The data in table split into two as shown below id-5,6
Id sid Startdate enddate Reasonid classid Acyearid schoolid
2 1 10/1/2013 12/1/2013 2 1 1 1
3 2 12/12/2012 20/12/2012 1 1 1 1
4 2 5/1/2013 7/1/2013 3 1 1 1
5 1 3/1/2013 6/1/2013 2 1 1 1
6 1 7/1/2013 8/1/2013 1 1 1 1
Case2
When user insert a row
Id sid Startdate enddate Reasonid classid Acyearid schoolid
1 1 5/1/2013 12/1/2013 2 1 1 1
Table entry becomes
Id sid Startdate enddate Reasonid classid Acyearid schoolid
3 2 12/12/2012 20/12/2012 1 1 1 1
4 2 5/1/2013 7/1/2013 3 1 1 1
5 1 5/1/2013 12/1/2013 2 1 1 1
Rows 1,2 deleted and id with 5 is inserted also check reason
Case 3:
When user insert data with values
Id sid Startdate enddate Reasonid classid Acyearid schoolid
1 1 6/1/2013 9/1/2013 2 1 1 1
Table data become
Id sid Startdate enddate Reasonid classid Acyearid schoolid
2 1 10/1/2013 12/1/2013 2 1 1 1
3 2 12/12/2012 20/12/2012 1 1 1 1
4 2 5/1/2013 7/1/2013 3 1 1 1
5 1 3/1/2013 5/1/2013 1 1 1 1
6 1 5/1/2013 9/1/2013 2 1 1 1
Ie based on the input data table my added with multiple row sometimes rows are added together. Iam try to solve thisby using two loops i able to solve only one condition
my code is shown below.
declare @StartDate smalldatetime='2013-02-12 00:00:00';
declare @EndDate smalldatetime='2013-02-15 00:00:00';
declare @Notes nvarchar(500);
declare @AbsentReasonCode int;
declare @_StartDateTable smalldatetime ;
declare @_EndDateTable smalldatetime ;
declare @_AbsentReasonCode int ;
declare @_update int=0;
declare CursorAttendance cursor for
select StartDate,EndDate,AbsentReasonCode from attendance
where StartDate<@EndDate and EndDate>@StartDate and StudentID=@StudentID
order by StartDate;
open CursorAttendance;
FETCH NEXT
FROM CursorAttendance INTO @_StartDateTable , @_EndDateTable,@_AbsentReasonCode
WHILE @@FETCH_STATUS = 0
BEGIN
set @_update=0;
while dbo.GetDatePart(@_StartDateTable)<dbo.getdatepart(@_enddatetable)>=dbo.GetDatePart(@StartDate)
and dbo.GetDatePart(@_StartDateTable)<= dbo.GetDatePart(@EndDate)
begin
set @_update=1;
end
begin
print('****--------splited date for student 42457,AttendanceID=100 -----*****');
set @_update=0;
print (cast( @StartDate as nvarchar(50))+','+cast(@EndDate as nvarchar(50)));
print (cast( @_StartDateTable as nvarchar(50))+','+cast(@_EndDateTable as nvarchar(50)));
print('****--------------*****');
end
else if @_update=2
begin
print('****------@_update=2-------*****');
end
end
FETCH NEXT
FROM CursorAttendance INTO @_StartDateTable , @_EndDateTable,@_AbsentReasonCode END
CLOSE CursorAttendance
DEALLOCATE CursorAttendance