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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

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


where's spendtime? I cant see such a column in any of posted queries

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

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/12/2013 :  02:27:11  Show Profile  Reply with Quote
well i am making a software that calculate spend time and excess short from employee time in and time out

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/12/2013 :  02:35:11  Show Profile  Reply with Quote
quote:
Originally posted by immad

well i am making a software that calculate spend time and excess short from employee time in and time out

immad uddin ahmed


ok so i guess what you want is to find time difference between consecutive timeout and timein values which is why you cant dispense with the order by.

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

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/12/2013 :  02:45:26  Show Profile  Reply with Quote
Absolutely

immad uddin ahmed
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/13/2013 :  02:35:38  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




there is a problem related to my data



eid-----------date----------------------------timein--------------------------timeout----
26153----2013-01-03 00:00:00.000----2013-06-11 08:14:00.000-----2013-06-11 17:50:00.000
26153----2013-01-03 00:00:00.000----2013-06-11 08:15:00.000-----NULL
26153----2013-01-03 00:00:00.000----2013-06-11 08:15:00.000-----NULL
26153----2013-01-03 00:00:00.000----2013-06-11 08:15:00.000-----NULL
26153----2013-01-03 00:00:00.000----NULL-----------------------------2013-06-11 17:50:00.000



i want this type of data

eid-----------date----------------------------timein--------------------------timeout----
26153----2013-01-03 00:00:00.000----2013-06-11 08:14:00.000-----2013-06-11 17:50:00.000


i dont want that time in is provided but time out is null or time out provided but time in is null actullay its effect my spend time
please implement on this query

SELECT
RTRIM(replace(EID, ' ', '')) EID,
date,
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,[timein],[timeout]

thanks

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  02:40:26  Show Profile  Reply with Quote
do you mean this?

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
HAVING MAX(CASE WHEN Status = 'IN' THEN time END) IS NOT NULL 
AND MAX(CASE WHEN Status = 'OUT' THEN time END) IS NOT NULL 



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

Edited by - visakh16 on 06/13/2013 02:42:21
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/13/2013 :  06:24:01  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

do you mean this?

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
HAVING MAX(CASE WHEN Status = 'IN' THEN time END) IS NOT NULL 
AND MAX(CASE WHEN Status = 'OUT' THEN time END) IS NOT NULL 



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




yes this is working but this type of data also giving me problem


eid-------date----------------------------timein--------------------------------Timeout
26359---2013-01-04 00:00:00.000----2013-06-13 08:28:00.000--------2013-06-13 12:28:00.000
26359---2013-01-04 00:00:00.000----2013-06-13 12:32:00.000--------2013-06-13 16:11:00.000
26359---2013-01-04 00:00:00.000----2013-06-13 12:32:00.000--------2013-06-13 16:11:00.000


same employee swap card mulitiple times

more examples


eid-------date------------------------------timein-----------------------------Timeout
26359---2013-05-17 00:00:00.000----2013-06-13 15:16:00.000--------2013-06-13 18:03:00.000
26359---2013-05-17 00:00:00.000----22013-06-13 15:16:00.000--------2013-06-13 18:03:00.000
26359---2013-05-17 00:00:00.000----2013-06-13 15:16:00.000--------2013-06-13 18:12:00.000
26359---2013-05-17 00:00:00.000----2013-06-13 18:12:00.000--------2013-06-13 18:22:00.000
26359---2013-05-17 00:00:00.000----2013-06-13 08:26:00.000--------2013-06-13 15:09:00.000



i dont want to see red line data it effects employee spend time
Thanks for helping





immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/13/2013 :  06:25:50  Show Profile  Reply with Quote
use DISTINCT keyword after SELECT
SELECT DISTINCT eid, date..............

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  06:27:32  Show Profile  Reply with Quote

SELECT eid,date,timein,timeout
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY eid,date,
MAX(CASE WHEN Status = 'IN' THEN time END),
MAX(CASE WHEN Status = 'OUT' THEN time END) ORDER BY eid) AS Seq1,
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
HAVING MAX(CASE WHEN Status = 'IN' THEN time END) IS NOT NULL 
AND MAX(CASE WHEN Status = 'OUT' THEN time END) IS NOT NULL 
)t
WHERE Seq1=1


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

Edited by - visakh16 on 06/13/2013 06:28:44
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/13/2013 :  06:28:20  Show Profile  Reply with Quote
quote:
Originally posted by bandi

use DISTINCT keyword after SELECT
SELECT DISTINCT eid, date..............

--
Chandu


Thanks chandu

immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/13/2013 :  06:31:45  Show Profile  Reply with Quote
quote:
Originally posted by immad

quote:
Originally posted by bandi

use DISTINCT keyword after SELECT
SELECT DISTINCT eid, date..............

--
Chandu


Thanks chandu

immad uddin ahmed


Welcome

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  06:32:30  Show Profile  Reply with Quote
or much simpler

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,
ROW_NUMBER() OVER (PARTITION BY eid,date,status,time ORDER BY eid) AS Dup
 FROM Table)t
WHERE Dup=1
GROUP BY eid,date,Seq
HAVING MAX(CASE WHEN Status = 'IN' THEN time END) IS NOT NULL 
AND MAX(CASE WHEN Status = 'OUT' THEN time END) IS NOT NULL 


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

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/13/2013 :  06:38:16  Show Profile  Reply with Quote


Thanks visakh


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  06:57:05  Show Profile  Reply with Quote
quote:
Originally posted by immad



Thanks visakh


immad uddin ahmed


you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.12 seconds. Powered By: Snitz Forums 2000