Logical Problem in Sql Server 2005

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

Logical Problem in Sql Server 2005

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2013-04-20 : 05:02:17

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

549 Posts

Posted - 2013-04-20 : 10:19:10

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

immad
Posting Yak Master

230 Posts

Posted - 2013-04-22 : 04:49:38

this particular query takes much time to execute is there any other way???

immad uddin ahmed

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-04-22 : 05:14:18

are your tables properly indexed?

mohammad.javeed.ahmed@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 05:18:04

[code]
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
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 05:19:20

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

immad
Posting Yak Master

230 Posts

Posted - 2013-04-23 : 00:47:40

YES Our table is properly index

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-23 : 12:44:04

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

asif372
Posting Yak Master

100 Posts

Posted - 2013-05-21 : 08:28:20

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 00:33:24

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

asif372
Posting Yak Master

100 Posts

Posted - 2013-05-23 : 01:40:31

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 01:42:58

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources