Author |
Topic |
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-08 : 07:17:17
|
My data is like thiseid-------date-------------time--------status26359---2013-01-01----07:44--------IN26359---2013-01-01----18:50--------OUT26359---2013-01-02----07:47--------IN26359---2013-01-02----18:51--------OUTi want this type of dataeid-------date-------------timein--------timeout26359---2013-01-01------07:44--------18:5026359---2013-01-02------07:47--------18:51i want timein and timeout side by sideThanks for the helpimmad 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 timeoutFROM TableGROUP BY eid,[date][/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 thanksimmad uddin ahmed |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-10 : 03:41:20
|
My data is like this eid-------date-------------time--------status26359---2013-01-01--------07:44--------IN26359---2013-01-01--------18:50--------OUT26359---2013-01-02--------07:47--------IN26359---2013-01-02--------18:51--------OUTsir your query is working fine in above data but when data looks like this eid--------------date------------------time---------------status26359----2013-01-01 13:00:00.000--2013-01-01 09:50:00.000--IN26359----2013-01-01 13:00:00.000--2013-01-01 11:47:00.000--OUT26359----2013-01-01 13:00:00.000--2013-01-01 17:21:00.000--IN26359----2013-01-01 13:00:00.000--2013-01-01 18:40:00.000--OUTyour query give me this result eid-----date----------------------------timein------------------timeout26359---2013-01-01 13:00:00.000--2013-01-01 17:21:00.000--2013-01-01 18:40:00.000i want this type of resulteid-------date-------------timein---------------------------timeout26359---2013-01-01------2013-01-01 09:50:00.000--------2013-01-01 11:47:00.00026359---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 resulti made this query but it give me First time in and last time out resultSELECT a.eid, a.date, d.InTime, e.OutTimeFROM (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) eRESULT:eid-----------date--------------------timein-------------------------timeout26359----2013-01-01 13:00:00.000---2013-01-01 09:50:00.000---2013-01-01 18:40:00.000immad uddin ahmed |
|
|
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_TimeFROM @Table tOUTER APPLY (SELECT MIN(Time) AS MinDate FROM @Table WHERE Time> t.Time AND Date <> t.Date AND EID = t.EID )t1GROUP BY EID,DATE,MinDate[/code]--Chandu |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-10 : 03:56:35
|
it gives me same result . i test this query beforeeid-------------------date----------------------timein--------------------------timeout26359------2013-01-01 13:00:00.000-----2013-01-01 09:50:00.000---2013-01-01 18:40:00.000i want this type of result written in redeid-------date---------------timein------------------------------------timeout26359---2013-01-01------2013-01-01 09:50:00.000--------2013-01-01 11:47:00.00026359---2013-01-02------2013-01-01 17:21:00.000--------2013-01-01 18:40:00.000this is my actual dataeid--------------date------------------time---------------status26359----2013-01-01 13:00:00.000--2013-01-01 09:50:00.000--IN26359----2013-01-01 13:00:00.000--2013-01-01 11:47:00.000--OUT26359----2013-01-01 13:00:00.000--2013-01-01 17:21:00.000--IN26359----2013-01-01 13:00:00.000--2013-01-01 18:40:00.000--OUT26359----2013-01-02 13:00:00.000--2013-01-02 09:40:00.000--IN26359----2013-01-02 13:00:00.000--2013-01-02 18:55:00.000--OUTimmad uddin ahmed |
|
|
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 timeoutFROM @ATTENDANCE tOUTER APPLY (SELECT TOP 1 Time AS MinDate FROM @ATTENDANCE WHERE Time > t.Time AND EID = t.EID AND Status = 'OUT' ORDER BY Time )t1WHERE Status= 'IN'GROUP BY EID,DATE,MinDate[/code]--Chandu |
|
|
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 timeoutFROM @ATTENDANCE tOUTER APPLY (SELECT TOP 1 Time AS MinDate FROM @ATTENDANCE WHERE Time > t.Time AND EID = t.EID AND Status = 'OUT' ORDER BY Time )t1WHERE Status= 'IN'GROUP BY EID,DATE,MinDate --Chandu
Thanks :)immad uddin ahmed |
|
|
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 timeoutFROM @ATTENDANCE tOUTER APPLY (SELECT TOP 1 Time AS MinDate FROM @ATTENDANCE WHERE Time > t.Time AND EID = t.EID AND Status = 'OUT' ORDER BY Time )t1WHERE Status= 'IN'GROUP BY EID,DATE,MinDate --Chandu
Doesnt require MIN here as you're already grouping on column------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 useSELECT eid,date,MAX(CASE WHE Status = 'IN' THEN time END) AS timein,MAX(CASE WHE Status = 'OUT' THEN time END) AS timeoutFROM (SELECT *,ROW_NUMBER() OVER (PARTITION eid,date,status ORDER BY time) AS Seq FROM Table)tGROUP BY eid,date,Seq ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 useSELECT eid,date,MAX(CASE WHE Status = 'IN' THEN time END) AS timein,MAX(CASE WHE Status = 'OUT' THEN time END) AS timeoutFROM (SELECT *,ROW_NUMBER() OVER (PARTITION eid,date,status ORDER BY time) AS Seq FROM Table)tGROUP BY eid,date,Seq ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-10 : 08:36:41
|
Thank You AGAIN vishak THIS PROBLEM IS SOLVE immad uddin ahmed |
|
|
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 timeoutFROM @ATTENDANCE tOUTER APPLY (SELECT TOP 1 Time AS MinDate FROM @ATTENDANCE WHERE Time > t.Time AND EID = t.EID AND Status = 'OUT' ORDER BY Time )t1WHERE Status= 'IN'GROUP BY EID,DATE,MinDate --Chandu
Thanks :)immad uddin ahmed
Welcome--Chandu |
|
|
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 useSELECT eid,date,MAX(CASE WHE Status = 'IN' THEN time END) AS timein,MAX(CASE WHE Status = 'OUT' THEN time END) AS timeoutFROM (SELECT *,ROW_NUMBER() OVER (PARTITION eid,date,status ORDER BY time) AS Seq FROM Table)tGROUP BY eid,date,Seq ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
when i insert this query data it gives me this errorMsg 257, Level 16, State 3, Line 2Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.insert into attend_logSELECT date,RTRIM(replace(EID, ' ', '')) EID,MAX(CASE WHEn Status = 'I' THEN time END) AS timein,MAX(CASE WHEn Status = 'O' THEN time END) AS timeoutFROM (SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq FROM atend)tGROUP BY eid,date,Seqorder by date,eidimmad uddin ahmed |
|
|
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 tableDo as follows:insert into attend_logSELECT RTRIM(replace(EID, ' ', '')) EID,date,...--Chandu |
|
|
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 tableDo as follows:insert into attend_logSELECT RTRIM(replace(EID, ' ', '')) EID,date,...--Chandu
if i am not wrongSELECT eid,date,MAX(CASE WHEN Status = 'IN' THEN time END) AS timein,MAX(CASE WHEN Status = 'OUT' THEN time END) AS timeoutFROM (SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq FROM Table)tGROUP BY eid,date,Seqif select have eid,date then group by also have same name and same sequence eid,dateimmad uddin ahmed |
|
|
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 tableDo as follows:insert into attend_logSELECT RTRIM(replace(EID, ' ', '')) EID,date,...--Chandu
if i am not wrongSELECT eid,date,MAX(CASE WHEN Status = 'IN' THEN time END) AS timein,MAX(CASE WHEN Status = 'OUT' THEN time END) AS timeoutFROM (SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq FROM Table)tGROUP BY eid,date,Seqif select have eid,date then group by also have same name and same sequence eid,dateimmad uddin ahmed
nopesequence doesnt have to be sameonly 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_logor 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 timeoutFROM (SELECT *,ROW_NUMBER() OVER (PARTITION by eid,date,status ORDER BY time) AS Seq FROM atend)tGROUP BY eid,date,Seqorder by date,eid ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Thanks for explaining meimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 01:42:34
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://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 resultThanks for explaining meimmad uddin ahmed
immad uddin ahmed |
|
|
Previous Page&nsp;
Next Page
|