SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Data
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/08/2013 :  07:17:17  Show Profile  Reply with Quote
My data is like this


eid-------date-------------time--------status
26359---2013-01-01----07:44--------IN
26359---2013-01-01----18:50--------OUT
26359---2013-01-02----07:47--------IN
26359---2013-01-02----18:51--------OUT



i want this type of data

eid-------date-------------timein--------timeout
26359---2013-01-01------07:44--------18:50
26359---2013-01-02------07:47--------18:51

i want timein and timeout side by side

Thanks for the help

immad uddin ahmed

Edited by - immad on 06/08/2013 07:31:30

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/08/2013 :  13:53:44  Show Profile  Reply with Quote

SELECT eid,[date],
MAX(CASE WHEN status = 'IN' THEN [time] END) AS timein,
MAX(CASE WHEN status = 'OUT' THEN [time] END) AS timeout
FROM Table
GROUP BY eid,[date]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/10/2013 :  01:02:21  Show Profile  Reply with Quote
sir your query is working fine but there is one problem
i am solving this problem by myself
thanks






immad uddin ahmed

Edited by - immad on 06/10/2013 01:34:36
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/10/2013 :  03:41:20  Show Profile  Reply with Quote
My data is like this


eid-------date-------------time--------status
26359---2013-01-01--------07:44--------IN
26359---2013-01-01--------18:50--------OUT
26359---2013-01-02--------07:47--------IN
26359---2013-01-02--------18:51--------OUT


sir your query is working fine in above data

but when data looks 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


your query give me this result

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

i want this type of result

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

mean if employee in and out several times then your query give me only last in and out
result

i made this query but it give me First time in and last time out result

SELECT a.eid, a.date, d.InTime, e.OutTime
FROM
(SELECT DISTINCT eid, date 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

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




immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/10/2013 :  03:48:46  Show Profile  Reply with Quote

SELECT EID, Date, MIN(Time) AS Start_Time, MAX(Time) AS End_Time
FROM @Table t
OUTER APPLY (SELECT MIN(Time) AS MinDate
             FROM @Table
             WHERE Time> t.Time
             AND Date <> t.Date	
             AND EID = t.EID
             )t1
GROUP BY EID,DATE,MinDate


--
Chandu

Edited by - bandi on 06/10/2013 03:49:04
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/10/2013 :  03:56:35  Show Profile  Reply with Quote
it gives me same result . i test this query before

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 written in red


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



this is my actual data
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:40:00.000--IN
26359----2013-01-02 13:00:00.000--2013-01-02 18:55:00.000--OUT



immad uddin ahmed

Edited by - immad on 06/10/2013 04:01:06
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/10/2013 :  06:10:11  Show Profile  Reply with Quote
SELECT eid,[date],
MAX([time]) AS timein,
MIN(MinDate) AS timeout
FROM @ATTENDANCE t
OUTER APPLY (SELECT TOP 1 Time AS MinDate
             FROM @ATTENDANCE
             WHERE Time > t.Time 
             AND EID = t.EID
             AND Status = 'OUT'
             ORDER BY Time
             )t1
WHERE Status= 'IN'
GROUP BY EID,DATE,MinDate


--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/10/2013 :  06:19:32  Show Profile  Reply with Quote
quote:
Originally posted by bandi

SELECT eid,[date],
MAX([time]) AS timein,
MIN(MinDate) AS timeout
FROM @ATTENDANCE t
OUTER APPLY (SELECT TOP 1 Time AS MinDate
             FROM @ATTENDANCE
             WHERE Time > t.Time 
             AND EID = t.EID
             AND Status = 'OUT'
             ORDER BY Time
             )t1
WHERE Status= 'IN'
GROUP BY EID,DATE,MinDate


--
Chandu



Thanks :)

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/10/2013 :  08:14:54  Show Profile  Reply with Quote
quote:
Originally posted by bandi

SELECT eid,[date],
MAX([time]) AS timein,
MIN(MinDate) AS timeout
FROM @ATTENDANCE t
OUTER APPLY (SELECT TOP 1 Time AS MinDate
             FROM @ATTENDANCE
             WHERE Time > t.Time 
             AND EID = t.EID
             AND Status = 'OUT'
             ORDER BY Time
             )t1
WHERE Status= 'IN'
GROUP BY EID,DATE,MinDate


--
Chandu


Doesnt require MIN here as you're already grouping on column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/10/2013 :  08:20:25  Show Profile  Reply with Quote
If In and OUT are consistent for a day you could even use


SELECT eid,date,
MAX(CASE WHE Status = 'IN' THEN time END) AS timein,
MAX(CASE WHE Status = 'OUT' THEN time END) AS timeout
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION eid,date,status ORDER BY time) AS Seq FROM Table)t
GROUP BY eid,date,Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/10/2013 :  08:20:25  Show Profile  Reply with Quote
If In and OUT are consistent for a day you could even use


SELECT eid,date,
MAX(CASE WHE Status = 'IN' THEN time END) AS timein,
MAX(CASE WHE Status = 'OUT' THEN time END) AS timeout
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION eid,date,status ORDER BY time) AS Seq FROM Table)t
GROUP BY eid,date,Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/10/2013 :  08:36:41  Show Profile  Reply with Quote
Thank You AGAIN vishak THIS PROBLEM IS SOLVE

immad uddin ahmed

Edited by - immad on 06/11/2013 08:33:33
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/10/2013 :  08:38:44  Show Profile  Reply with Quote
quote:
Originally posted by immad

quote:
Originally posted by bandi

SELECT eid,[date],
MAX([time]) AS timein,
MIN(MinDate) AS timeout
FROM @ATTENDANCE t
OUTER APPLY (SELECT TOP 1 Time AS MinDate
             FROM @ATTENDANCE
             WHERE Time > t.Time 
             AND EID = t.EID
             AND Status = 'OUT'
             ORDER BY Time
             )t1
WHERE Status= 'IN'
GROUP BY EID,DATE,MinDate


--
Chandu



Thanks :)

immad uddin ahmed


Welcome

--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/11/2013 :  08:56:06  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

If In and OUT are consistent for a day you could even use


SELECT eid,date,
MAX(CASE WHE Status = 'IN' THEN time END) AS timein,
MAX(CASE WHE Status = 'OUT' THEN time END) AS timeout
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION eid,date,status ORDER BY time) AS Seq FROM Table)t
GROUP BY eid,date,Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




when i insert this query data it gives me this error

Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.


insert into attend_log
SELECT date,
RTRIM(replace(EID, ' ', '')) EID,
MAX(CASE WHEn Status = 'I' THEN time END) AS timein,
MAX(CASE WHEn Status = 'O' THEN time END) AS timeout
FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq
FROM atend)t
GROUP BY eid,date,Seq
order by date,eid


immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/11/2013 :  09:13:45  Show Profile  Reply with Quote
-- Hey you have placed date column to id of Attend_log table
Do as follows:
insert into attend_log
SELECT RTRIM(replace(EID, ' ', '')) EID,
date,
.
.
.

--
Chandu
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/12/2013 :  01:16:23  Show Profile  Reply with Quote
quote:
Originally posted by bandi

-- Hey you have placed date column to id of Attend_log table
Do as follows:
insert into attend_log
SELECT RTRIM(replace(EID, ' ', '')) EID,
date,
.
.
.

--
Chandu


if i am not wrong

SELECT eid,date,
MAX(CASE WHEN Status = 'IN' THEN time END) AS timein,
MAX(CASE WHEN Status = 'OUT' THEN time END) AS timeout
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq FROM Table)t
GROUP BY eid,date,Seq

if select have eid,date then group by also have same name and same sequence eid,date



immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/12/2013 :  01:24:30  Show Profile  Reply with Quote
quote:
Originally posted by immad

quote:
Originally posted by bandi

-- Hey you have placed date column to id of Attend_log table
Do as follows:
insert into attend_log
SELECT RTRIM(replace(EID, ' ', '')) EID,
date,
.
.
.

--
Chandu


if i am not wrong

SELECT eid,date,
MAX(CASE WHEN Status = 'IN' THEN time END) AS timein,
MAX(CASE WHEN Status = 'OUT' THEN time END) AS timeout
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq FROM Table)t
GROUP BY eid,date,Seq

if select have eid,date then group by also have same name and same sequence eid,date



immad uddin ahmed


nope

sequence doesnt have to be same
only thing is all columns used without aggregation (MIN,MAX etc) in select have to be included in GROUP BY but order can change between select and GROUP BY.
In your case order has to be same as how the columns are in attend_log
or to be safe use like below listing columns

insert into attend_log
(
datefield,
eidfiled,
... put corresponding columns here
)
SELECT date,
RTRIM(replace(EID, ' ', '')) EID,
MAX(CASE WHEn Status = 'I' THEN time END) AS timein,
MAX(CASE WHEn Status = 'O' THEN time END) AS timeout
FROM 
(SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq 
FROM atend)t
GROUP BY eid,date,Seq
order by date,eid


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/12/2013 :  01:26:02  Show Profile  Reply with Quote
also order by has no significance while inserting into a table unless you've an identity field and needs to generate the values in a particular sequence.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/12/2013 :  01:39:54  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

also order by has no significance while inserting into a table unless you've an identity field and needs to generate the values in a particular sequence.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks for explaining me

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/12/2013 :  01:42:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/12/2013 :  01:43:54  Show Profile  Reply with Quote
quote:
Originally posted by immad

quote:
Originally posted by visakh16

also order by has no significance while inserting into a table unless you've an identity field and needs to generate the values in a particular sequence.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




yes i want to use order by becouse i want time in and time out in sequence if i dont then spendtime calculate wrong result


Thanks for explaining me

immad uddin ahmed



immad uddin ahmed
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000