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
 Data

Author  Topic 

immad
Posting Yak Master

230 Posts

Posted - 2013-06-08 : 07:17:17
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 13:53:44
[code]
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]
[/code]

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

immad
Posting Yak Master

230 Posts

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






immad uddin ahmed
Go to Top of Page

immad
Posting Yak Master

230 Posts

Posted - 2013-06-10 : 03:41:20
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 03:48:46
[code]
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[/code]

--
Chandu
Go to Top of Page

immad
Posting Yak Master

230 Posts

Posted - 2013-06-10 : 03:56:35
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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 06:10:11
[code]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
[/code]

--
Chandu
Go to Top of Page

immad
Posting Yak Master

230 Posts

Posted - 2013-06-10 : 06:19:32
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

52326 Posts

Posted - 2013-06-10 : 08:14:54
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

52326 Posts

Posted - 2013-06-10 : 08:20:25
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

52326 Posts

Posted - 2013-06-10 : 08:20:25
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

230 Posts

Posted - 2013-06-10 : 08:36:41
Thank You AGAIN vishak THIS PROBLEM IS SOLVE

immad uddin ahmed
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 08:38:44
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

230 Posts

Posted - 2013-06-11 : 08:56:06
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 09:13:45
-- 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

230 Posts

Posted - 2013-06-12 : 01:16:23
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

52326 Posts

Posted - 2013-06-12 : 01:24:30
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

52326 Posts

Posted - 2013-06-12 : 01:26:02
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

230 Posts

Posted - 2013-06-12 : 01:39:54
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

52326 Posts

Posted - 2013-06-12 : 01:42:34
welcome

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-12 : 01:43:54
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
    Next Page

- Advertisement -