May 20, 2012 at 11:52 pm
Card_NoCheckIn CheckOut Duration
1442012-05-17 10:01:34.0002012-05-17 10:19:57.00018
1442012-05-16 10:14:12.0002012-05-16 18:51:58.000517
1442012-05-15 11:02:12.0002012-05-15 19:09:45.000487
1442012-05-14 10:06:08.0002012-05-14 19:04:17.000538
1442012-05-12 10:01:07.0002012-05-12 16:04:04.000363
1442012-05-11 10:17:07.0002012-05-11 17:01:52.000404
1442012-05-10 13:22:35.0002012-05-10 19:04:53.000342
1442012-05-10 10:07:57.0002012-05-10 12:44:01.000157
1442012-05-09 10:15:08.0002012-05-09 19:11:44.000536
1442012-05-08 10:11:09.0002012-05-08 18:45:29.000514
I need output like value of first checkout row substract from values of second checkIn row...
can u help me ?
May 21, 2012 at 12:42 am
Edited to use sql quotes and use the test data.
drop table timing
go
create TABLE timing
(Card_No int, CheckIn DateTime, CheckOut DateTime)
go
Insert Into timing
Select 144,'2012-05-17 10:01:34.000','2012-05-17 10:19:57.000'
Union ALL Select 144,'2012-05-16 10:14:12.000','2012-05-16 18:51:58.000'
Union ALL Select 144,'2012-05-15 11:02:12.000','2012-05-15 19:09:45.000'
Union ALL Select 144,'2012-05-14 10:06:08.000','2012-05-14 19:04:17.000'
Union ALL Select 144,'2012-05-12 10:01:07.000','2012-05-12 16:04:04.000'
Union ALL Select 144,'2012-05-11 10:17:07.000','2012-05-11 17:01:52.000'
Union ALL Select 144,'2012-05-10 13:22:35.000','2012-05-10 19:04:53.000'
Union ALL Select 144,'2012-05-10 10:07:57.000','2012-05-10 12:44:01.000'
Union ALL Select 144,'2012-05-09 10:15:08.000','2012-05-09 19:11:44.000'
Union ALL Select 144,'2012-05-08 10:11:09.000','2012-05-08 18:45:29.000'
;
with mycte
as
(
select card_no,checkin,checkout,row_number() over(partition by card_no order by checkin asc) as rn
from timing
)
select cur.card_no,cur.checkin TodaysCheckInTime,cur.checkout TodaysCeckOutTime
,prev.checkout PreviousCheckoutTime
,datediff(mi,prev.checkout,cur.checkin) diff_between_TodaysCheckInTime_PreviousCheckOutTime
,datediff(mi,cur.CheckIn,cur.checkout) diff_between_TodaysCheckOutTime_PreviousCheckInTime_or_duration
from mycte cur
left join mycte prev
on cur.card_no = prev.card_no
and cur.rn = prev.rn + 1
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 3:27 am
What do you mean by "First CheckOut Row" and "Second CheckOut Row"??
From what I understood(that "First CheckOut Row" is the first row ordered by "CheckOut" and "Second CheckOut Row" is the second row ordered by "Checkin") I came up with the following query:
--Creating Table
Create Table Ex
(Card_No int,
CheckIn DateTime,
CheckOut DateTime,
Duration int)
--Inserting Sample Data
Insert Into Ex
Select 144,'2012-05-17 10:01:34.000','2012-05-17 10:19:57.000',18
Union ALL
Select 144,'2012-05-16 10:14:12.000','2012-05-16 18:51:58.000',517
Union ALL
Select 144,'2012-05-15 11:02:12.000','2012-05-15 19:09:45.000',487
Union ALL
Select 144,'2012-05-14 10:06:08.000','2012-05-14 19:04:17.000',538
Union ALL
Select 144,'2012-05-12 10:01:07.000','2012-05-12 16:04:04.000',363
Union ALL
Select 144,'2012-05-11 10:17:07.000','2012-05-11 17:01:52.000',404
Union ALL
Select 144,'2012-05-10 13:22:35.000','2012-05-10 19:04:53.000',342
Union ALL
Select 144,'2012-05-10 10:07:57.000','2012-05-10 12:44:01.000',157
Union ALL
Select 144,'2012-05-09 10:15:08.000','2012-05-09 19:11:44.000',536
Union ALL
Select 144,'2012-05-08 10:11:09.000','2012-05-08 18:45:29.000',514
--Query For your Requirement
Select
a.Card_No,
Cast(DATEDIFF(S, a.CheckIn, b.CheckIn) As Float) As C_IN_Diff_Secs,
Cast(DATEDIFF(S, a.CheckIn, b.CheckIn)/60 As Float) As C_IN_Diff_Mins,
Cast(DATEDIFF(S, a.CheckIn, b.CheckIn)/3600 As Float) As C_IN_Diff_Hours,
Cast(DATEDIFF(DD, a.CheckIn, b.CheckIn) As Float) As C_IN_Diff_Days,
Cast(DATEDIFF(S, a.CheckOut, b.CheckOut) As Float) As C_OUT_Diff_Secs,
Cast(DATEDIFF(S, a.CheckOut, b.CheckOut)/60 As Float) As C_OUT_Diff_Mins,
Cast(DATEDIFF(S, a.CheckOut, b.CheckOut)/3600 As Float) As C_OUT_Diff_Hours,
Cast(DATEDIFF(DD, a.CheckOut, b.CheckOut) As Float) As C_OUT_Diff_Days,
(a.Duration - b.Duration) As Duration_Diff
From
(Select Top 1 * From Ex
Order By CheckOut) As a
JOIN
(Select * From
(Select *, ROW_NUMBER() Over (Order By CheckIn) As rownum From Ex) As x
Where x.rownum = 2) As b
ON a.Card_No = b.Card_No
If you want the data according to the order in your sample data then you will have to change the "Order By Clauses" in the query to "Order By <ColumnName> desc".
Hope this is what you are looking for.
If not then please elaborate upon your requirement.
May 21, 2012 at 3:57 am
I think this can be simplified.
DECLARE @t TABLE
(Card_No int, CheckIn DateTime, CheckOut DateTime, Duration int)
Insert Into @t
Select 144,'2012-05-17 10:01:34.000','2012-05-17 10:19:57.000',18
Union ALL Select 144,'2012-05-16 10:14:12.000','2012-05-16 18:51:58.000',517
Union ALL Select 144,'2012-05-15 11:02:12.000','2012-05-15 19:09:45.000',487
Union ALL Select 144,'2012-05-14 10:06:08.000','2012-05-14 19:04:17.000',538
Union ALL Select 144,'2012-05-12 10:01:07.000','2012-05-12 16:04:04.000',363
Union ALL Select 144,'2012-05-11 10:17:07.000','2012-05-11 17:01:52.000',404
Union ALL Select 144,'2012-05-10 13:22:35.000','2012-05-10 19:04:53.000',342
Union ALL Select 144,'2012-05-10 10:07:57.000','2012-05-10 12:44:01.000',157
Union ALL Select 144,'2012-05-09 10:15:08.000','2012-05-09 19:11:44.000',536
Union ALL Select 144,'2012-05-08 10:11:09.000','2012-05-08 18:45:29.000',514
SELECT Card_No, CheckIn, CheckOut, Duration
,DATEDIFF(minute
,(SELECT TOP 1 Checkout
FROM @t t2
WHERE t1.card_no = t2.card_no and t1.Checkin > t2.Checkout
ORDER BY Checkin DESC), CheckIN) AS Out2In
FROM @t t1
To produce these results:
Card_NoCheckInCheckOutDurationOut2In
1442012-05-17 10:01:34.0002012-05-17 10:19:57.00018910
1442012-05-16 10:14:12.0002012-05-16 18:51:58.000517905
1442012-05-15 11:02:12.0002012-05-15 19:09:45.000487958
1442012-05-14 10:06:08.0002012-05-14 19:04:17.0005382522
1442012-05-12 10:01:07.0002012-05-12 16:04:04.0003631020
1442012-05-11 10:17:07.0002012-05-11 17:01:52.000404913
1442012-05-10 13:22:35.0002012-05-10 19:04:53.00034238
1442012-05-10 10:07:57.0002012-05-10 12:44:01.000157896
1442012-05-09 10:15:08.0002012-05-09 19:11:44.000536930
1442012-05-08 10:11:09.0002012-05-08 18:45:29.000514NULL
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2012 at 4:01 am
May 21, 2012 at 4:07 am
vinu512 (5/21/2012)
Looks good Dwain.But, I'm still unsure what the OP wants....so will say something after the OP elaborates upon the requirement a little. 🙂
Actually, not so good. Got sloppy and had to edit my proposal. Should be OK now.
Agreed that OP needs to clarify what he wants but I'm thinking it is the minutes from CheckOut to Checkin (on the next record).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2012 at 6:41 am
All of you thank you very much for reply....
May 21, 2012 at 10:16 pm
sachince61 (5/21/2012)
All of you thank you very much for reply....
You're welcome. 🙂
But, what worked??....it would be great if you could post the solution so that other people can also view it and learn from it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply