Home » SQL & PL/SQL » SQL & PL/SQL » Creating a running counter?
Creating a running counter? [message #333362] Fri, 11 July 2008 06:54 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
I have a table that contians charges per account.

There can be up to say 6 charges max per account.

Here is a simple query to pull back one account.

Select Account_Num,CPT,CPT_Desc From Charges
WHERE Account_Num=1


and the results would look like

Account_Num CTP
1 11111
1 22222
1 33333
1 44444

Is there a way to put a number by each charge, and then reset per account.
Account_Num CTP Number
1 11111 1
1 22222 2
1 33333 3
1 44444 4


My end goal is to get all the charges in one line so the header would be like

Account_Num, CPT1,CPT2,CTP3,CPT4

I figured if i could get the counter next to each charge i could use a group by and a set of cases put this into one line.

Any ideas would be great.

Thanks

Re: Creating a running counter? [message #333371 is a reply to message #333362] Fri, 11 July 2008 07:23 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Research use of ROW_NUMBER analytic function.
Re: Creating a running counter? [message #333382 is a reply to message #333362] Fri, 11 July 2008 07:52 Go to previous message
fmrock
Messages: 45
Registered: December 2006
Member
Thanks,

SELECT 
	CHARGES.CPCODE "Group", 
	CHARGES.ACCOUNT "Account", 
	CHARGES.TYPE "Type", 
	CHARGES.PRCODE "CPT", 
	CHARGES.PRDESC "CPT_Desc", 
	CHARGES.PRAMOUNT "CPT_Amount", 
	ROW_NUMBER() OVER (partition BY CHARGES.CPCODE,CHARGES.ACCOUNT ORDER BY CHARGES.SEQNO) "CPT_Num"
FROM   
	CHARGES CHARGES
WHERE  
	(CHARGES.SPLITFLAG IS  NULL)
	AND (CHARGES.TYPE='C') 
	AND (CHARGES.CPCODE IN ('111','222','333'))
	AND (CHARGES.ACCOUNT in ('1','2','3','4','5','6')) 



Previous Topic: sequence with "NO ORDER"
Next Topic: using utl_SMPT.VRFY
Goto Forum:
  


Current Time: Sun Dec 11 02:44:04 CST 2016

Total time taken to generate the page: 0.05449 seconds