Issue with Grouping consideringTime sequence

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)

Issue with Grouping consideringTime sequence

Author

Topic

kavitha.rose
Starting Member

2 Posts

Posted - 2015-02-05 : 18:47:03

Hi All,

Out of all the products in our system each product is uniquely identified by ProdNum,ProdStore and ProdCategory.
Consider that for a particular product I have a set of 10 rows.
In this 10 rows there are 5 columns that may change and there is a timestamp with it below are an example of those columns.
Out of this Row 1&2 needs to be grouped together as a set,3 needs to be 1 set,4 needs to be another set and 5 another.This is because of the time sequence. DOes anyone have any ideas in how this can be done?
I have tried many things including Adding a GroupID to identify each product rows and self joins and lead lag functions etc

1 2 3 4 5 TimeStamp
A B C D A 2009-01-10 06:26:50.0000000
A B C D A 2009-07-15 06:27:03.0000000
A B C D B 2010-05-12 06:39:39.0000000
A B C D A 2010-08-03 06:52:07.0000000
A B C D B 2010-08-06 06:56:07.0000000

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-06 : 05:59:36

[code]
-- *** Consumable Test Data ***
-- please supply in future
CREATE TABLE #t
(
Col1 char(1) NOT NULL
,Col2 char(1) NOT NULL
,Col3 char(1) NOT NULL
,Col4 char(1) NOT NULL
,Col5 char(1) NOT NULL
,UpdatedDate datetime NOT NULL
);
INSERT INTO #t
VALUES ('A', 'B', 'C', 'D', 'A', '20090110 06:26:50')
,('A', 'B', 'C', 'D', 'A', '20090715 06:27:03')
,('A', 'B', 'C', 'D', 'B', '20100512 06:39:39')
,('A', 'B', 'C', 'D', 'A', '20100803 06:52:07')
,('A', 'B', 'C', 'D', 'B', '20100806 06:56:07');
-- *** End Test Data ***

WITH Grps
AS
(
SELECT *
,ROW_NUMBER() OVER (ORDER BY UpdatedDate)
- ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3, Col4, Col5 ORDER BY UpdatedDate) AS Grp
FROM #t
)
,Packed
AS
(
SELECT Col1, Col2, Col3, Col4, Col5, Grp
,MAX(UpdatedDate) AS LastUpdated
FROM Grps
GROUP BY Col1, Col2, Col3, Col4, Col5, Grp
)
SELECT Col1, Col2, Col3, Col4, Col5, LastUpdated
FROM Packed
ORDER BY LastUpdated;
[/code]

kavitha.rose
Starting Member

2 Posts

Posted - 2015-02-08 : 21:32:04

Thanks.That was a neat way of solving it!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2015-02-09 : 17:42:12

This method will likely give you the fastest performance and it will preserve a setid a little nicer


-- *** Consumable Test Data ***
-- please supply in future
declare @tmp as table 
(id int primary key clustered identity(1,1)
	,Col1 char(1) NOT NULL
	,Col2 char(1) NOT NULL
	,Col3 char(1) NOT NULL
	,Col4 char(1) NOT NULL
	,Col5 char(1) NOT NULL
	,UpdatedDate datetime NOT NULL
	,setid int
);
INSERT INTO @tmp (col1,col2,col3,col4,col5,UpdatedDate )
VALUES ('A', 'B', 'C', 'D', 'A', '20090110 06:26:50')
	,('A', 'B', 'C', 'D', 'A', '20090715 06:27:03')
	,('A', 'B', 'C', 'D', 'B', '20100512 06:39:39')
	,('A', 'B', 'C', 'D', 'A', '20100803 06:52:07')
	,('A', 'B', 'C', 'D', 'B', '20100806 06:56:07');

declare @setid as int ,@col1 char(1),@col2 char(1),@col3 char(1),@col4 char(1),@col5 char(1)

set @setid = 0
update @tmp
set
@setid = setid = case when col1 = @col1 and @col2 = col2 and @col3 = col3 and @col4 = col4 and @col5 = col5 then @setid else  @setid + 1 end
,@col1 = col1 
,@col2 = col2 
,@col3 = col3
,@col4 = col4
,@col5 = col5
-- *** End Test Data ***

select * from @tmp

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources