By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,660 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,660 IT Pros & Developers. It's quick & easy.

Writing a cursor with do while loop

P: 6
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

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 5 * FROM 
  2. (Select Client_id, branch , sum(bill_amount)amount
  3. from dbo.billtable
  4. where trade_date >= '01 oct 2009' and trade_date <= '31 oct 2009'
  5. AND BRANCH='A'    
  6. group by branch,Client_id)
  7. ORDER BY ter_branch tr DESC
  8.  
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
Dec 1 '09 #1
Share this Question
Share on Google+
5 Replies


code green
Expert 100+
P: 1,726
It sounds more favourable to JOIN to this other branch table
rather than loop through al the branch names
Dec 1 '09 #2

nbiswas
100+
P: 149
Try this(an example to simulate ur situation)

Creating some sample record set

Expand|Select|Wrap|Line Numbers
  1. declare @tblBill table(Clientid int, Billamount int, Branch varchar(10),BillDate datetime)
  2. declare @tblBranch table(Branch varchar(10))
  3. insert into @tblBill 
  4.     select 11111,100000,'A','10/01/2009' union all 
  5.     select 22222,199999, 'A' ,'10/21/2009' union all
  6.     select 33333,199998,'A','10/25/2009' union all 
  7.     select 11,100,'A','10/02/2009' union all 
  8.     select 22,5000, 'A' ,'10/03/2009' union all
  9.     select 33,34567,'A','10/04/2009' union all 
  10.     select 44444,12315, 'B','10/24/2009' union all
  11.     select 55555,5444,'B' ,'09/27/2009'union all 
  12.     select 66666, 5410, 'B' ,'10/18/2009' union all 
  13.     select 678,33333, 'B','10/24/2009' union all
  14.     select 12347,444,'B' ,'10/27/2009'union all 
  15.     select 012, 123, 'B' ,'10/18/2009' union all 
  16.     select 77777,10000, 'C','10/23/2009' union all
  17.     select 88888,20000,'C' ,'08/27/2009'union all 
  18.     select 99999, 30000, 'C' ,'10/05/2009' union all 
  19.     select 189,40000, 'C','10/23/2009' union all
  20.     select 987,1110000,'C' ,'10/27/2009'union all 
  21.     select 456, 4566, 'C' ,'10/05/2009' union all 
  22.     select 12345,70000, 'D','10/23/2009' union all
  23.     select 23456,90000,'D' ,'07/27/2009'union all 
  24.     select 34567, 1110000, 'D' ,'10/05/2009' 
  25. insert into @tblBranch 
  26.     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]

Expand|Select|Wrap|Line Numbers
  1. select 
  2.         Clientid
  3.         ,Billamount
  4.         ,Branch
  5.         ,BillDate
  6. from (
  7. select 
  8. DENSE_RANK() over(partition by t1.Branch order by t1.Billamount desc) rn
  9. ,t1.* 
  10. from @tblBill t1
  11. inner join @tblBranch t2
  12. on t1.Branch = t2.Branch
  13. where t1.BillDate between '10/01/2009' And '10/31/2009') X
  14. where X.rn <=3
Output:

Clientid Billamount Branch BillDate
Expand|Select|Wrap|Line Numbers
  1. 22222    199999    A    2009-10-21 00:00:00.000
  2. 33333    199998    A    2009-10-25 00:00:00.000
  3. 11111    100000    A    2009-10-01 00:00:00.000
  4. 678    33333    B    2009-10-24 00:00:00.000
  5. 44444    12315    B    2009-10-24 00:00:00.000
  6. 66666    5410    B    2009-10-18 00:00:00.000
  7. 987    1110000    C    2009-10-27 00:00:00.000
  8. 189    40000    C    2009-10-23 00:00:00.000
  9. 99999    30000    C    2009-10-05 00:00:00.000
  10. 34567    1110000    D    2009-10-05 00:00:00.000
  11. 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.
Dec 2 '09 #3

P: 6
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
Dec 2 '09 #4

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

plz reply
Dec 2 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.