Group consecutive records - 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

General SQL Server Forums

New to SQL Server Programming

Group consecutive records

Author

Topic

viallos
Starting Member

3 Posts

Posted - 2012-02-22 : 18:55:22

I have to analyse massive amount of data and would like to remove unnecessery rows from my search results.

It is periodical table that contain interest value for contract at the end of each month. In many examples rates are not changing for couple of months and this is the content I want to filtered out. I want to select only month when value have changed compared to last month. I can't do distinct as value can come back to previous value after number of month and I want to capture this change.

My table is as follows:

acc_no	int_rate	marg_rate	cust_rate	period_end
111	3	1	2	31/01/2010
111	3	1	2	28/02/2010
111	3,5	1,5	2	31/03/2010
111	3,5	2	1,5	30/04/2010
111	3,5	2	1,5	31/05/2010
111	3,5	2	1,5	30/06/2010
111	3	1	2	31/07/2010
111	3	1	2	31/08/2010
222	4	2	2	31/01/2010
222	4	2	2	28/02/2010
222	4	2	2	31/03/2010
222	5	3	2	30/04/2010
222	5	3	2	31/05/2010
222	5	3	2	30/06/2010
222	4	2	2	31/07/2010
222	4	2	2	31/08/2010
333	3	1	2	31/01/2010
333	3	1	2	28/02/2010
333	4	2	2	31/03/2010
333	4	2	2	30/04/2010
333	5	3	2	31/05/2010
333	5	3	2	30/06/2010
333	3,5	2	1,5	31/07/2010
333	3,5	2	1,5	31/08/2010

Expected results as follows

acc_no	int_rate	marg_rate	cust_rate	period_end
111	3	1	2	31/01/2010
111	3,5	1,5	2	31/03/2010
111	3,5	2	1,5	30/04/2010
111	3	1	2	31/07/2010
222	4	2	2	31/01/2010
222	5	3	2	30/04/2010
222	4	2	2	31/07/2010
333	3	1	2	31/01/2010
333	4	2	2	31/03/2010
333	5	3	2	31/05/2010
333	3,5	2	1,5	31/07/2010

Thanks for help in advance

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-02-23 : 02:43:56

Try this..

Select * from
(Select row_number() over(partition by acc_no,int_rate,marg_rate,cust_rate order by period_end) as s_no,
acc_no,int_rate,marg_rate,cust_rate,period_end)a where s_no=1

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

viallos
Starting Member

3 Posts

Posted - 2012-02-23 : 04:33:22

This is working perfectly.

Thanks a million - this query reduced numbers of rows I have to review by 80%

Really appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 06:35:41

NO, IT DOESN'T WORK!

This line is removed by the flawed query!

111 3 1 2 31/07/2010

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 06:53:42

[code]DECLARE @Sample TABLE
(
acc_no INT,
int_rate MONEY,
marg_rate MONEY,
cust_rate MONEY,
period_end DATE
)

SET DATEFORMAT DMY

INSERT @Sample
VALUES (111, 3 , 1 , 2 , '31/01/2010'),
(111, 3 , 1 , 2 , '28/02/2010'),
(111, 3.5, 1.5, 2 , '31/03/2010'),
(111, 3.5, 2 , 1.5, '30/04/2010'),
(111, 3.5, 2 , 1.5, '31/05/2010'),
(111, 3.5, 2 , 1.5, '30/06/2010'),
(111, 3 , 1 , 2 , '31/07/2010'),
(111, 3 , 1 , 2 , '31/08/2010'),
(222, 4 , 2 , 2 , '31/01/2010'),
(222, 4 , 2 , 2 , '28/02/2010'),
(222, 4 , 2 , 2 , '31/03/2010'),
(222, 5 , 3 , 2 , '30/04/2010'),
(222, 5 , 3 , 2 , '31/05/2010'),
(222, 5 , 3 , 2 , '30/06/2010'),
(222, 4 , 2 , 2 , '31/07/2010'),
(222, 4 , 2 , 2 , '31/08/2010'),
(333, 3 , 1 , 2 , '31/01/2010'),
(333, 3 , 1 , 2 , '28/02/2010'),
(333, 4 , 2 , 2 , '31/03/2010'),
(333, 4 , 2 , 2 , '30/04/2010'),
(333, 5 , 3 , 2 , '31/05/2010'),
(333, 5 , 3 , 2 , '30/06/2010'),
(333, 3.5, 2 , 1.5, '31/07/2010'),
(333, 3.5, 2 , 1.5, '31/08/2010')

-- Senthil_Nagore (9 rows returned) FAIL
Select * from
(Select row_number() over(partition by acc_no,int_rate,marg_rate,cust_rate order by period_end) as s_no,
acc_no,int_rate,marg_rate,cust_rate,period_end from @sample)a where s_no=1

-- SwePeso (11 rows returned)
SELECT ACC_NO,
INT_RATE,
MARG_RATE,
CUST_RATE,
MIN(PERIOD_END) AS PERIOD_END
FROM (
SELECT ACC_NO,
INT_RATE,
MARG_RATE,
CUST_RATE,
PERIOD_END,
ROW_NUMBER() OVER (PARTITION BY ACC_NO ORDER BY PERIOD_END) - ROW_NUMBER() OVER (PARTITION BY ACC_NO, INT_RATE, MARG_RATE, CUST_RATE ORDER BY PERIOD_END) AS Block
FROM @Sample
) AS d
GROUP BY ACC_NO,
INT_RATE,
MARG_RATE,
CUST_RATE,
BLock
ORDER BY ACC_NO,
PERIOD_END[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources