help me plz in this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help me plz in this query

Author

Topic

rsankhwaya
Starting Member

4 Posts

Posted - 2010-05-18 : 07:31:39

i m sending u a table structure. and i want to execute a query on it. plz help me.

structure of table attendance log

CREATE TABLE [dbo].[AttendanceLog](
[TransID] [int] NOT NULL,
[TransType] [varchar](5) NULL,
[TransDtTime] [datetime] NULL,
[EmpID] [int] NULL,
[PCName] [varchar](50))

Data in Table are
INSERT INTO [Temp].[dbo].[AttendanceLog]
([TransID]
,[TransType]
,[TransDtTime]
,[EmpID]
,[PCName])
VALUES
(101,1,15/5/2010 09:00:00 AM ,101,AAA)
(102,1,15/5/2010 09:10:00 AM ,102,bbb)
(103,1,15/5/2010 09:20:00 AM ,103,ccc)
(104,0,15/5/2010 13:00:00 PM ,102,bbb)
(105,0,15/5/2010 14:00:00 PM ,101,AAA)
(106,1,15/5/2010 14:15:00 PM ,101,aaa)
(107,0,15/5/2010 16:00:00 PM ,103,ccc)
(108,0,15/5/2010 18:00:00 PM ,101,AAA)
(109,1,15/5/2010 18:10:00 PM ,102,bbb)

now i want to execute a query which show report of single day like

Empid LogInTime LogOutTime Duration

problem is that a employee can login and logout more than 1 time in a day.

i want a solution for that.

LogInTime and LogOutTime both calculated form transDtTime attribute on base of TransType("0" for logout and "1" for login) and Duration is (LogOutTime-LogInTime)

my query is

SELECT CONVERT(varchar(12), a.TransDtTime, 108) AS LoginTime, CONVERT(varchar(12), b.TransDtTime, 108)
AS LogoutTime, CONVERT(varchar(12), b.TransDtTime - a.TransDtTime, 108) AS Duration
FROM AttendanceLog AS a INNER JOIN
AttendanceLog AS b ON a.EmpID = b.EmpID
WHERE (a.TransType = '1') AND (b.TransType = '0')

thanks
Ravindra Prajapat

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-18 : 08:19:53

quote:
Originally posted by rsankhwaya
plz sir my today selection is based on it plz help me.
Ravindra Prajapat

What does this mean?
I read this as you need to solve this problem to get the job, but you do not know how to solve it. This tells me you are NOT qualified for the job.

If you would like the answer within 2 hours, i am sure SOMEBODY here will be willing to give you the answer for a hefty consulting fee...

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rsankhwaya
Starting Member

4 Posts

Posted - 2010-05-18 : 08:26:17

quote:
Originally posted by DonAtWork

quote:
Originally posted by rsankhwaya
plz sir my today selection is based on it plz help me.
Ravindra Prajapat

What does this mean?
I read this as you need to solve this problem to get the job, but you do not know how to solve it. This tells me you are NOT qualified for the job.

If you would like the answer within 2 hours, i am sure SOMEBODY here will be willing to give you the answer for a hefty consulting fee...

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

i m not want it for my job. this is part of my campus year end project

thanks
Ravindra Prajapat

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-18 : 08:50:18

If I do your project, do I get the qualification?

We're not going to do your homework for you. You learn nothing in the process. Show us what you've done so far and we'll offer advice and suggestions.

--
Gail Shaw
SQL Server MVP

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-18 : 09:30:22

He did show us the query he has tried so far.

Ravindra: Follow the HOW TO ASK link so that you can give us the information we need to help you solve this problem.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rsankhwaya
Starting Member

4 Posts

Posted - 2010-05-18 : 20:39:24

quote:
Originally posted by DonAtWork

He did show us the query he has tried so far.

Ravindra: Follow the HOW TO ASK link so that you can give us the information we need to help you solve this problem.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

thanks don sorry this is my first time to use a forum so plz ignore it. i m changing the post in right way plz try to help me. thanks for ur reply

thanks
Ravindra Prajapat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources