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
 Logical Problem in Sql Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 04/20/2013 :  05:02:17  Show Profile  Reply with Quote
my data is Like this

ID------CheckTime------------------Type
9000----2013-01-09 09:00:00.000-----I
9000----2013-01-09 09:01:00.000-----O
9000----2013-01-09 09:10:00.000-----O
9000----2013-01-09 10:00:00.000-----I
9000----2013-01-09 10:01:00.000-----I
9000----2013-01-09 12:00:00.000-----O
9000----2013-01-09 13:00:00.000-----I
9000----2013-01-09 13:01:00.000-----I
9000----2013-01-09 13:01:00.000-----I
9000----2013-01-09 15:00:00.000-----O
9000----2013-01-09 15:01:00.000-----O


i have Query like this

SELECT
T .ID,
DATEADD(dd, 0, DATEDIFF(dd, 0, T .CheckTime)) Date,
T .CheckTime AS TimeIn,
(SELECT TOP 1 CASE WHEN T1.Type = 'O' THEN CHECKTIME ELSE NULL END
FROM AttendLog T1
WHERE T1.ID = T .ID AND T1.CHECKTIME > T .CHECKTIME
ORDER BY CHECKTIME) AS TimeOut
FROM AttendLog T
WHERE
Type = 'I'

but getiing false data like this

ID------Date----------TimeIN----------TimeOut
9000----2013-01-09----09:00:00.000----09:01:00.000
9000----2013-01-09----10:00:00.000----NULL
9000----2013-01-09----10:01:00.000----12:00:00.000
9000----2013-01-09----13:00:00.000----NULL
9000----2013-01-09----13:01:00.000----15:00:00.000
9000----2013-01-09----13:01:00.000----15:00:00.000


My Required Data is like this

ID------Date----------TimeIN----------TimeOut-----
9000----2013-01-09----09:00:00.000----09:10:00.000
9000----2013-01-09----10:01:00.000----12:00:00.000
9000----2013-01-09----13:01:00.000----15:01:00.000

Kindly Suggest Bettter Procedure thanks in Advance....

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/20/2013 :  10:19:10  Show Profile  Reply with Quote
How about this:

Select D.ID, D.Date, D.TimeIn, D.TimeOut from 
(SELECT 
T.ID,
DATEADD(dd, 0, DATEDIFF(dd, 0, T.CheckTime)) Date,
T.CheckTime AS TimeIn,
(SELECT TOP 1 CASE WHEN T1.Type = 'O' THEN CHECKTIME ELSE NULL END
FROM AttendLog T1
WHERE T1.ID = T.ID AND T1.CHECKTIME > T .CHECKTIME
ORDER BY CHECKTIME) AS TimeOut
FROM AttendLog T
WHERE 
Type = 'I') as D Where D.TimeOut Is NOT NULL

Edited by - MuMu88 on 04/20/2013 10:21:04
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 04/22/2013 :  04:49:38  Show Profile  Reply with Quote
this particular query takes much time to execute is there any other way???

immad uddin ahmed
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
572 Posts

Posted - 04/22/2013 :  05:14:18  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
are your tables properly indexed?

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/22/2013 :  05:18:04  Show Profile  Reply with Quote

SELECT p.ID,p.[Date],p.TimeIN,
DATEADD(dd,-DATEDIFF(dd,0,q.MaxDate),q.MaxDate) AS TimeOUT
FROM
(
SELECT t.ID,DATEADD(dd,DATEDIFF(dd,0,t.CheckTime),0) AS [Date],
DATEADD(dd,-DATEDIFF(dd,0,t.CheckTime),t.CheckTime) AS [TimeIN],
MAX(CASE WHEN t1.Type='I' THEN CheckTime END) AS [NextTypeDate]
FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CheckTime ) AS Seq,ROW_NUMBER() OVER (PARTITION BY ID,Type ORDER BY CheckTime ) AS SSeq FROM Table) t
INNER JOIN (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CheckTime ) AS Seq,ROW_NUMBER() OVER (PARTITION BY ID,Type ORDER BY CheckTime ) AS SSeq FROM Table)t1
ON t1.ID = t.ID
AND ((t1.Seq = t.Seq + 1 AND t1.Type='O')
OR t1.SSeq = t.SSeq + 1)
WHERE t.Type = 'I'
GROUP BY t.ID,t.CheckTime
)p
OUTER APPLY (SELECT MAX(CheckTime) AS MaxDate
FROM Table 
WHERE ID = p.ID
AND Type = 'O'
AND CheckTime < p.NextTypeDate
)q


------------------------------------------------------------------------------------------------------
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 - 04/22/2013 :  05:19:20  Show Profile  Reply with Quote
i dispensed with usage of CTEs in above solution as OP seems to be looking for an inline suggestion as per here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184712

------------------------------------------------------------------------------------------------------
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 - 04/23/2013 :  00:47:40  Show Profile  Reply with Quote
YES Our table is properly index

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/23/2013 :  12:44:04  Show Profile  Reply with Quote
quote:
Originally posted by immad

YES Our table is properly index

immad uddin ahmed


how did you determine that?

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

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 05/21/2013 :  08:28:20  Show Profile  Reply with Quote
thanks all of you for your Interest.
can my requirement be fullfill while using Pivot Table if yes than what will be the query because all suggested Queries are taking very much time
Thanks in Advance
Regards
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/22/2013 :  00:33:24  Show Profile  Reply with Quote
Pivot? you dont need pivot here

Can you see execution plan and let us know the costly steps?

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

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 05/23/2013 :  01:40:31  Show Profile  Reply with Quote
Thanks Visakh dear last pattern of Query is taking much time to execute including Groupby kindly help its urgent

select
a.CID,
a.BID,
a.Eid,
CONVERT (Datetime,Convert (varchar(10),a.itime,120)) AS Date,
a.itime TimeIN,
a.IAttendID,
case when max(b.checktime)>a.itime
and convert (VARCHAR(10),a.ITime,120) = convert (VARCHAR(10),MAX(b.checktime),120)
then max(b.checktime) else null end as TimeOut,

a.OAttendID,
a.Remarks,
a.Comments,
a.InBetween
from

(
select
a.CID,
a.BID,
a.Eid,
min(a.itime) as itime,
IAttendID,
a.otime,
OAttendID,
min(b.checktime) as itime2,
a.Remarks,
a.Comments,
a.InBetween
from
(
select
a.CID,
a.BID,
a.Eid,
a.checktime as itime,
a.AttendID IAttendID,
min(b.checktime) as otime,
b.AttendID OAttendID,
a.Remarks,
a.Comments,
a.InBetween
from
TABLE as a
left outer join TABLE as b
on b.Eid=a.Eid
and b.checktime>a.checktime
and b.Status='O'
and a.bid = b.bid
AND convert (VARCHAR(10),a.CHECKTIME,120) = convert (VARCHAR(10),b.CHECKTIME,120)
where a.Status='I'
group by
a.CID,
a.BID,
a.Eid,
a.AttendID,
b.AttendID,
a.checktime,
a.Remarks,
a.Comments,
a.InBetween
) as a
left outer join TABLE as b
on b.Eid=a.Eid
and b.checktime>a.otime
and b.Status='I'
and a.bid = b.bid
AND convert (VARCHAR(10),b.CHECKTIME,120) = convert (VARCHAR(10),a.ITIME,120)

group by
a.CID,
a.BID,
a.Eid,
IAttendID,
OAttendID,
otime,
a.Remarks,
a.Comments,
a.InBetween
) as a

left outer join TABLE as b
on a.Eid=b.Eid
and b.Status='O'
AND convert (VARCHAR(10),a.ITime,120) = convert (VARCHAR(10),b.CheckTime,120)
AND isnull(a.itime2,'21991231') > b.checktime
and a.bid = b.bid
and a.IAttendID = a.OAttendID
Where
b.checktime is not NULL

group by
a.CID,
a.BID,
a.Eid,
a.itime,
a.IAttendID,
a.OAttendID,
a.Remarks,
a.Comments,
a.InBetween


Edited by - asif372 on 05/23/2013 01:42:40
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/23/2013 :  01:42:58  Show Profile  Reply with Quote
this doesnt help as its not even close to my earlier suggestion.
You have to explain us what you're trying to achieve here by giving some sample data and expected output from it.

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