Results 1 to 4 of 4

Thread: Alternative of Cursor in SQL Server 2005

  1. #1
    Join Date
    Dec 2008
    Posts
    10

    Alternative of Cursor in SQL Server 2005

    Hi,
    I have a table which contain following fields
    account_no,
    Payment_date,
    Payment-Amount

    Now this table contain all the history of all accounts for payments.
    So if any account member has paid bills 100 times there are 100 rows for that and so on for all accounts.

    Now I have to display the latest payment date and amount for all accounts.

    So I am using the cursor for that, but the cursor is taking lot of minutes due to the large number of accounts.

    So please tell me the alternative for this problem.

  2. #2
    Join Date
    Mar 2009
    Posts
    2
    Code:
    select
    	*
    from
    	(
    		select
    			account_no
    			, max(Payment_date) max_date
    		from
    			<my_table>
    		group by
    			account_no
    	) g
    	join <my_table> t
    		on g.account_no = t.account_no
    		and g.max_date = t.Payment_date

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    whoops, looks like in 2005, "partion by" etc makes such problems easier.. maybe someone will submit such a solution

  4. #4
    Join Date
    Mar 2009
    Posts
    1

    Working with payment histories

    Because payments are sometimes received on the same day, max(date) may not produce the desired results when 're-joining' to the original table...

    Try something like this:

    Select Id, Date, Amt
    From
    (Select row_number() Over (Partition By Id Order by Date desc, Amt) as PayDateOrder, Id, Date, Amt
    From PymtTable) OrderedPymts
    Where PayDateOrder = 1

    By pre-ordering the payments (most recent set to 1), and then looking at only payments numbered as 1, all fields from the most recent payment row are now available to you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •