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]

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]

SQL SERVER – Simple Example of Cursor

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

Hope this helps.