Interval Time between Two Datetime parameter

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

Interval Time between Two Datetime parameter

Author

Topic

belkin_99
Starting Member

27 Posts

Posted - 2008-09-16 : 10:18:21

I have two Tables:

X with (Index char (25), Names varchar (120))
300 Rows Included Names with Null Values.

Y with (Index char (25),Location varchar (120),LogTime datetime)
800 Rows Included Location with Null Values .
where Each row from X.Index has Four rows from Y.LogTime

I created third Temp Table

Create Table #TempThird (Index char (25), Names varchar (120),
Location varchar (120), IntervalTime1 datetime, IntervalTime2 datetime)

Insert into #TempThird

Select
Index, Names , ???Q1 First Difference ???,???Q1 Second Difference???

From X
Inner Join Y
ON X.Index = Y.Index

My Questions are:

Q1: What I should put Or How to get
The First Difference which is : Y.LogTime (row3)- Y.LogTime (Row1)
The Second Difference which is : Y.LogTime (row4)- Y.LogTime (Row1)
Both on Y.Index = X.Index

Q2: I noticed when I run the insert I did not get Rows with the Null values Is this Right?

Any Help will be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 10:28:53

May be this:-

INSERT into #TempThird
Select 
X.Index, X.Names , 
DATEDIFF(ss,MAX(CASE WHEN r.Seq=3 THEN r.LogTime ELSE NULL END),MAX(CASE WHEN r.Seq=1 THEN r.LogTime ELSE NULL END)) AS Diff1,
DATEDIFF(ss,MAX(CASE WHEN r.Seq=4 THEN r.LogTime ELSE NULL END),MAX(CASE WHEN r.Seq=1 THEN r.LogTime ELSE NULL END)) AS Diff2
From X
Inner Join 
(select (select count(*) from Y WHERE Index=t.Index AND LogTime<t.LogTime)+1 AS Seq,* FROM  Y t)r
ON X.Index = r.Index
GROUP BY X.Index,X.Names

belkin_99
Starting Member

27 Posts

Posted - 2008-09-18 : 21:25:13

Sir,
I really appreciate your help but, as I understood you create another table cable called r which have ( Index, Seq, LogTime)
but how you fill this table with the information
This step honestly I am confused about it.

(select (select count(*) from Y WHERE Index=t.Index AND LogTime<t.LogTime)+1 AS Seq,* FROM Y t)r
ON X.Index = r.Index

What is the t?

Please can you explain in details?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 23:57:09

quote:
Originally posted by belkin_99

Sir,
I really appreciate your help but, as I understood you create another table cable called r which have ( Index, Seq, LogTime)
but how you fill this table with the information
This step honestly I am confused about it.

(select (select count(*) from Y WHERE Index=t.Index AND LogTime<t.LogTime)+1 AS Seq,* FROM Y t)r
ON X.Index = r.Index

What is the t?

Please can you explain in details?

t is alias given for table Y. as i'm using it twice inside r i want distinguish them both. thats the purpose of t.
r is called a derived table. it will be populated on the fily on execution of the query.

belkin_99
Starting Member

27 Posts

Posted - 2008-09-21 : 19:17:41

Sir,
I am sorry to say I am still stuck with it.

Table1: X
In_Nr Type TimeLog
------ ---- --------
22 In 09/21/2008 12:00:20 PM
22 out 09/21/2008 1:20:22 PM
22 si 09/21/2008 12:10:13 PM
44 In 09/22/2008 2:00:20 PM
44 Out 09/22/2008 2:40:20 PM

Table2: Y
In_Nr Name
------ ----
22 TTT
44 ZZZ
55 VVV

the output table should be:

Table output:
In_Nr Name TimelogIN TimeLogSi TimeLogOUT Interval1 Interval2
----- ---- ------ --------- --------- -------- --------
22 TTT 12:00:20PM 12:10:13PM 1:20:22 PM out-in out-si

44 zzz 2:00:20PM NULL 2:40:20PM out-in NULL

I know it is seem easy but I am unable to implement that

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 03:47:09

quote:
Originally posted by belkin_99

Sir,
I am sorry to say I am still stuck with it.

Table1: X
In_Nr Type TimeLog
------ ---- --------
22 In 09/21/2008 12:00:20 PM
22 out 09/21/2008 1:20:22 PM
22 si 09/21/2008 12:10:13 PM
44 In 09/22/2008 2:00:20 PM
44 Out 09/22/2008 2:40:20 PM

Table2: Y
In_Nr Name
------ ----
22 TTT
44 ZZZ
55 VVV

the output table should be:

Table output:
In_Nr Name TimelogIN TimeLogSi TimeLogOUT Interval1 Interval2
----- ---- ------ --------- --------- -------- --------
22 TTT 12:00:20PM 12:10:13PM 1:20:22 PM out-in out-si

44 zzz 2:00:20PM NULL 2:40:20PM out-in NULL

I know it is seem easy but I am unable to implement that

Thanks

SELECT y.In_Nr,y.Name,
MAX(CASE WHEN x.Type ='In' THEN x.TimeLog ELSE NULL END) AS TimelogIN,
MAX(CASE WHEN x.Type ='Si' THEN x.TimeLog ELSE NULL END) AS TimelogSI,
MAX(CASE WHEN x.Type ='Out' THEN x.TimeLog ELSE NULL END) AS TimelogOUT, 
CASE WHEN SUM(CASE WHEN x.Type ='In' OR x.Type ='Out'THEN 1 ELSE 0 END) >1 THEN 'out-in' ELSE NULL END AS Interval1, 
CASE WHEN SUM(CASE WHEN x.Type ='si' OR x.Type ='Out'THEN 1 ELSE 0 END) >1 THEN 'out-si' ELSE NULL END AS Interval2 
FROM Y y
INNER JOIN
(SELECT (SELECT COUNT(*) FROM X WHERE In_Nr=t.In_Nr AND TimeLog <t.TimeLog) + 1 AS Seq,*
FROM X t) tmp
ON tmp.In_Nr=y.In_Nr

belkin_99
Starting Member

27 Posts

Posted - 2008-09-23 : 00:03:01

Sir,

I implemented your code , and I get the following
1- Skip for the first field of the first row.
2- duplicate all row data of first In_Nr (row), to the next In_Nr, which different in_Nr

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 00:11:31

quote:
Originally posted by belkin_99

Sir,

I implemented your code , and I get the following
1- Skip for the first field of the first row.
2- duplicate all row data of first In_Nr (row), to the next In_Nr, which different in_Nr

Thanks for your help

i missed a group by

SELECT y.In_Nr,y.Name,
MAX(CASE WHEN x.Type ='In' THEN x.TimeLog ELSE NULL END) AS TimelogIN,
MAX(CASE WHEN x.Type ='Si' THEN x.TimeLog ELSE NULL END) AS TimelogSI,
MAX(CASE WHEN x.Type ='Out' THEN x.TimeLog ELSE NULL END) AS TimelogOUT, 
CASE WHEN SUM(CASE WHEN x.Type ='In' OR x.Type ='Out'THEN 1 ELSE 0 END) >1 THEN 'out-in' ELSE NULL END AS Interval1, 
CASE WHEN SUM(CASE WHEN x.Type ='si' OR x.Type ='Out'THEN 1 ELSE 0 END) >1 THEN 'out-si' ELSE NULL END AS Interval2 
FROM Y y
INNER JOIN
(SELECT (SELECT COUNT(*) FROM X WHERE In_Nr=t.In_Nr AND TimeLog <t.TimeLog) + 1 AS Seq,*
FROM X t) tmp
ON tmp.In_Nr=y.In_Nr
GROUP BY y.In_Nr,y.Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources