April 23, 2012 at 11:25 am
Hi
I have a table timings with 4 different time fields namely : intime, outtime, lunchout, lunchin. I'm trying to find total hours logged i.e ((outtime-intime) - (lunchin-lunchout). Which I can fin out successfully using :
select convert (varchar(10),((outtime_d - intime_a)-(lunchin_c - lunchout_b)),8) as time1 from timings where fname = 'abc'
create table timings
(
fname varchar(max),
fid varchar(30) PRIMARY KEY CLUSTERED,
intime_a datetime,
outtime_d datetime,
lunchout_b datetime,
lunchin_c datetime
)
insert into timings values('Abc','4C00A2C82A0C','4/23/2012 2:07:51 PM','4/23/2012 9:07:51 PM','4/23/2012 4:12:51 PM','4/23/2012 5:07:51 PM')
time1 = 06:05:00
I have another table called attendance with the following fields : fname, presenttime, date.
create attendance timings
(
fname varchar(max),
presenttime datetime,
date datetime
)
I need to insert into attendance:
fname: fname from timings
presenttime: time1 - the result of above listed query
date: current system date. (only date, not time, need to remove timestamp from getdate() function)
This is all I could comeup with, I know its probably wrong, but that's the limit of my SQL
insert into attendance (fname,presenttime,date)
values
(
a.fname,
(select convert (varchar(10),((outtime_d - intime_a)-(lunchin_c - lunchout_b)),8) as time1 from timings where fname = 'abc'),
getdate()
)
from timings a
I first attempted to use the datediff function, and late concatenate the values, but I was getting incorrect results.
SELECT ROUND(cast((datediff(hh, outtime_d ,intime_a) / 60.0) as FLOAT),2) AS DiffHour from timings where fname = 'abc'
SELECT ROUND(cast((datediff(mi, outtime_d ,intime_a) / 60.0) as FLOAT),2) AS DiffMinute from timings where fname = 'abc'
SELECT ROUND(cast((datediff(ss, outtime_d ,intime_a) / 60.0) as FLOAT),2) AS DiffSec from timings where fname = 'abc'
Thanks
April 23, 2012 at 11:58 am
Dump the presenttime column, and replace it with either one column for the number of minutes present (can be converted to hours and minutes either in queries or in the presentation layer), or with one column for hours and one for minutes. I'd just store minutes present, since that reduces the total number of calculations you need to do at all stages.
As a suggestion, re-design your clock-in/clock-out table. Make it vertical instead of horizontal. Otherwise, you'll have a problem the moment someone needs to clock out for a doctor's appointment and then clock back in, and still takes a lunch break, or anything similar to that.
Something like:
create table dbo.TimeClock (
ID int identity primary key,
EmployeeID int not null,
ClockTime datetime not null,
Direction char(1),
constraint CK_Direction check (Direction in ('I','O'));
That allows more flexibility. You can add a "Type" column to it to record things like "Lunch" or "End of Day" values (perhaps an ID value from a lookup table that lists the potential reasons), but it's not usually necessary to do so.
Will make it much less work in the future when you need to add more functionality to this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 12:37 pm
GSquared (4/23/2012)
Dump the presenttime column, and replace it with either one column for the number of minutes present (can be converted to hours and minutes either in queries or in the presentation layer), or with one column for hours and one for minutes. I'd just store minutes present, since that reduces the total number of calculations you need to do at all stages.As a suggestion, re-design your clock-in/clock-out table. Make it vertical instead of horizontal. Otherwise, you'll have a problem the moment someone needs to clock out for a doctor's appointment and then clock back in, and still takes a lunch break, or anything similar to that.
Something like:
create table dbo.TimeClock (
ID int identity primary key,
EmployeeID int not null,
ClockTime datetime not null,
Direction char(1),
constraint CK_Direction check (Direction in ('I','O'));That allows more flexibility. You can add a "Type" column to it to record things like "Lunch" or "End of Day" values (perhaps an ID value from a lookup table that lists the potential reasons), but it's not usually necessary to do so.
Will make it much less work in the future when you need to add more functionality to this.
If you refer to one of my previous threads, you can get a better picture of where the present timings table is being populated from.
http://www.sqlservercentral.com/Forums/Topic1286923-338-1.aspx
While using
SELECT fname,
CAST(CONVERT (VARCHAR(10),((outtime_d - intime_a)-(lunchin_c - lunchout_b)),8) AS varchar(10)) AS time1,
DATEADD(DAY, DATEDIFF(DAY, '19000101',CURRENT_TIMESTAMP),'19000101') AS DateOnly
FROM timings WHERE fname = 'abc'
I get the following:
fname time1 DateOnly
------------------------------------- ---------- -----------------------
Abc 02:15:54 2012-04-23 00:00:00.000
Abc 06:05:00 2012-04-23 00:00:00.000
I can store the date only, by changing the datatype of the field to varchar, and using
CONVERT (VARCHAR(10),getdate(),111)
but how would I insert it in the above query?
THanks
PS - I will take your suggestion into consideration to accommodate other miscellaneous check ins/outs.
Update:
Figured it out, used this:
insert into attendance(fname,presenttime,date)
SELECT
fname,
CAST(CONVERT (VARCHAR(10),((outtime_d - intime_a)-(lunchin_c - lunchout_b)),8) AS varchar(10)),
CONVERT (VARCHAR(10),getdate(),3)
FROM timings WHERE fname = 'abc'
Although I had to convert both my fields to varchar from datetime, to get the desired format.
April 24, 2012 at 6:31 am
Using varchar to hold date and time data is fine, so long as nobody will ever require that it be used for anything other than a human eye looking at it.
Storing it that way is generally considered a really bad idea, because the moment someone wants you to calculate things like total hours for the week, month, quarter, or year, or average duration of lunch break, or any of a millino other things that HR and accounting departments and managers and even employees might want, you have to jump through all kinds of complex hoops in order to do any of those things.
Do what you're going to do with this, but keep in mind that it's been done before, and has proven to be more of a problem than a solution most of the time.
Can't tell you what to do. Wouldn't even if I could. But I do suggest changing the direction of where you're going with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy