12,632,072 members (24,659 online)
Rate this:
See more:
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;
--12 - 19 => 12 - 15,16 - 19
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;
--case 1
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
--else if dbo.GetDatePart(@_StartDateTable)<dbo.getdatepart(@startdate)>dbo.GetDatePart(@EndDate)
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('****--------------*****');
--print(@_StartDateTable)print(@_EndDateTable);
--insert into dbo.Attendance values( @StartDate,@EndDate);
--insert into dbo.Attendance values( @_StartDateTable,@_EndDateTable,@_AbsentReasonCode);
--delete from Attendance where AttendanceID=@AttendanceID;
--
end
else if @_update=2
begin
print('****------@_update=2-------*****');
end

end
-------------------------------------------

FETCH NEXT
FROM CursorAttendance INTO @_StartDateTable , @_EndDateTable,@_AbsentReasonCode 	END
CLOSE CursorAttendance
DEALLOCATE CursorAttendance
-------------------------------------------------------```
Posted 27-Jan-13 19:42pm
Updated 27-Jan-13 19:48pm
v4
Zoltán Zörgő 28-Jan-13 0:49am

Could you give us the logic of it in English, not only some cases? And please update your original post with "improve question" widget, and try to use proper formatting.
Tharaka MTR 28-Jan-13 1:01am

Yes, provide the logic please
rajin kp 28-Jan-13 1:27am

@Zoltán Zörgő , @Tharaka MTR My attendance table inserted by using information from excel. Consider that one student take leave from 5-1-13 to 7-1-13 with reason 1 and we make and entry in excel for this. but end of that year student may submit some medical certificate for his leave then teacher enter data in to another excel with date 5-1-13 to 6-1-13 with reason 3 and 7-1-13 to 7-1-13 with reason1 .teachers never search for previous data and try to modify it..s/w must be able to find the appropriate value and delete it and insert while uploading the data using these excel sheet.
We give priority for last data.ie based on the last data table must be inserted or updated. if the above reason come in reverse table must be inserted with values from 5-1-13 to 7-1-13 with reason 1.
Also another sheet may contain value 6-1-13 to 9-1-13 reason 2 for same student. then table updated with last values from 5-1-13 to 5-1-13 with reason 1 and 6-1-13 to9-1-13 with reason 2.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 255 Richard MacCutchan 118 Peter Leow 85 Admire Mhlaba 75 F. Xaver 65
 OriginalGriff 1,999 ppolymorphe 1,242 Peter Leow 804 John Simmons / outlaw programmer 665 CPallini 571

Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 28 Jan 2013