Query Help_union - 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

Query Help_union

Author

Topic

jmohan
Starting Member

2 Posts

Posted - 2010-05-11 : 22:07:24

Hi everyone,

I have a table called log1 table as follows:

employeecode logdate1 logtime1 statusid
101 5/5/2010 0:00 5/5/2010 15:10 101
101 5/5/2010 0:00 5/5/2010 15:20 101
101 5/5/2010 0:00 5/5/2010 15:15 102
101 5/5/2010 0:00 5/5/2010 15:25 102

[ where, statusid 101=in time, 102= out time ]
__________________________________________________________________

I request a query that need to show the result as follows:

employeecode in-time out-time
101 5/5/2010 15:10 5/5/2010 15:15101 5/5/2010 15:20 5/5/2010 15:25__________________________________________________________________

Based on the status id, i need two columns intime, outime from the 'logtime1' column.

I have written a query as follows:

select logdate1,logtime1 as intime,0 as outtime from log1 where statusid=101
UNION
select logdate1,0 as intime,logtime1 as outtime from log1 where statusid=102

but it shows wrong result as follows:

employeecode intime outtime
101 1/1/1900 0:00 5/5/2010 15:15
101 1/1/1900 0:00 5/5/2010 15:25
101 5/5/2010 15:10 1/1/1900 0:00
101 5/5/2010 15:20 1/1/1900 0:00

I need two rows only . but it shows with null value.
__________________________________________________________________

please help me for a query. The right query would be appreciated.
Thanks in Advance,

Mohan J.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-05-11 : 23:57:19

Use the script,

select employeecode,logdate1,logtime1,statusid into #temp from log1 where statusid=101
select employeecode,logdate1,logtime1,statusid into #temp1 from log1 where statusid=102

select #temp.employeecode,#temp.logtime1 as [in time],
(select min(logtime1) from #temp1 where #temp1.logtime1>= #temp.logtime1 and #temp.statusid=101) as [out time] from #temp

drop table #temp
drop table #temp1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-12 : 05:30:25

Try this:


SELECT L1.employeecode, L1.logtime1 AS intime, L2.logtime1 AS outtime
  FROM Log1 AS L1
       OUTER APPLY
       (SELECT TOP(1) logtime1
          FROM Log1 AS L2
         WHERE L1.employeecode = L2.employeecode
           AND statusid = 102
           AND L2.logtime1 > L1.logtime1
         ORDER BY L2.logtime1) AS L2
 WHERE statusid = 101

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources