• I've rewritten some of our report writers' cursor queries lately, mainly thanks to going on Itzik Ben-Gan's Advanced T-SQL course last year and reading his Inside SQL Server 2008 T-SQL Querying book.

    Using common table expressions and joining a table back to itself to get running totals has been the most common cursor replacement technique and it is covered in the book.

    A very simplified version of one for deliveries in year on a purchase order is below. My original CTE query was much more complex.

    with p as

    (

    SELECT po.PurchaseOrder

    ,p.TransDate

    ,YEAR(p.TransDate) as DeliveryYear

    ,ISNULL(sum(p.Quantity),0.0) as QtyDelivered

    FROM dbo.PurchaseOrders

    )

    --OK Now calculate a running total as we go.

    select p.PurchaseOrder,

    p.TransDate,p.DeliveryYear,p.QtyDelivered,SUM(p1.QtyDelivered) as CumDelivered

    from p join p as p1

    on p.PurchaseOrder = p1.PurchaseOrder and p.DeliveryYear = p1.DeliveryYear

    and p1.TransDate <= p.TransDate

    group by p.PurchaseOrder,p.DeliveryYear,p.TransDate,p.QtyDelivered

    order by p.PurchaseOrder,p.DeliveryYear,p.TransDate