ROW_NUMBER/RANk RESET WHEN ON/OFF?

Question

ROW_NUMBER/RANk RESET WHEN ON/OFF?

mlinden80

Old Hand

Points: 320
More actions
April 16, 2015 at 3:05 pm

#316658

Hi,
At work I have a new data set, where a product can have extras added. The data records a time stamp for every time the product has been amended and the status of he extras for each updated.
If the extras was still attached when updated, it will be YES, if it was not upsold or removed it will return NULL. I would like a flag that would return the first instance of each add/removal.
for example, if the extra was added right from the start to end of contract then i can select MIN(DATE) of when it was added. Thats the easy ones out of the way. But we have customers who change their mind multiple time during the tenure. so if it was added then removed, then added again i would like a flag to show 1 twice for each added and 1 for the removal.
Hope you can help
I have attached some code (and picture) you can run straight in SQL which will give you the attmpt i made but not what i wanted, and a hardcoded value, but its a representation of what im looking for.
IF OBJECT_ID(N'tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1
--create temp table
CREATE TABLE #TEMP1(
RECORD_ID INT NOT NULL IDENTITY,
UPDATE_DATE DATETIME NULL,
EXTRAS VARCHAR(3) NULL)
--insert 10 random recordsinto temp table
INSERT INTO #TEMP1
( UPDATE_DATE, EXTRAS )
VALUES ( GETDATE()-10,'YES'),
( GETDATE()-9,NULL),
( GETDATE()-8,'YES'),
( GETDATE()-7,'YES'),
( GETDATE()-6,'YES'),
( GETDATE()-5,NULL),
( GETDATE()-4,NULL),
( GETDATE()-3,'YES'),
( GETDATE()-2,'YES'),
( GETDATE()-1,NULL)
--the bit i struggle with. I am trying to add a flag/row number which resets itself every time the extras is added and removed.
-- every way i try carries on the number from the last occurance
select *,row_number()over (PARTITION BY EXTRAS ORDER BY RECORD_ID) as FLAG from #test
ORDER BY RECORD_ID
-- this is hard coded, but shows 2 representations of how i would like the flag to return. labelled FLAG1 and FLAG2. Is it possible to reflect the values
--without inner joing on same table? can it be done with onumber/rank/dense rank
IF OBJECT_ID(N'tempdb..#TEMP2') IS NOT NULL DROP TABLE #TEMP2
CREATE TABLE #TEMP2(
RECORD_ID INT NOT NULL IDENTITY,
UPDATE_DATE DATETIME NULL,
EXTRAS VARCHAR(3) NULL,
FLAG1 INT NULL,
FLAG2 INT NULL)
INSERT INTO #TEMP2
( UPDATE_DATE,EXTRAS,FLAG1,FLAG2 )
VALUES ( GETDATE()-10,'YES',1,1),
( GETDATE()-9,NULL,1,1),
( GETDATE()-8,'YES',1,1),
( GETDATE()-7,'YES',2,0),
( GETDATE()-6,'YES',3,0),
( GETDATE()-5,NULL,1,1),
( GETDATE()-4,NULL,2,0),
( GETDATE()-3,'YES',1,1),
( GETDATE()-2,'YES',2,0),
( GETDATE()-1,NULL,1,0)
SELECT * FROM #TEMP2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Can you include ddl and sample data for #test

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mlinden80 Old Hand Points: 320 More actions · Answer 2

Hi,

The sample data is in the SQL code provided. Its a simplified table, of issue experienced. Unfortunately as its work data, I cant provide any live samples.

If you copy and paste, it should return 2 tables of 10 rows. the first one where I am genuinely trying to get flag. 2nd table is hard coded, for how i would like the flag to look like (2 alternatives)

Thanks

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

This should work for you

WITH CTE AS (

select *,

row_number()over (ORDER BY RECORD_ID) -

row_number()over (PARTITION BY EXTRAS ORDER BY RECORD_ID) as rnDiff

from #TEMP1)

select RECORD_ID,UPDATE_DATE,EXTRAS,

row_number()over (PARTITION BY EXTRAS,rnDiff ORDER BY RECORD_ID) as FLAG1,

case when row_number()over (PARTITION BY EXTRAS,rnDiff ORDER BY RECORD_ID) = 1 THEN 1 ELSE 0 END as FLAG2

from CTE

ORDER BY RECORD_ID;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

mlinden80 Old Hand Points: 320 More actions · Answer 4

Mark Cowne (4/17/2015)
This should work for you

Perfect, thank you ever so much!!!