October 8, 2014 at 1:07 am
Hi,
I am having below schema:
CREATE TABLE #Attendance(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StudentID] [int] NOT NULL,
[ClassID] [int] NOT NULL,
[DateAdded] [datetime] default getdate() NOT NULL
) ON [PRIMARY]
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:45:02.900')
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:50:02.900')
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:55:02.900')
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,2,'2014-10-07 11:38:02.900')
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,2,'2014-10-07 11:45:02.900')
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,2,'2014-10-07 11:50:02.900')
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,2,'2014-10-07 11:55:02.900')
select * from #Attendance
CREATE TABLE #ClassAttendance(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClassID] [int] NOT NULL,
StartTime datetime,
ClassMinutes int
) ON [PRIMARY]
insert into #ClassAttendance (ClassID,StartTime,ClassMinutes) values(1,'2014-10-07 10:48:02.900',180)
insert into #ClassAttendance (ClassID,StartTime,ClassMinutes) values(1,'2014-10-07 11:38:02.900',270)
--drop table #Attendance
DateAdded column in first table is nothing but in and out time.
Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.
Validations i need to consider are:
If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance
Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.
and by using this thing i want to calculate total number of minutes student present in the class and number of minutes absent.
If there is only one DateAdded for class then consider as a absent student.
Thanks,
Abhas.
October 8, 2014 at 2:41 am
Looks like course work to me...
So, would you post whatever you alreday tried so far, and we will help you to learn other than give you straight answer which will not be too helpfull...
October 8, 2014 at 2:44 am
Thanks Eugene Elutin
I tried by finding min and max dateadded group by class but stucked with validation part.
If you have any idea then please help.
Thanks,
Abhas.
October 8, 2014 at 2:48 am
for clarification...based on your sample data provided....can you please post your expected results.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 8, 2014 at 7:01 am
Hi Livingston,
Just simplified data as below:
create table #Time (StudentID int,ClassID int,StartDate datetime,ActualTIme time,StartTime time,EndTime time)
insert into #Time values(1,1,'2014-10-07 12:26:30.540','12:26:30','12:50:00','17:10:00')
insert into #Time values(1,1,'2014-10-07 01:35:30.540','01:35:30','12:50:00','17:10:00')
insert into #Time values(1,1,'2014-10-07 01:40:30.540','01:40:30','12:50:00','17:10:00')
insert into #Time values(1,1,'2014-10-07 01:35:30.540','01:35:30','12:50:00','17:10:00')
insert into #Time values(1,1,'2014-10-07 01:35:30.540','01:35:30','12:50:00','17:10:00')
insert into #Time values(1,2,'2014-10-07 12:26:30.540','18:26:30','18:50:00','20:10:00')
insert into #Time values(1,2,'2014-10-07 01:35:30.540','19:35:30','18:50:00','20:10:00')
insert into #Time values(1,2,'2014-10-07 01:40:30.540','19:40:30','18:50:00','20:10:00')
insert into #Time values(1,2,'2014-10-07 01:35:30.540','19:35:30','18:50:00','20:10:00')
insert into #Time values(1,2,'2014-10-07 01:35:30.540','20:35:30','18:50:00','20:10:00')
select * from #Time
drop table #Time
Now I want to display data for ClassID where ActualTime falls between StartTime and EndTime. And I want a single row. STartTime and ENdTime will always fix for particular class. I want to FInd Minimum Actual time and Maximun Actual time which falls between StartTime and EndTime and calculate total minutes.
I tried a lot but failing if i use between startTime and EndTime.
Please help.
Thanks,
ABhas.
October 8, 2014 at 9:12 am
I have resolved the issue. I was debugging wrong.. I was comparing 1:30 instead of 13:30.
Thanks,
Abhas.
October 8, 2014 at 9:23 am
glad to hear you have solved the problem,,,care to share so that others may learn?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 8, 2014 at 9:24 am
select StudentID
,ClassId
,StartTime
,EndTime
,MIN(ActualTime) AS MIN_ActualTime
,MAX(ActualTime) AS MAX_ActualTime
,DATEDIFF(MINUTE, MIN(ActualTime), MAX(ActualTime)) AS TotalDiffInMin
from #Time
where ActualTime BETWEEN StartTime and EndTime
group by StudentID, ClassId, StartTime, EndTime
Note: your no records for ClassID = 1 have ActualTime between Start and End time, shouldn't be 13:... instead of 01:...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply