DateDiff Query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

DateDiff Query

Author

Topic

simsekm
Starting Member

20 Posts

Posted - 2014-10-15 : 12:14:00

Hi Guys,

I have a such table :

ID Prod_Date Machine_ID

ID coulmn is int (clustred Index), Prod_Date is datetime And Machine_ID is int

This table will used to calculate running time of machines(as minute). Prod_Date shows date/time when a product pass front of sensor. For example i will want to calculate total running time between 13:00 PM and 14:00 PM. If there is more than 1 minute between 2 rows then i will accept that machine stoped. Vice versa i will accept that machine is running.

1-) I need query which will do this job
2-) As you guess, table will be very large . So i need query tips for performance (fast transaction)

Regards

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-15 : 12:59:44

The biggest performance gain will come from clustering the table by ( Prod_Date, ID ) not by just ID.

There are several ways to write the query itself. Any reasonable method should perform reasonably well once the table is properly clustered. (Itzik Ben-Gan's "gaps and islands" method would probably be the fastest query, but it's not necessarily the easiest to understand.)

simsekm
Starting Member

20 Posts

Posted - 2014-10-15 : 13:55:11

quote:
The biggest performance gain will come from clustering the table by ( Prod_Date, ID ) not by just ID.

There are several ways to write the query itself. Any reasonable method should perform reasonably well once the table is properly clustered. (Itzik Ben-Gan's "gaps and islands" method would probably be the fastest query, but it's not necessarily the easiest to understand.)

But there are many machines and there can be same value for Prod_Date column. And as I know clustered index should be unique column. Am not i right?Or may be if I set default value of Prod_Date as getdate() then this column will have unique date/time values?

I will search "gaps and islands" method. And are there any advices for query?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-15 : 15:01:08

quote:
Originally posted by simsekm

quote:
The biggest performance gain will come from clustering the table by ( Prod_Date, ID ) not by just ID.

There are several ways to write the query itself. Any reasonable method should perform reasonably well once the table is properly clustered. (Itzik Ben-Gan's "gaps and islands" method would probably be the fastest query, but it's not necessarily the easiest to understand.)

But there are many machines and there can be same value for Prod_Date column. And as I know clustered index should be unique column. Am not i right?Or may be if I set default value of Prod_Date as getdate() then this column will have unique date/time values?

I will search "gaps and islands" method. And are there any advices for query?

I clustered by both Prod_Date and ID to make it unique. But technically you could just cluster on Prod_Date; SQL will add a value to make the key value unique if it needs to.

If you'll provide INSERT statements to create sample/test data, I'll write a query.

simsekm
Starting Member

20 Posts

Posted - 2014-10-15 : 15:15:03

My query is pretty simple :

Insert into Production(Prod_Date,Machine_Id) Values(Date_Val,Mach_ID)

So my table name is Production. Should I change my insert query too? Because I know that Insert and Update statements cause of index fragmentation. And if there is a better way for insert statement then i will use it with pleasure....

simsekm
Starting Member

20 Posts

Posted - 2014-10-15 : 16:38:45

quote:
Originally posted by ScottPletcher

I clustered by both Prod_Date and ID to make it unique. But technically you could just cluster on Prod_Date; SQL will add a value to make the key value unique if it needs to.

If you'll provide INSERT statements to create sample/test data, I'll write a query.

Did i miss anything at your question?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-16 : 14:12:54

I need a table definition, actual data rows, and the expected result.

Insert into Production(Prod_Date,Machine_Id) Values('20140603 18:42',1)
Insert into Production(Prod_Date,Machine_Id) Values('20140603 18:44',1)
Insert into Production(Prod_Date,Machine_Id) Values('20140603 18:47',1)
Insert into Production(Prod_Date,Machine_Id) Values('20140603 18:41',2)
Insert into Production(Prod_Date,Machine_Id) Values('20140603 18:45',2)
...

simsekm
Starting Member

20 Posts

Posted - 2014-10-17 : 02:35:43

Here is table definition : (Yes, very small table and no more column)

ID int Unchecked
Machine_ID int Unchecked
Prod_Date datetime Unchecked (defualt value of this coulmn is getdate())

And Here is my real query :

INSERT INTO Production (Machine_ID) Values(7)
INSERT INTO Production (Machine_ID) Values(1)
INSERT INTO Production (Machine_ID) Values(1)
INSERT INTO Production (Machine_ID) Values(2)
INSERT INTO Production (Machine_ID) Values(3)
INSERT INTO Production (Machine_ID) Values(3)
INSERT INTO Production (Machine_ID) Values(7)
INSERT INTO Production (Machine_ID) Values(7)
INSERT INTO Production (Machine_ID) Values(1)
INSERT INTO Production (Machine_ID) Values(2)
INSERT INTO Production (Machine_ID) Values(7)

And here is some actual data rows :

ID Machine_ID Prod_Date
2004 7 2014-10-10 14:46:49.777
2005 1 2014-10-10 14:46:50.830
2006 1 2014-10-10 14:46:51.883
2007 2 2014-10-10 14:46:52.797
2008 3 2014-10-10 14:46:53.693
2009 3 2014-10-10 14:46:54.593
2010 3 2014-10-10 14:46:55.393
2011 7 2014-10-10 14:46:56.163
2012 7 2014-10-10 14:46:57.120
2013 2 2014-10-10 14:46:57.860
2014 2 2014-10-10 14:50:50.130
2015 2 2014-10-10 14:50:50.997
2016 3 2014-10-10 14:50:51.770
2017 7 2014-10-10 14:50:52.917
2018 7 2014-10-10 14:50:53.683
2019 7 2014-10-10 14:50:54.723
2020 1 2014-10-10 14:50:55.730
2021 1 2014-10-10 14:50:56.747
2022 1 2014-10-10 14:50:57.857
2023 1 2014-10-10 14:51:00.990
2024 1 2014-10-10 14:51:04.117
2025 1 2014-10-10 14:51:06.510
2026 4 2014-10-10 14:51:09.330
2027 7 2014-10-10 14:51:13.037
2028 7 2014-10-10 14:52:14.993

Expected result is calculating running total time of a machine (for example 4 or 7 or etc.) Criteria is 40 seconds. If there are more than 40 secenods between 2 consecutive rows then i will assume that machine is not running.

Let me show it with an example :

2014-10-10 14:46:49.777 'Machine started to work
2014-10-10 14:46:50.830 'Machine is running (Difference is nearly 1 second)
2014-10-10 14:46:51.883 'Machine is running (Difference is nearly 1 second)
2014-10-10 14:46:58.797 'Machine is running (Difference is nearly 7 seconds)
2014-10-10 14:47:04.693 'Machine is running (Difference is nearly 6 seconds)
2014-10-10 14:47:14.593 'Machine is running (Difference is nearly 10 seconds)
2014-10-10 14:47:27.393 'Machine is running (Difference is nearly 13 seconds)
2014-10-10 14:48:56.163 'Machine seems that has a break nearly 89 seconds
2014-10-10 14:49:09.564 'Machine is running (Difference is nearly 13 seconds)
2014-10-10 14:49:12.414 'Machine is running (Difference is nearly 3 second)
2014-10-10 14:50:45.163 'Machine seems that has a break nearly 93 seconds

So, machine started to run at 2014-10-10 14:46:49 and last run time was 2014-10-10 14:47:27 . And it starts again at 2014-10-10 14:48:56 and last running time was 2014-10-10 14:49:12

First part of total running time of this machine is 38 seconds. And second part of total running time is 16 seconds. Total running time is between 2014-10-10 14:00:00 and 15:00:00 is 38 sec + 16 sec = 54 seconds.

There are any unclear point?

simsekm
Starting Member

20 Posts

Posted - 2014-10-18 : 16:13:03

@Scott,

Can you help to me?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-20 : 12:09:37

I'm not trying to be difficult, but I simply don't have time to convert plain text data:
"
ID Machine_ID Prod_Date
2004 7 2014-10-10 14:46:49.777
2005 1 2014-10-10 14:46:50.830
2006 1 2014-10-10 14:46:51.883
"
into actual INSERT statements. That often takes almost as much time as writing the SQL itself.

ElenaSTL
Starting Member

10 Posts

Posted - 2014-10-20 : 16:39:47

declare @Table1 table (RunTime datetime,
OperationStatus varchar(10))

insert into @Table1 values ('2014-10-10 14:46:49.777','start'),
('2014-10-10 14:46:50.830','unknown'),
('2014-10-10 14:46:51.883','unknown'),
('2014-10-10 14:46:58.797','unknown'),
('2014-10-10 14:47:04.693','unknown'),
('2014-10-10 14:47:14.593','unknown'),
('2014-10-10 14:47:27.393','unknown'),
('2014-10-10 14:48:56.163','unknown'),
('2014-10-10 14:49:09.564','unknown'),
('2014-10-10 14:49:12.414','unknown'),
('2014-10-10 14:50:45.163','unknown')

select * from @Table1

declare @Table2 table (RunTime datetime,
OperationStatus varchar(10),
OperatingTime int)

declare @AtDateTime datetime
declare @NextDateTime datetime

set @AtDateTime = (select top 1 RunTime from @Table1)

insert into @Table2
select RunTime, OperationStatus, 0 from @Table1 where RunTime = @AtDateTime

set @AtDateTime = '1999-10-10 14:46:49.777'

WHILE (not (@AtDateTime is null))
BEGIN

set @AtDateTime = (select top 1 RunTime from @Table1)

delete from @Table1 where RunTime = @AtDateTime

set @NextDateTime = (select top 1 RunTime from @Table1)

if DATEDIFF(second,@AtDateTime,@NextDateTime) < 40
begin
insert into @Table2 values (@NextDateTime,'running',DATEDIFF(second,@AtDateTime,@NextDateTime))
end
else
begin
insert into @Table2 values (@NextDateTime,'stop',0)
end
END

select * from @Table2

select SUM(OperatingTime) from @Table2
where RunTime between '2014-10-10 14:00:00' and '2014-10-10 15:00:00'

simsekm
Starting Member

20 Posts

Posted - 2014-10-21 : 08:37:08

quote:
Originally posted by ElenaSTL

declare @Table1 table (RunTime datetime,
OperationStatus varchar(10))

insert into @Table1 values ('2014-10-10 14:46:49.777','start'),
('2014-10-10 14:46:50.830','unknown'),
('2014-10-10 14:46:51.883','unknown'),
('2014-10-10 14:46:58.797','unknown'),
('2014-10-10 14:47:04.693','unknown'),
('2014-10-10 14:47:14.593','unknown'),
('2014-10-10 14:47:27.393','unknown'),
('2014-10-10 14:48:56.163','unknown'),
('2014-10-10 14:49:09.564','unknown'),
('2014-10-10 14:49:12.414','unknown'),
('2014-10-10 14:50:45.163','unknown')

select * from @Table1

declare @Table2 table (RunTime datetime,
OperationStatus varchar(10),
OperatingTime int)

declare @AtDateTime datetime
declare @NextDateTime datetime

set @AtDateTime = (select top 1 RunTime from @Table1)

insert into @Table2
select RunTime, OperationStatus, 0 from @Table1 where RunTime = @AtDateTime

set @AtDateTime = '1999-10-10 14:46:49.777'

WHILE (not (@AtDateTime is null))
BEGIN

set @AtDateTime = (select top 1 RunTime from @Table1)

delete from @Table1 where RunTime = @AtDateTime

set @NextDateTime = (select top 1 RunTime from @Table1)

if DATEDIFF(second,@AtDateTime,@NextDateTime) < 40
begin
insert into @Table2 values (@NextDateTime,'running',DATEDIFF(second,@AtDateTime,@NextDateTime))
end
else
begin
insert into @Table2 values (@NextDateTime,'stop',0)
end
END

select * from @Table2

select SUM(OperatingTime) from @Table2
where RunTime between '2014-10-10 14:00:00' and '2014-10-10 15:00:00'

@ElenaSTL,

Thanks for this great solution. You're life saver.

@ScottPletcher,

Thanks budy. Your help was also great.

I'm grateful to this forum.

Regards.

ElenaSTL
Starting Member

10 Posts

Posted - 2014-10-24 : 11:28:02

a better code for this task in this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=196895

made by my friend S.T.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources