need help to run 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

need help to run this query

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-08 : 02:48:50

you guys help me in solving this query but there is still one problem
can u help me
-- 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

this is working and showing absent for emp 22 in timeout

but what if there is timeout for emp 23 and i want to show null for time in

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 2, '02/01/2010', '13:45:00', 23 union all
select 1, '02/01/2010', '09:45:00', 22

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 03:53:52

[code]
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 2, '02/01/2010', '13:45:00', 23 union all
select 1, '02/01/2010', '09:45:00', 22

;with cte
as
(
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
)

select * from cte
union all
select EmpNo,entrydate,null,EntryTime from @sample s where not exists(select * from cte where s.EmpNo=cte.EmpNo)
[/code]

PBUH

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-08 : 05:34:46

Thanx a lot but how do i insert this select into another table

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 05:41:53

[code]

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 2, '02/01/2010', '13:45:00', 23 union all
select 1, '02/01/2010', '09:45:00', 22

;with cte
as
(
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
)
, cte1
as
(
select * from cte
union all
select EmpNo,entrydate,null,EntryTime from @sample s where not exists(select * from cte where s.EmpNo=cte.EmpNo)
)

insert into @yourtable

select * from cte1

[/code]

PBUH

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-08 : 06:55:49

I tried this with real data but for date 2010-09-01 and date 2010-09-09 its not showing null for timein
please help

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

insert @sample
SELECT 02,'2010-09-01','16:33:40',7930 union all
SELECT 01,'2010-09-04','09:12:17',7930 union all
SELECT 02,'2010-09-04','17:09:34',7930 union all
SELECT 01,'2010-09-05','09:33:13',7930 union all
SELECT 02,'2010-09-05','17:26:16',7930 union all
SELECT 01,'2010-09-06','09:08:03',7930 union all
SELECT 02,'2010-09-06','16:37:20',7930 union all
SELECT 02,'2010-09-09','16:37:20',7930 union all
SELECT 01,'2010-09-07','09:12:16',7930

--select * from @sample
;with cte
as
(
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
)

select * from cte
union all
select EmpNo,entrydate,null,EntryTime from @sample s where not exists(select * from cte where s.EmpNo=cte.EmpNo)

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 07:24:41

Because in your initial requirement you had different empno and different dates but in your current sample data you have same empno
but different dates


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

insert @sample
SELECT 02,'2010-09-01','16:33:40',7930 union all 
SELECT 01,'2010-09-04','09:12:17',7930 union all 
SELECT 02,'2010-09-04','17:09:34',7930 union all 
SELECT 01,'2010-09-05','09:33:13',7930 union all 
SELECT 02,'2010-09-05','17:26:16',7930 union all 
SELECT 01,'2010-09-06','09:08:03',7930 union all 
SELECT 02,'2010-09-06','16:37:20',7930 union all 
SELECT 02,'2010-09-09','16:37:20',7930 union all 
SELECT 01,'2010-09-07','09:12:16',7930 

select * from @sample order by EntryDate,FnType
;with cte
as
(
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
)

select * from cte
union all
select EmpNo,entrydate,null,EntryTime from @sample s where not exists(select * from cte where s.EmpNo=cte.EmpNo and s.EntryDate=cte.EntryDate)

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources