Help with row_number - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with row_number

Author

Topic

Diablos
Starting Member

10 Posts

Posted - 2012-05-08 : 12:08:20

Hi,
The idea of this query is to detect consecutive recharges, if "Control" is 1 then counter=counter+1 else counter=0,
i'm using the column "counter"(row_Number) as a counter but the order is wrong i want to order by "Card" and "Entry" and because of this the row_number function is not working properly.

here is my query:

select *, row_number() over(partition by control,card order by card,year1,month1)as counter from(
select *,
case when month1<>12 then
case when year1=year2 and month1+1=month2 then
1
else
0
end
else
case when year1+1=year2 and month2=1 then
1
else
0
end
end as control
from(
SELECT [Entry No_] as entry, [Card No_] as card, Year as year1, Month as month1, Amount as value,
(select top 1 month from [MGC$Interaction Log Entry]
where
[MGC$Interaction Log Entry].[entry no_]>aux.[Entry No_] and[MGC$Interaction Log Entry].[card no_]=
aux.[card no_] and [valuation type]=4 and canceled=0 and [interaction group code]='psoc') as month2,
(select top 1 year from [MGC$Interaction Log Entry]
where
[MGC$Interaction Log Entry].[entry no_]>aux.[Entry No_] and[MGC$Interaction Log Entry].[card no_]=
aux.[card no_] and [valuation type]=4 and canceled=0 and [interaction group code]='psoc') as year2,
dateadd(yy,(year-1900),0) + dateadd(mm,month,0) + 02-1 as data,(select top 1 [entry no_] from [MGC$Interaction Log Entry]
where
[MGC$Interaction Log Entry].[entry no_]>aux.[Entry No_] and[MGC$Interaction Log Entry].[card no_]=
aux.[card no_] and [valuation type]=4 and canceled=0 and [interaction group code]='psoc') as entry2
FROM [MGC$Interaction Log Entry] as aux
where [valuation type]=4 and canceled=0 and [interaction group code]='psoc') as dados1) as dados2
where card ='gps09-00074'

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:23:54

i cant view the image . can you post sample data please?

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

Diablos
Starting Member

10 Posts

Posted - 2012-05-08 : 12:32:08

quote:
Originally posted by visakh16

i cant view the image . can you post sample data please?

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

try this link http://s16.postimage.org/nt6aru3ph/contador.png

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:40:45

quote:
Originally posted by Diablos

quote:
Originally posted by visakh16

i cant view the image . can you post sample data please?

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

try this link http://s16.postimage.org/nt6aru3ph/contador.png

Cant access it as its blocked here

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

Diablos
Starting Member

10 Posts

Posted - 2012-05-08 : 12:42:36

quote:
Originally posted by visakh16

quote:
Originally posted by Diablos

quote:
Originally posted by visakh16

i cant view the image . can you post sample data please?

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

here: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/03f10b4f-3afc-4496-958c-bfccb9f64d79

try this link http://s16.postimage.org/nt6aru3ph/contador.png

Cant access it as its blocked here

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

here: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/03f10b4f-3afc-4496-958c-bfccb9f64d79

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 16:22:10

so whats the problem you're facing. unable to understand from posted data as rownumber values look fine. How else are you expecting it to come?

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

Diablos
Starting Member

10 Posts

Posted - 2012-05-09 : 04:03:14

quote:
Originally posted by visakh16

so whats the problem you're facing. unable to understand from posted data as rownumber values look fine. How else are you expecting it to come?

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

is not ordering in the right order, if you notice the first two lines the year1 is 2012 e then it's 2009 an do so on, those first two lines should be in the bottom...
should be something like this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 22:14:40

unless you clearly define in words your rule i dont think anybody will be able to help you out!

how did you get counter as 1 for last row?does it reset for each control value?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources