Timesheet 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

SQL Server 2000 Forums

SQL Server Development (2000)

Timesheet Query

Author

Topic

Session101
Starting Member

15 Posts

Posted - 2004-07-29 : 12:01:25

I am creating a timesheet for users by extracting their clock-in and clock-out times from multiple SQL Server tables. Below is the output that I am extracting from my query:

ID ID DESC ClockIn LunchOut LunchIn ClockOut UserID Date
--- ------- ------- -------- ------- -------- ------ ----
1 For the day 6:09AM NULL NULL 10:33AM 139 7/27
2 For Lunch NULL 11:38AM 10:33AM NULL 139 7/27
1 For the day 11:38AM NULL NULL 7:45PM 139 7/27

The output that I want should be in one only row:

ClockIn LunchOut LunchIn ClockOut UserID Date
------- ------- -------- -------- ------ -----
6:09AM 10:33AM 11:38 7:45PM 139 7/27

I suppose what I am trying to ask is how to create one uniqie row if I need acquire data from 3 rows.

Thanks,
Karl

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 12:53:21

Where is the data coming from?? What is the query you are using...

Corey

Session101
Starting Member

15 Posts

Posted - 2004-07-29 : 13:03:00

The query that I am using is:

SELECT ID, IDDesc,
convert(varchar(50),GetDate(), 101),
'ClockIn' = CASE
WHEN ID = 1 THEN SubString(Convert
(VARCHAR(30), TimeIn, 100), 13, 109)
END,
'LunchOut' =
CASE
WHEN ID = 2 THEN SubString(Convert(VARCHAR(30), TimeOut,
100), 13, 109)
END,
'LunchIn' =
CASE
WHEN ID = 2 THEN SubString(Convert(VARCHAR(30), TimeIn,
100), 13, 109)
END,
'ClockOut' =
CASE
WHEN ID = 1 THEN SubString(Convert(VARCHAR(30), TimeOut,
100), 13, 109)
END,
convert(varchar(50),SystemUser.firstname) + ' ' + convert(varchar(50), SystemUser.lastname)
FROM time
JOIN timeCode
ON time.ID = timeCode.ID
JOIN SystemUser
ON time.SystemUserID = SystemUser.SystemUserID
WHERE time.SystemUserID = '139'
AND timeIn >= '7/26/2004' and timeIn < '7/27/2004'

The three tables that I am joining are: time, timeCode, and SystemUser. They are all SQL Server tables. Thanks!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 13:45:12

this might work:

Select
u.systemUserId,
ClockIn = (Select SubString(Convert(VARCHAR(30), TimeIn, 100), 13, 109) From time Where systemUserId = u.systemUserId and Id = 1 and timeIn >= convert(datetime,convert(nvarchar,getdate(),101)) and timeIn < convert(datetime,convert(nvarchar,getdate()+1,101))),
LunchOut = (Select SubString(Convert(VARCHAR(30), TimeOut, 100), 13, 109) From time Where systemUserId = u.systemUserId and Id = 2 and timeIn >= convert(datetime,convert(nvarchar,getdate(),101)) and timeIn < convert(datetime,convert(nvarchar,getdate()+1,101))),
LunchIn = (Select SubString(Convert(VARCHAR(30), TimeIn, 100), 13, 109) From time Where systemUserId = u.systemUserId and Id = 2 and timeIn >= convert(datetime,convert(nvarchar,getdate(),101)) and timeIn < convert(datetime,convert(nvarchar,getdate()+1,101))),
ClockOut = (Select SubString(Convert(VARCHAR(30), TimeOut, 100), 13, 109) From time Where systemUserId = u.systemUserId and Id = 1 and timeIn >= convert(datetime,convert(nvarchar,getdate(),101)) and timeIn < convert(datetime,convert(nvarchar,getdate()+1,101)))
From SystemUser as u
Where u.systemUserId = '139'

but just to add my 2 cents... I don't think you are going about this time clock the right way. Ideally, this would work, but it is very possible if not likely that someone will need to clock in for multiple periods during a given day. Plus you need to take in consideration for vacation and such.

Corey

Session101
Starting Member

15 Posts

Posted - 2004-07-29 : 16:23:17

Thanks Corey for your responses as they are greatly appreciated. After inputting your revised query, it still outputs multiple rows. I need it to output one unique row that will have one row comprising of the clockIn, clockOut, LunchIn, and LunchOut. Any ideas? Thanks!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-29 : 16:49:18

it shouldn't return more than 1 row... How many entries are there for systemUserId = '139' in systemusers???

show the query you used...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources