Inserting custom query values from one into anothe

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 2005 Forums

Transact-SQL (2005)

Inserting custom query values from one into anothe

Author

Topic

mas1337
Starting Member

5 Posts

Posted - 2012-04-23 : 13:19:46

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)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 15:14:28

[code]
insert into attendance(fname,
presenttime ,
date )
select fname ,
convert (varchar(10),((outtime_d - intime_a)-(lunchin_c - lunchout_b)),8),
dateadd(dd,datediff(dd,0,getdate()),0)
from timings
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mas1337
Starting Member

5 Posts

Posted - 2012-04-24 : 02:31:13

Visakh16,

dateadd(dd,datediff(dd,0,getdate()),0)

gives me an error "Incorrect syntax near 'dd'."

I ended up changing both the field's datatypes to varchar

and 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'

The results are satisfactory for now.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:53:59

are you using sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-27 : 06:22:38

quote:
Originally posted by visakh16

are you using sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lol...that's the best question I've seen on SQLTeam.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources