counting status change in table

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

Transact-SQL (2005)

counting status change in table

Author

Topic

tariq2k
Starting Member

6 Posts

Posted - 2011-10-25 : 06:55:16

H,

I would like to get a colum counting a status change for every object-date pair. So it would like this:

object date status status_change
1 1 z 1
1 2 z 1
1 4 a 2
1 10 b 3
1 15 b 3
1 19 b 3
2 1 y 1
2 4 y 1
2 6 c 2
1 20 z 4

Unfortunately dense_rank won't work with that (it orders alphabetically).

Fore easier checking you can use a subquery I wrote:
SELECT * FROM
(SELECT 1 object, 1 date, 'z' status
UNION ALL SELECT 1 object, 2 date, 'z' status
UNION ALL SELECT 1 object, 4 date, 'a' status
UNION ALL SELECT 1 object, 10 date, 'b' status
UNION ALL SELECT 1 object, 15 date, 'b' status
UNION ALL SELECT 1 object, 19 date, 'b' status
UNION ALL SELECT 2 object, 1 date, 'y' status
UNION ALL SELECT 2 object, 4 date, 'y' status
UNION ALL SELECT 2 object, 6 date, 'c' status
UNION ALL SELECT 1 object, 20 date, 'z' STATUS
)q

thanks in advance
T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 07:49:59

see
http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tariq2k
Starting Member

6 Posts

Posted - 2011-10-25 : 09:47:35

Hi,

I checked your "Third running streak" script. To get what I wanted I need implement another subquery. Is it possible to avoid nesting so much?

Below your script with my modifications:

declare @t table (Id int, dt datetime, value int)
set dateformat 'dmy'
insert into @t

select 1, '10/12/2008', 10 union all
select 1, '11/12/2008', 10 union all
select 1, '12/12/2008', 10 union all
select 1, '13/12/2008', 9 union all
select 1, '14/12/2008', 10 union all
select 1, '15/12/2008', 10 union all
select 1, '16/12/2008', 10 union all
select 1, '17/12/2008', 10 union all
select 2, '05/03/2008', 8 union all
select 2, '06/03/2008', 6 union all
select 2, '07/03/2008', 8 union all
select 2, '08/03/2008', 8 union all
select 2, '09/03/2008', 8 union all
select 2, '20/03/2008', 8

SELECT *, DENSE_RANK() OVER (PARTITION BY id ORDER BY mindate) FROM
(SELECT * , MIN(dt) OVER(PARTITION BY id,grpID - recID,grpID - colID) mindate
FROM (
SELECT Id,
dt,
value,
ROW_NUMBER() OVER (PARTITION BY Id, value ORDER BY dt) AS recID,
ROW_NUMBER() OVER (ORDER BY value, dt) AS grpID,
ROW_NUMBER() OVER (ORDER BY dt) AS colID
FROM @t
) AS d
)AS q

ORDER BY id,
dt

tariq2k
Starting Member

6 Posts

Posted - 2011-10-25 : 10:14:45

BTW,

it seems you should add "PARTITION BY Id" to both grpID and ColID.

Check out what happens when you add following to your base table:
select 2, '21/03/2008', 9 UNION ALL
select 2, '15/12/2008', 9

record (id=1, dt=16/12/2008,value=10) goes to a new group, when it shouldn't change.

T.

tariq2k
Starting Member

6 Posts

Posted - 2011-10-26 : 08:16:47

Hi,

concerning query "Third running streak" - could someone explain (or point me to an explenation) of this part:
ROW_NUMBER() OVER (PARTITION BY Id, value ORDER BY dt) AS recID,
ROW_NUMBER() OVER (ORDER BY value, dt) AS grpID,
ROW_NUMBER() OVER (ORDER BY dt) AS colID

and then:
GROUP BY Id,
grpID - recID,
grpID - colID

I see it works - I just would like to understand logic behind it

Tomek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources