Writing a cursor with do while loop

[sonalshastry]

Hi

i want to write a cursor where in branch name should be passed into the cursor one by one, I have a bill table with following detail

Client, bill_date,bill_ amount, branch ...

I need to fetch data for top 5 client from every branch

below query gives me the data across for branch A

SELECT TOP 5 * FROM 
(Select Client_id, branch , sum(bill_amount)amount
from dbo.billtable
where trade_date >= '01 oct 2009' and trade_date <= '31 oct 2009'
AND BRANCH='A'    
group by branch,Client_id)
ORDER BY ter_branch tr DESC

for all the branches i dont want to hard code the branch value as there are around 900 braches

there is one more table which contain branch names, if do while loop can take branch name as a variable

please help

[code green]

It sounds more favourable to JOIN to this other branch table
rather than loop through al the branch names

[nbiswas]

Solution to Writing a cursor with do while loop

Try this(an example to simulate ur situation)

Creating some sample record set

declare @tblBill table(Clientid int, Billamount int, Branch varchar(10),BillDate datetime)
declare @tblBranch table(Branch varchar(10))
insert into @tblBill 
	select 11111,100000,'A','10/01/2009' union all 
	select 22222,199999, 'A' ,'10/21/2009' union all
	select 33333,199998,'A','10/25/2009' union all 
	select 11,100,'A','10/02/2009' union all 
	select 22,5000, 'A' ,'10/03/2009' union all
	select 33,34567,'A','10/04/2009' union all 
	select 44444,12315, 'B','10/24/2009' union all
	select 55555,5444,'B' ,'09/27/2009'union all 
	select 66666, 5410, 'B' ,'10/18/2009' union all 
	select 678,33333, 'B','10/24/2009' union all
	select 12347,444,'B' ,'10/27/2009'union all 
	select 012, 123, 'B' ,'10/18/2009' union all 
	select 77777,10000, 'C','10/23/2009' union all
	select 88888,20000,'C' ,'08/27/2009'union all 
	select 99999, 30000, 'C' ,'10/05/2009' union all 
	select 189,40000, 'C','10/23/2009' union all
	select 987,1110000,'C' ,'10/27/2009'union all 
	select 456, 4566, 'C' ,'10/05/2009' union all 
	select 12345,70000, 'D','10/23/2009' union all
	select 23456,90000,'D' ,'07/27/2009'union all 
	select 34567, 1110000, 'D' ,'10/05/2009' 
insert into @tblBranch 
	select 'A' union all select 'B' union all select 'C' union all select 'D'

Query- [ Picking up top 3 clients from every branch as per the highest bill payment for a particular data range]

select 
		Clientid
		,Billamount
		,Branch
		,BillDate
from (
select 
DENSE_RANK() over(partition by t1.Branch order by t1.Billamount desc) rn
,t1.* 
from @tblBill t1
inner join @tblBranch t2
on t1.Branch = t2.Branch
where t1.BillDate between '10/01/2009' And '10/31/2009') X
where X.rn <=3

Output:

Clientid Billamount Branch BillDate

22222	199999	A	2009-10-21 00:00:00.000
33333	199998	A	2009-10-25 00:00:00.000
11111	100000	A	2009-10-01 00:00:00.000
678	33333	B	2009-10-24 00:00:00.000
44444	12315	B	2009-10-24 00:00:00.000
66666	5410	B	2009-10-18 00:00:00.000
987	1110000	C	2009-10-27 00:00:00.000
189	40000	C	2009-10-23 00:00:00.000
99999	30000	C	2009-10-05 00:00:00.000
34567	1110000	D	2009-10-05 00:00:00.000
12345	70000	D	2009-10-23 00:00:00.000

Hope this helps.

One advice: Try to avoid procedural approaches(like cursor, while loops , if ..else etc.). Adopt Set Based approach(e.g. CTE etc.) . Your query performance will be higher.

[sonalshastry]

Hi,

I am useing MS SQL 2000 where in 'DENSE_RANK' is not a recognized function name.

so that I can not use this function, thats the reason I am writing a cursor in MS SQL 2000

[sonalshastry]

can some one give reference for Cursor writing , it will be big help

plz reply

[nbiswas]

Writing a cursor with do while loop

SQL SERVER – Simple Example of Cursor

SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

Hope this helps.