Attendance query Required?

Posted by Haider_Sigma under Sql Server on 4/26/2012 | Points: 10 | Views : 1572 | Status : [Member] | Replies : 5
employee(empid ,empname)
attandance(id ,empid,dateofprent day,timein timout)

show empname ,Absentdate that are absent in april2012

Haider Ali
Software Engineer




Responses

Posted by: Pandians on: 4/26/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Try this sample!
Create Table employee

(
empid Int Identity(1,1) Primary Key Not Null,
empname Varchar(100)
)
Go
Insert employee(empname) Values('Pandian')
Insert employee(empname) Values('Mani')
Insert employee(empname) Values('Raj')
Insert employee(empname) Values('Kv')
Insert employee(empname) Values('Naveen')
Go

Create Table attandance
(
id Int Identity(1,1) Primary Key Not Null,
empid Int Foreign Key References employee(empid),
dateofprent DateTime,
timein Time,
timout Time
)
Insert attandance(empid,dateofprent,timein,timout) Values(1,'2012-04-10','09:00','18:00')
Insert attandance(empid,dateofprent,timein,timout) Values(1,'2012-03-10','09:00','18:00')
Insert attandance(empid,dateofprent,timein,timout) Values(2,'2012-03-10','09:00','18:00')
Insert attandance(empid,dateofprent,timein,timout) Values(3,'2012-04-10','09:00','18:00')
Insert attandance(empid,dateofprent,timein,timout) Values(4,'2012-03-10','09:00','18:00')
Insert attandance(empid,dateofprent,timein,timout) Values(5,'2012-04-10','09:00','18:00')
go

select E.empname from employee E Left Join attandance A
On (E.empid = A.empid And Year(a.dateofprent) = 2012 and Month(a.dateofprent) = 4)
Where A.empid is null
go


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Haider_Sigma on: 4/27/2012 [Member] Starter | Points: 25

Up
0
Down
Thank for your reply
but i also show the date(absent date)

Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/27/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
You got the answer or You still have any issue on that ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Haider_Sigma on: 4/28/2012 [Member] Starter | Points: 25

Up
0
Down
Thank for your reply
but i also show the date(absent date)


Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/8/2012 [Member] Starter | Points: 25

Up
0
Down
if the above answer is correct then mark it as resolved

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response