Help with Grouping Dates in Range

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 2012 Forums

Transact-SQL (2012)

Help with Grouping Dates in Range

Author

Topic

sigreesql
Starting Member

6 Posts

Posted - 2014-06-11 : 04:37:35

Have been tearing my hair out for a day trying to work this one out, have tried grouping on dates and minuted intervals without success.

I've got a table with CompanyIDs and a Datinserted field where they perform a lookup on our system. I need to group the DatInserted on set minute intervals as if they perform more than one look up in a set minute interval they only get charged once.

So for example based on a 5 minute period from this table:

intCompanyID DatInserted
365 2014-04-25 11:12:01.940
365 2014-04-25 11:16:50.960
365 2014-06-03 11:38:40.187
365 2014-06-03 11:38:41.320
365 2014-06-03 15:49:00.223
365 2014-06-04 08:57:59.627
365 2014-06-04 08:58:20.417
365 2014-06-04 09:09:23.103
365 2014-06-04 09:10:39.967
365 2014-06-05 11:30:21.103
365 2014-06-05 11:31:06.803
365 2014-06-05 11:31:10.187

I need the following groups:

intCompanyID DatInserted Group
365 2014-04-25 11:12:01.940 1
365 2014-04-25 11:16:50.960 1
365 2014-06-03 11:38:40.187 2
365 2014-06-03 11:38:41.320 2
365 2014-06-03 15:49:00.223 3
365 2014-06-04 08:57:59.627 4
365 2014-06-04 08:58:20.417 4
365 2014-06-04 09:09:23.103 5
365 2014-06-04 09:10:39.967 5
365 2014-06-05 11:30:21.103 6
365 2014-06-05 11:31:06.803 6
365 2014-06-05 11:31:10.187 6

I want to avoid using a loop if possible.

Many thanks in advance for any help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-11 : 07:05:04

[code]-- Sample data
DECLARE @Sample TABLE
(
CompanyID SMALLINT NOT NULL,
Inserted DATETIME NOT NULL
);

INSERT @Sample
(
CompanyID,
Inserted
)
VALUES (365, '20140425 11:12:01.940'),
(365, '20140425 11:16:50.960'),
(365, '20140603 11:38:40.187'),
(365, '20140603 11:38:41.320'),
(365, '20140603 15:49:00.223'),
(365, '20140604 08:57:59.627'),
(365, '20140604 08:58:20.417'),
(365, '20140604 09:09:23.103'),
(365, '20140604 09:10:39.967'),
(365, '20140605 11:30:21.103'),
(365, '20140605 11:31:06.803'),
(365, '20140605 11:31:10.187');

-- Solution by SwePeso
WITH cteSource(CompanyID, Inserted, seq)
AS (
SELECT CompanyID,
Inserted,
CASE
WHEN LAG(Inserted, 1, '19000101') OVER (PARTITION BY CompanyID ORDER BY Inserted) < DATEADD(MINUTE, -5, Inserted) THEN 1
ELSE 0
END AS seq
FROM @Sample
)
SELECT CompanyID,
Inserted,
SUM(seq) OVER (PARTITION BY CompanyID ORDER BY Inserted ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Group]
FROM cteSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigreesql
Starting Member

6 Posts

Posted - 2014-06-11 : 09:02:20

Thank so much just what I needed - hadn't come across LAG before!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-11 : 13:09:27

No problem.
That's the beauty of educating yourself and keeping yourself up to date with each new release of SQL Server.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigreesql
Starting Member

6 Posts

Posted - 2014-06-12 : 06:15:44

This worked ok on a sample of test data but when I have a larger data set it doesn't seem to be working correctly, this is what I get - a different group should be assigned at 2014-05-01 00:05:25.140

intCompanyID DatInserted Group
365 2014-05-01 00:00:37.797 1
365 2014-05-01 00:01:18.827 1
365 2014-05-01 00:01:59.907 1
365 2014-05-01 00:02:40.723 1
365 2014-05-01 00:03:21.603 1
365 2014-05-01 00:04:02.360 1
365 2014-05-01 00:04:43.550 1
365 2014-05-01 00:05:25.140 1
365 2014-05-01 00:06:06.150 1
365 2014-05-01 00:06:47.197 1
365 2014-05-01 00:07:29.247 1
365 2014-05-01 00:08:09.837 1
365 2014-05-01 00:08:50.963 1
365 2014-05-01 00:09:31.807 1
365 2014-05-01 00:10:13.307 1
365 2014-05-01 00:10:54.193 1
365 2014-05-01 00:11:35.600 1
365 2014-05-01 00:12:16.780 1
365 2014-05-01 00:12:57.633 1

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-12 : 07:20:41

[code]
;with cTE
AS
( select 365 CompanyID,CAST('2014-05-01 00:00:37.797' as DATETIME) as Inserted UNION ALL
select 365, CAST('2014-05-01 00:01:18.827' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:01:59.907' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:02:40.723' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:03:21.603' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:04:02.360' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:04:43.550' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:05:25.140' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:06:06.150' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:06:47.197' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:07:29.247' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:08:09.837' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:08:50.963' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:09:31.807' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:10:13.307' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:10:54.193' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:11:35.600' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:12:16.780' as DATETIME) UNION ALL
select 365, CAST('2014-05-01 00:12:57.633' as DATETIME) )
,
aCTE
AS (
select
CompanyID
,Inserted
,CONVERT(VARCHAR(30) , DATEADD(ss,-DATEPART(ss,Inserted)%60, DATEADD(mI,-DatePart (MI,Inserted)%5,Inserted)) ,120) InsertedRef
from CTE)

select
CompanyID
,Inserted
,Dense_Rank() OVER(Partition by CompanyID Order by InsertedRef) as Groups
from
aCTE
Order by Inserted
[/code]

[code]
CompanyID Inserted Groups
365 2014-05-01 00:00:37.797 1
365 2014-05-01 00:01:18.827 1
365 2014-05-01 00:01:59.907 1
365 2014-05-01 00:02:40.723 1
365 2014-05-01 00:03:21.603 1
365 2014-05-01 00:04:02.360 1
365 2014-05-01 00:04:43.550 1
365 2014-05-01 00:05:25.140 2
365 2014-05-01 00:06:06.150 2
365 2014-05-01 00:06:47.197 2
365 2014-05-01 00:07:29.247 2
365 2014-05-01 00:08:09.837 2
365 2014-05-01 00:08:50.963 2
365 2014-05-01 00:09:31.807 2
365 2014-05-01 00:10:13.307 3
365 2014-05-01 00:10:54.193 3
365 2014-05-01 00:11:35.600 3
365 2014-05-01 00:12:16.780 3
365 2014-05-01 00:12:57.633 3
[/code]

sabinWeb MCP

sigreesql
Starting Member

6 Posts

Posted - 2014-06-12 : 07:46:34

Thanks for the reply that's close but as the groupings on 5 min intervals it won't work for say the following:

datInserted Groups InsertedRef
2014-05-27 15:09:02.567 4 2014-05-27 15:05:00
2014-05-27 15:11:36.860 5 2014-05-27 15:10:00

I need to group on the start point of 15:09:02.567 and any records that fall within 5 minutes of that get the same group, then the next group will be within 5 mins of the next record:

datInserted Groups
2014-05-27 15:09:02.567 4
2014-05-27 15:11:36.860 4

Hope that makes sense!

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-12 : 08:08:43

It is easy if we loop


IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
	DROP TABLE #t1
GO
CREATE TABLE #test(intCompanyID int,DatInserted datetime)
INSERT INTO #test
SELECT 365 ,'2014-04-25 11:12:01.940' UNION ALL
SELECT 365 ,'2014-04-25 11:16:50.960' UNION ALL
SELECT 365 ,'2014-06-03 11:38:40.187' UNION ALL
SELECT 365 ,'2014-06-03 11:38:41.320' UNION ALL
SELECT 365 ,'2014-06-03 15:49:00.223' UNION ALL
SELECT 365 ,'2014-06-04 08:57:59.627' UNION ALL
SELECT 365 ,'2014-06-04 08:58:20.417' UNION ALL
SELECT 365 ,'2014-06-04 09:09:23.103' UNION ALL
SELECT 365 ,'2014-06-04 09:10:39.967' UNION ALL
SELECT 365 ,'2014-06-05 11:30:21.103' UNION ALL
SELECT 365 ,'2014-06-05 11:31:06.803' UNION ALL
SELECT 365 ,'2014-06-05 11:31:10.187'

SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1))AS'ID',intCompanyID,DatInserted INTO #t1 FROM #test
DECLARE @Id int =1
DECLARE @C int = 1
WHILE (@Id <=(SELECT COUNT(*) FROM #test ))
BEGIN
DECLARE @s datetime = (SELECT DatInserted FROM #t1 WHERE ID = @Id)
DECLARE @String varchar(100) = (SELECT (stuff((SELECT ','+CONVERT(varchar(30),DatInserted,121)  FROM #t1 WHERE ABS (DATEDIFF(MINUTE,@s,DatInserted) ) BETWEEN 0 AND 5 FOR XML PATH('')),1,1,'')) )
DECLARE @table TABLE(intCompanyID INT,DatInserted datetime,Groups INT)
INSERT INTO @table SELECT intCompanyID,DatInserted,@C FROM  #t1 WHERE PATINDEX('%,' + CONVERT(varchar(30),DatInserted,121)+',%', ',' + @String + ',') > 0 
DELETE FROM #t1 WHERE PATINDEX('%,' + CONVERT(varchar(30),DatInserted,121)+',%', ',' + @String + ',') > 0 
IF @@rowcount<> 0
BEGIN
SET @C  = @C  + 1
END
SET @Id = @Id + 1
END
SELECT * FROM @table

DROP TABLE #test

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

sigreesql
Starting Member

6 Posts

Posted - 2014-06-12 : 08:44:53

Thank you but am trying to avoid a loop as it's a very large dataset

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-13 : 04:52:25

Tried my best to done it without loop ..Try this



-----------------------Table to hold test data-----------------------------------------------------------------------------------------
CREATE TABLE #test(intCompanyID int,DatInserted datetime)
INSERT INTO #test
SELECT 365 ,'2014-04-25 11:12:01.940' UNION ALL
SELECT 365 ,'2014-04-25 11:16:50.960' UNION ALL
SELECT 365 ,'2014-06-03 11:38:40.187' UNION ALL
SELECT 365 ,'2014-06-03 11:38:41.320' UNION ALL
SELECT 365 ,'2014-06-03 15:49:00.223' UNION ALL
SELECT 365 ,'2014-06-04 08:57:59.627' UNION ALL
SELECT 365 ,'2014-06-04 08:58:20.417' UNION ALL
SELECT 365 ,'2014-06-04 09:09:23.103' UNION ALL
SELECT 365 ,'2014-06-04 09:10:39.967' UNION ALL
SELECT 365 ,'2014-06-05 11:30:21.103' UNION ALL
SELECT 365 ,'2014-06-05 11:31:06.803' UNION ALL
SELECT 365 ,'2014-06-05 11:31:10.187'

--------------bringing the Next_ID date reocrds for Comparision and inserting the reulst set into another temp table---------
SELECT * INTO #text
FROM 
(
SELECT	 (SELECT intCompanyID FROM (SELECT intCompanyID,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum =number) AS intCompanyID
		,(SELECT DatInserted FROM (SELECT DatInserted,RowNum   = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum =number) AS DatInserted
		,(SELECT DatInserted FROM (SELECT DatInserted,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum =number+1) AS Next_ID
		, CASE
		  WHEN number =1 THEN 1
		  WHEN ISNULL(DATEDIFF(   MINUTE
				   ,(SELECT DatInserted FROM (SELECT DatInserted,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum =number)
				   ,(SELECT DatInserted FROM (SELECT DatInserted,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum =number+1)
				  ),6)<5 
				AND  DATEDIFF(   MINUTE
				   ,(SELECT DatInserted FROM (SELECT DatInserted,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum =number-1)
				   ,(SELECT DatInserted FROM (SELECT DatInserted,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum =number)
				  )>5 THEN number
		   ELSE (SELECT MAX(RowNum) FROM (SELECT intCompanyID,RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM #test)a WHERE a.RowNum <number)
	      END  AS GroupNum
FROM	master.dbo.spt_values
WHERE	Type = 'P'
	    AND Number <= 12 AND number <>0
)a
--SELECT * FROM #text
--------------Final Query to achieve desired GroupNumber-------------------------------------------------

SELECT intCompanyID,DatInserted,DENSE_RANK()OVER (ORDER BY GroupNum ) AS GroupNumber FROM 
(
SELECT intCompanyID,DatInserted,CASE WHEN Next_ID IS NULL THEN (SELECT MAX(GroupNum) FROM #text T WHERE T.GroupNum<(SELECT MAX(GroupNUM) FROM #text T1)) ELSE GroupNum END AS GroupNum FROM #text
)b

---------------Drop tables-------------------------------------------------------------------------------
DROP TABLE #text
DROP TABLE #test
---------------------------------------------------------------------------------------------------------

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 06:31:53

quote:
Originally posted by sigreesql

This worked ok on a sample of test data but when I have a larger data set it doesn't seem to be working correctly, this is what I get - a different group should be assigned at 2014-05-01 00:05:25.140

intCompanyID DatInserted Group
365 2014-05-01 00:00:37.797 1
365 2014-05-01 00:01:18.827 1
365 2014-05-01 00:01:59.907 1
365 2014-05-01 00:02:40.723 1
365 2014-05-01 00:03:21.603 1
365 2014-05-01 00:04:02.360 1
365 2014-05-01 00:04:43.550 1
365 2014-05-01 00:05:25.140 1
365 2014-05-01 00:06:06.150 1
365 2014-05-01 00:06:47.197 1
365 2014-05-01 00:07:29.247 1
365 2014-05-01 00:08:09.837 1
365 2014-05-01 00:08:50.963 1
365 2014-05-01 00:09:31.807 1
365 2014-05-01 00:10:13.307 1
365 2014-05-01 00:10:54.193 1
365 2014-05-01 00:11:35.600 1
365 2014-05-01 00:12:16.780 1
365 2014-05-01 00:12:57.633 1

Why? It's neither 5 minutes after previous row nor 5 minutes after the first row?

Or do you want to data to be put into 5 minutes slots?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 06:37:20

Is this what you want?

intCompanyID	DatInserted Group
365	2014-05-01 00:00:37.797	1
365	2014-05-01 00:01:18.827	1
365	2014-05-01 00:01:59.907	1
365	2014-05-01 00:02:40.723	1
365	2014-05-01 00:03:21.603	1
365	2014-05-01 00:04:02.360	1
365	2014-05-01 00:04:43.550	1
365	2014-05-01 00:05:25.140	1
365	2014-05-01 00:06:06.150	2
365	2014-05-01 00:06:47.197	2
365	2014-05-01 00:07:29.247	2
365	2014-05-01 00:08:09.837	2
365	2014-05-01 00:08:50.963	2
365	2014-05-01 00:09:31.807	2
365	2014-05-01 00:10:13.307	2
365	2014-05-01 00:10:54.193	2
365	2014-05-01 00:11:35.600	3
365	2014-05-01 00:12:16.780	3
365	2014-05-01 00:12:57.633	3

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 06:50:54

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
CompanyID SMALLINT NOT NULL,
Inserted DATETIME NOT NULL
);

-- Populate sample data
INSERT @Sample
(
CompanyID,
Inserted
)
VALUES (365, '20140501 00:00:37.797'),
(365, '20140501 00:01:18.827'),
(365, '20140501 00:01:59.907'),
(365, '20140501 00:02:40.723'),
(365, '20140501 00:03:21.603'),
(365, '20140501 00:04:02.360'),
(365, '20140501 00:04:43.550'),
(365, '20140501 00:05:25.140'),
(365, '20140501 00:06:06.150'),
(365, '20140501 00:06:47.197'),
(365, '20140501 00:07:29.247'),
(365, '20140501 00:08:09.837'),
(365, '20140501 00:08:50.963'),
(365, '20140501 00:09:31.807'),
(365, '20140501 00:10:13.307'),
(365, '20140501 00:10:54.193'),
(365, '20140501 00:11:35.600'),
(365, '20140501 00:12:16.780'),
(365, '20140501 00:12:57.633');

-- Solution by SwePeso
WITH cteSource(grp, CompanyID, Inserted, tl)
AS (
SELECT 1 AS grp,
CompanyID,
MIN(Inserted) AS Inserted,
DATEADD(MINUTE, 5, MIN(Inserted)) AS tl
FROM @Sample
GROUP BY CompanyID

UNION ALL

SELECT c.grp + 1 AS grp,
c.CompanyID,
f.Inserted,
DATEADD(MINUTE, 5, f.Inserted) AS tl
FROM cteSource AS c
CROSS APPLY (
SELECT x.Inserted,
ROW_NUMBER() OVER (ORDER BY x.Inserted) AS rn
FROM @Sample AS x
WHERE x.CompanyID = c.CompanyID
AND x.Inserted > DATEADD(MINUTE, 5, c.Inserted)
) AS f
WHERE f.rn = 1
)
SELECT s.CompanyID,
s.Inserted,
c.grp
FROM cteSource AS c
INNER JOIN @Sample AS s ON s.CompanyID = c.CompanyID
AND s.Inserted BETWEEN c.Inserted AND c.tl
ORDER BY s.CompanyID,
s.Inserted;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigreesql
Starting Member

6 Posts

Posted - 2014-06-13 : 08:53:30

SwePeso that is perfect!! Thanks so much!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources