query help

Question

hi friends i am new to sql i have a table with fields

Table=ATTENDANCE

Attenidstudentid teacherid repleceteacherid currentdatclassid

11 1 0 6/7/2012 1

22 0 2 7/7/2012 1

31 1 0 8/7/2012 1

now i want to calculate teacher attendance between 2 dates each teacher is assigned to one class when teacher takes his class then his teacherid is get inserted to teacherid col(with replacement=0)

and when other techer takes the class then his teacherid get inserted in replacementteacher col(with teacherid=0)

i want the output in the following form

totallectures presentlectures absentlectures

10 7 3

how to write query if possible send me the query

please help

[:(]

Santhosh Kumar Jayaraman · Answer

select TeacherIdcount(distinct(classid)) as totallectures (Select COUNT(distinct(classid)) from attendance2 where replacedteacherid = 0 and teacherId = a.teacherId and currentdat = a.currentdat ) as Present (Select COUNT(distinct(classid)) from attendance2 where replacedteacherid = a.teacherId and teacherId = a.teacherId and currentdat = a.currentdat ) as Absent currentdat from attendance2 a WHERE TeacherId = 1 AND currentdat BETWEEN '6/1/2012' AND '6/8/2012' group by teacherIdcurrentdat

sameer gadade · Answer

hi santosh you helped me alot ur query worked Exactly as i want thank you very very very much!!!!!

Rahul Bhatt · Answer

Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:Table Normal;mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:;mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin-top:0cm;mso-para-margin-right:0cm;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0cm;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:Calibrisans-serif;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Times New Roman;mso-bidi-theme-font:minor-bidi;mso-fareast-language:EN-US;} To achieve this task Using Case clasue you can easy get your desire output Case when a.Replacingteacherid = a.teacherId then 1 else 0 END If teacherid and replaceteacherid both are same it means teacher is present If teacherid and replaceteacherid both are not equal it means teacher absent Full Query Declare @FromDate as DateTime = '06/01/2012' Declare @ToDate as DateTime = '06/09/2012' Declare @TeacherID as INT = 1 select a . teacherId as [Teacher ID]  SUM ( Case when a . Replacingteacherid = a . teacherId then 1 else 0 END ) as Present  SUM ( Case when a . Replacingteacherid != a . teacherId then 1 else 0 END ) as [Absent]  COUNT ( a . Attenid ) as [Total Lectures]  convert ( varchar ( 10 ) a . currentdat  110 ) as Cur_date from attendance as a where a . teacherId = @TeacherID and CONVERT ( varchar ( 10 ) a . currentdat  102 ) between CONVERT ( varchar ( 10 ) @FromDate  102 ) and CONVERT ( varchar ( 10 ) @ToDate  102 ) Group by a . teacherId  convert ( varchar ( 10 ) a . currentdat  110 )

sameer gadade · Answer

Thanks to giving rply to me. I tried what u send to me. and m very close to my output. pls can u gve me output vch i want. I CREATE TABLE [dbo].[attendance2]( [Attenid] [int] NULL [teacherId] [int] NULL [Replacingteacherid] [int] NULL [replacedteacherid] [int] NULL [currentdat] [datetime] NULL [classid] [int] NULL [Studid] [int] NULL ) ON [PRIMARY] insert into attendance2 values(1110'6/7/2012'11) insert into attendance2 values(1110'6/7/2012'12) insert into attendance2 values(1110'6/7/2012'13) insert into attendance2 values(1110'6/7/2012'14) insert into attendance2 values(2110'6/7/2012'22) insert into attendance2 values(2110'6/7/2012'23) insert into attendance2 values(3121'6/7/2012'31) insert into attendance2 values(3121'6/8/2012'32) insert into attendance2 values(4110'6/8/2012'13) insert into attendance2 values(4110'6/8/2012'11) insert into attendance2 values(5121'6/8/2012'22) insert into attendance2 values(5121'6/8/2012'23) select TeacherIdcount(teacherId) as totallectures (Select COUNT(Replacingteacherid) from attendance2 where replacedteacherid = 0 and teacherId = a.teacherId and currentdat = a.currentdat) as Present (Select COUNT(teacherId) from attendance2 where replacedteacherid = a.teacherId and teacherId = a.teacherId and currentdat = a.currentdat) as Absent currentdat from attendance2 a WHERE TeacherId = 1 AND currentdat BETWEEN '6/1/2012' AND '6/8/2012' group by teacherIdcurrentdat Output is: TeacherId totallectures Present Absent currentdat 1 8 6 2 2012-6-7 1 4 2 2 2012-6-8 But I wana Output TeacherId totallectures Present Absent currentdat 1 3 2 1 2012-6-7 1 2 1 1 2012-6-8

Santhosh Kumar Jayaraman · Answer

try this. Query: select TeacherIdcount(*) as totallectures (Select COUNT(*) from attendance where replacedteacherid=0 and teacherId=a.teacherId and classid=a.classid and currentdat=a.currentdat) as Present (Select COUNT(*) from attendance where replacedteacherid=a.teacherId and teacherId=a.teacherId and classid=a.classid and currentdat=a.currentdat) as Absent currentdat from attendance a group by teacherIdclassidcurrentdat Table for that: create table attendance ( Attenid int teacherId int Replacingteacherid int replacedteacherid int currentdat datetime classid int) Insert: insert into attendance values(11 1  0 '6/7/2012'1) insert into attendance values(212 1'6/7/2012' 1) insert into attendance values(3 11 0' 6/7/2012'1) Query: select TeacherIdcount(*) as totallectures (Select COUNT(*) from attendance where replacedteacherid=0 and teacherId=a.teacherId and classid=a.classid and currentdat=a.currentdat) as Present (Select COUNT(*) from attendance where replacedteacherid=a.teacherId and teacherId=a.teacherId and classid=a.classid and currentdat=a.currentdat) as Absent currentdat from attendance a group by teacherIdclassidcurrentdat Please mark this answer as accepted if it solves ur question

sameer gadade · Answer

AttID-TeachrID-ReplacTeachID-ReplacID-StudID-Cur_Date -ClassID 1 - 1 - 1 - 0 - 1 -6/1/2012(m/d/y) 1 1 - 1 - 1 - 0 - 2 -6/1/2012(m/d/y) 1 1 - 1 - 1 - 0 - 3 -6/1/2012(m/d/y) 1 2 - 1 - 1 - 0 - 1 -6/1/2012(m/d/y) 2 2 - 1 - 1 - 0 - 2 -6/1/2012(m/d/y) 2 2 - 1 - 1 - 0 - 3 -6/1/2012(m/d/y) 2 3 - 1 - 1 - 0 - 4 -6/1/2012(m/d/y) 3 3 - 1 - 1 - 0 - 5 -6/1/2012(m/d/y) 3 4 - 1 - 2 - 1 - 11 -6/1/2012(m/d/y) 4 4 - 1 - 2 - 1 - 12 -6/1/2012(m/d/y) 4 5 - 1 - 1 - 0 - 1 -6/1/2012(m/d/y) 1 5 - 1 - 1 - 0 - 2 -6/1/2012(m/d/y) 1 5 - 1 - 1 - 0 - 3 -6/1/2012(m/d/y) 1 6 - 1 - 2 - 1 - 1 -6/2/2012(m/d/y) 2 6 - 1 - 2 - 1 - 2 -6/2/2012(m/d/y) 2 6 - 1 - 2 - 1 - 3 -6/2/2012(m/d/y) 2 This is Student Attendance table from which i wana calculate teacher attendance also. ReplacID must Yes = 1 and No = 0 I wana output like this Search Criteria for this- Teacher ID and From date and To date Output Teacher ID - Present - Absent - Total Lectures Cur_date 1 - 3 - 1 4 - 6/1/2012 1 - 1 - 1 2 - 6/2/2012

Santhosh Kumar Jayaraman · Answer

But your table columns are not enough.. How can i know who was the teacher intially for the 2nd row? TeacherId=0 and replaceteacherid=2 from the above columns i can only know that some teacher is replaced but i dont know which teacher is that!!!

sameer gadade · Answer

no i want attendance(total lectures) of one teacher between two dates

Santhosh Kumar Jayaraman · Answer

Can you explain littlebit more about output that what exactly you want ? You want to take all the teachers into account.