how to bring required data from one table and inse

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

how to bring required data from one table and inse

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-09-18 : 03:24:34

i have a table with data empno,fntype,entrydate,entrytime

fntype 1 is for in and fntype 2 is for out
how to find timein and timeout from the table and insert into another table with empno,date, timein, timeout

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-18 : 05:51:27

insert destination_table(colname1,colname2,...)
select colname1,colname2,...
from source_table
where ...

No, you're never too old to Yak'n'Roll if you're too young to die.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-09-18 : 06:06:23

FnType EntryDate EntryTime EmpNo
01 01-09-2010 13:48:09 '00000000002127
02 01-09-2010 14:51:18 '00000000002865
01 01-09-201 14:55:02 '00000000007728
02 01-09-2010 14:55:11 '00000000000092
02 01-09-2010 15:00:45 '00000000007385
02 01-09-2010 15:01:15 '00000000007463
02 01-09-2010 15:14:09 '00000000005237

From this table i want to insert timein and timeout based on fntype

in a new table with following fields

Empno EntryDate Timein Timout

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-18 : 07:27:11

Please give better example data.
The given data has no Emp with one IN and one OUT.
The next question is if there are many different dates for one Emp.

So please give good examples and the wanted output in relation to the examples.

No, you're never too old to Yak'n'Roll if you're too young to die.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-09-18 : 07:52:21

I have a table with following data

FnType EntryDate EntryTime EmpNo

1 01/01/2010 10:48:09 13
2 01/01/2010 13:48:09 13
1 02/01/2010 10:10:09 13
2 02/01/2010 12:48:09 13

fnType 1 is for timein
fntype 2 is for timeout

using this (EntryTime and Fntype) i want to find time and timeout for given employee

after finding the records i want to put the data in new table in this form

EmpNo EntryDate Timein timeout

13 01/01/2010 10:48:09 13:48:09
13 02/01/2010 10:10:09 12:48:09

please help thanx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-18 : 09:48:13

You can replace @sample by your tablename.


-- create sample data
declare @sample table (FnType tinyint, EntryDate varchar(10), EntryTime varchar(8), EmpNo int)

insert @sample
select 1, '01/01/2010', '10:48:09', 13 union all
select 2, '01/01/2010', '13:48:09', 13 union all
select 1, '02/01/2010', '10:10:09', 13 union all
select 2, '02/01/2010', '12:48:09', 13
-- show sample data
select * from @sample

-- the solution
select
t1.EmpNo, 
t1.EntryDate,
t1.EntryTime as Timein,
t2.EntryTime as timeout
from @sample as t1
join @sample as t2
on t1.EmpNo=t2.EmpNo
and t1.EntryDate=t2.EntryDate
and t1.FnType=1
and t2.FnType=2

No, you're never too old to Yak'n'Roll if you're too young to die.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-09-18 : 10:38:41

this will work if FnType 1 and 2 is there
say if FnType 2 that is Timeout is not there, in that case i want to insert Absent for time out how we will do that

please help thanx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-18 : 10:42:11

[code]-- create sample data
declare @sample table (FnType tinyint, EntryDate varchar(10), EntryTime varchar(8), EmpNo int)

insert @sample
select 1, '01/01/2010', '10:48:09', 13 union all
select 2, '01/01/2010', '13:48:09', 13 union all
select 1, '02/01/2010', '10:10:09', 13 union all
select 2, '02/01/2010', '12:48:09', 13 union all
select 1, '02/01/2010', '09:45:00', 22

select * from @sample

select
t1.EmpNo,
t1.EntryDate,
t1.EntryTime as Timein,
isnull(t2.EntryTime,'Absent') as timeout
from @sample as t1
left join @sample as t2
on t1.EmpNo=t2.EmpNo
and t1.EntryDate=t2.EntryDate
and t1.FnType=1
and t2.FnType=2
Where t1.Fntype=1
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-09-19 : 02:42:19

thnx a lot for your help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-19 : 05:45:00

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-02 : 02:24:15

I have two table employee and schedule
employee where i am having all details of employee
and schedule for employee in the form of schedule id like
mon=1 tuesday=1 thu 3 etc.

in schedule table i am having schid, timein, timeout

i want to run the query where i will get all empdetail + timein , timeout for mon,tue and thursday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources