A good example of OVER(PARTITION BY

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)

A good example of OVER(PARTITION BY

Author

Topic

ElenaSTL
Starting Member

10 Posts

Posted - 2014-10-24 : 11:26:23

I published a solution for a task where we should compare a value from one record with a value from the next record.
I made it using loop and did it only for one machine with some actual data provided by simsekm. Here is the topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=196753
To solve this task for several machines we need to create an outer loop for each machine.
My friend S.T. made a better code with no loops and using OVER(PARTITION BY and ROW_NUMBER() function.
For almost 20 years of developing reports and SQL for them I never met the situation when I should compare consecutive records and maybe this was the reason why I did not use partition. Another reason why I did not use partition was that I did not meet a good example of using it, but only confusing explanations like this:
PARTITION BY is analytic, while GROUP BY is aggregate. In order to use PARTITION BY, you have to contain int with an OVER clause.
Now I will show the good example made by S.T. so all SQL developers (including my daughter Olga, who is also SQL/Reports developer) can keep it in mind.
--Create original test table
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test

CREATE TABLE #Test
(IdKey Int IDENTITY(1,1) NOT NULL,
ProdDate Datetime NOT NULL,
MachineId Int NOT NULL
)
--Populate original test table
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:46:49.777', 111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:46:50.830', 111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:46:51.883', 111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:46:58.797', 111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:47:04.693', 111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:47:14.593', 111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:47:27.393',111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:48:56.163',111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:49:09.564',111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:49:12.414',111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:50:45.163',111)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:01:21', 222)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:01:59', 222)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:03:00', 222)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:03:44', 222)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:03:59', 222)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:04:40', 222)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:10:10', 333)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:11:09', 333)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:12:30', 333)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:12:55', 333)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:13:10', 333)
INSERT INTO #Test (ProdDate, MachineId) VALUES ('2014-10-22 13:14:20', 333)

--Testing test table
--SELECT * FROM #Test ORDER BY MachineId, ProdDate

--Declare from/to parameters
DECLARE @_From Datetime = '2014-10-22 13:00:00',
@_To Datetime = '2014-10-22 14:00:00'

--Create working table
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp
(RowN Int NOT NULL,
MachineId Int NOT NULL,
ProdDate Datetime NULL,
NextProdDate datetime NULL
)

--Populate working table

INSERT INTO #Temp
(RowN,
MachineId,
ProdDate,
NextProdDate
)

SELECT ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY ProdDate),
MachineId,
ProdDate,
NULL
FROM #Test

--Lookup for next machine's run time
UPDATE T
SET T.NextProdDate = TT.ProdDate
FROM #Temp T INNER JOIN #Temp TT ON T.RowN = TT.RowN - 1
AND T.MachineId = TT.MachineId

--Testing working table
--SELECT * FROM #Temp

--Final SELECT
SELECT MachineId,
SUM(CASE WHEN DATEDIFF(SECOND, ProdDate, NextProdDate) < 60
THEN DATEDIFF(SECOND, ProdDate, NextProdDate)
ELSE 0 END) RunningTimeInSec,
SUM(CASE WHEN DATEDIFF(SECOND, ProdDate, NextProdDate) >= 60
THEN 1 ELSE 0 END)NumberOfStops
FROM #Temp
WHERE ProdDate BETWEEN @_From AND @_To
GROUP BY MachineId
ORDER BY MachineId

--Drop temp tables
DROP TABLE #Test
DROP TABLE #Temp

simsekm
Starting Member

20 Posts

Posted - 2014-10-28 : 04:39:57

Hi,

This solution is better. Thanks for your great effort. I hope this query will help to everybody who needs a solution such "islands and gaps"

Regards

simsekm
Starting Member

20 Posts

Posted - 2014-11-26 : 09:32:13

Hi again,

I need an answer. I want to set RunningTimeInSec value as "10" if there are 2 zero RunningTimeInSec value consecutively. So i need to detect 2 zero value of RunningTimeInSec. How can i do that?

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources