Click here to Skip to main content
12,632,072 members (24,659 online)
Rate this:
 
Please Sign up or sign in to vote.
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
Comments
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100