Query Problem

[immad]

my data is like this

eid-----------date------------------------------time--------------------------status
26359---2013-01-01 13:00:00.000------2013-01-01 09:50:00.000--------IN
26359---2013-01-01 13:00:00.000------2013-01-01 11:47:00.000--------OUT
26359---2013-01-01 13:00:00.000------2013-01-01 17:21:00.000--------IN
26359---2013-01-01 13:00:00.000------2013-01-01 18:40:00.000--------OUT
26359---2013-01-02 13:00:00.000------2013-01-02 09:00:00.000--------IN
26359---2013-01-02 13:00:00.000------2013-01-02 18:00:00.000--------OUT

i am using this query

SELECT a.eid, a.date, d.InTime, e.OutTime
FROM
(SELECT DISTINCT date, eid FROM #TestTb) a
OUTER APPLY (SELECT b.eid, b.Date, MIN(b.Time) InTime FROM #TestTb b WHERE a.eid = b.eid AND a.date = b.date GROUP BY b.eid, b.Date) d
OUTER APPLY (SELECT c.eid, c.Date, MAX(c.Time) OutTime FROM #TestTb c WHERE a.eid = c.eid AND a.date = c.date GROUP BY c.eid, c.Date) e

it gives me that result

eid------------------date-----------------------timein-----------------------timeout
26359--------2013-01-01 13:00:00.000---2013-01-01 09:50:00.000--2013-01-01 18:40:00.000

i want this type of result

eid------------------date--------------------timein------------------------------timeout
26359--------2013-01-01 13:00:00.000---2013-01-01 09:50:00.000--2013-01-01 11:47:00.000
26359--------2013-01-01 13:00:00.000---2013-01-01 17:21:00.000--2013-01-01 18:40:00.000


please help me out
thanks in advance

 

[sabinUE]

;with InData
AS
(
	Select 
		  eid
		,[date]
		,[time]
		,ROW_NUMBER() OVER(Partition by [eid],[Date] Order by [Date],[time]) AS [rn]
		,[status]
	From #TestTb
	Where [status]='IN'	
)
,OutData
AS
(
	Select 
		eid
		,[date]
		,[time]
		,ROW_NUMBER() OVER(Partition by [eid],[Date] Order by [Date],[time]) AS [rn]
		,[status]
	From #TestTb
	Where [status]='Out'	
)
	
	
SELECT 
	COALESCE(A.[eid],B.[eid]) as [eid]
	,COALESCE(A.[date],B.[date]) as [date]
	,A.[time] as [timeIn]
	,B.[time] as [timeOut]
FROM
(SELECT 
	eid
	,[date]
	,[time]
	,[rn]
 FROM
	[InData])A
FULL JOIN
(SELECT 
	eid
	,[date]
	,[time]
	,[rn]
 FROM
	[OutData])B
 ON 
 A.[eid]=B.[eid]
 AND A.[date]=B.[date]
 AND A.[rn]=B.[rn]

Sabin

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[imex]

Try:

SELECT 
    i.eid, 
    i.date, 
    i.time as InTime,
    (SELECT TOP 1 o.time 
     FROM #TestTb as o 
     where 
         o.eid = i.eid and 
         o.date = i.date and
         o.time > i.time and
         o.status = 'OUT'
     order by o.time) as OutTime
FROM FROM #TestTb as i
where
    i.status = 'IN'

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[immad]

Dear imex

query give me this result
eid--------------date--------------------timein-----------------------------timeout
886------2013-01-01 00:00:00.000---2013-06-10 08:07:00.000---2013-06-10 20:09:00.000


but actual data is this

eid-----------date-------------------------time---------------------------status
886-----2013-01-01 00:00:00.000----2013-06-10 08:06:00.000------Out
886-----2013-01-01 00:00:00.000----2013-06-10 08:07:00.000------IN
886-----2013-01-01 00:00:00.000----2013-06-10 20:09:00.000------Out


its didnt show red line

i want every data show in result.if one row is IN and 2 rows are out then its show like this

eid--------------date--------------------timein--------------------------timeout
886-----2013-01-01 00:00:00.000------------------------------2013-06-10 08:06:00.000
886------2013-01-01 00:00:00.000---2013-06-10 08:07:00.000---2013-06-10 20:09:00.000


if there are two or three rows then data show like those time are IN status then its show in timein column and those time are out status then data show in timeOUT column
weather how many line we have in data.i am giving u an example there are lots of employee who timein and timeout lots of time

basicaly i am making a attednace software some time employee do time in and time out several times thats why i want to make a report on attednace system and show to managers that how many time employee do timein and time out so managers ask reason to employee
i hope u under stand

 

[imex]

Try:

with CTE_RN as
(
    select
        t.*,
        ROW_NUMBER() OVER(PARTITION BY t.eid ORDER BY t.[time]) as RN
    from #TestTb as t
)
    
select 
    r.eid, 
    r.date, 
    case when r.status = 'IN' 
        then r.time 
    end as [TimeIn],
    case when r.status = 'OUT' 
        then r.time 
        else case when n.status = 'OUT'
                 then n.time 
             end
    end as [TimeOut]
from CTE_RN as r
left join CTE_RN as n
    on n.RN = r.RN + 1
left join CTE_RN as p
    on p.RN = r.RN - 1
where 
    r.status = 'IN' or
    p.status is null or
    p.status = 'OUT'

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[immad]

Thanks its working