| Sloan's profileGranada Coder, C#, Sql S...PhotosBlogLists |  Tools  Help |
|
8/13/2009 CTE Running Total Example
I happened across a CURSOR based "Running Total" example on the web today. So I coded up (one) alternate solution. Below is (one variation) of a 'Running Total' solution using a CTE. -----------START TSQL Use NorthwindGO
declare @CustomerID varchar(6)declare @BeginDate datetimedeclare @EndDate datetimeselect @CustomerID = (select top 1 CustomerID from dbo.Orders )select @BeginDate = '01/01/1900'select @EndDate = '12/31/2010'
; WITH MyCTE -- http://technet.microsoft.com/en-us/library/ms175972.aspx( ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry,CustomerID,CustomerName,[Address],City ,Region,PostalCode,Country,Salesperson,OrderID,OrderDate,RequiredDate,ShippedDate,ShipperName,ProductID ,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight,ROWID) AS( SELECTShipName ,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry,CustomerID,CustomerName,[Address],City ,Region,PostalCode,Country,Salesperson,OrderID,OrderDate,RequiredDate,ShippedDate,ShipperName,ProductID ,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight , ROW_NUMBER() OVER ( ORDER BY OrderDate , ProductName ASC ) as ROWID FROMdbo .Invoices inv -- "Invoices" is a VIEW, FYI whereinv .CustomerID = @CustomerID and (inv.OrderDate between @BeginDate and @EndDate)) SELECT /*ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry,CustomerID,CustomerName,[Address], City,Region,PostalCode,Country,Salesperson,OrderID,OrderDate,RequiredDate,ShippedDate,ShipperName, ProductID,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight, */ --trim the list down a little for the final outputCustomerID ,Salesperson,OrderID,OrderDate,ProductName,UnitPrice,Quantity,Discount,ExtendedPrice,Freight,(ExtendedPrice + Freight) as ComputedTotal --The below line is the "trick". I reference the above CTE, but only get data that is less than or equal to the row that I am on (outerAlias.ROWID) , (Select SUM (ExtendedPrice + Freight) from MyCTE innerAlias where innerAlias.ROWID <= outerAlias.ROWID ) as RunningTotal , ROWID as ROWID_SHOWN_FOR_KICKS , OrderDate as OrderDateASecondTimeForConvenience FROM MyCTE outerAlias--Two Order By Options --ORDER BY outerAlias.OrderDate , ProductName -- << Whatever the ORDER BY is here, should match the "ROW_NUMBER() OVER ( ORDER BY ________ ASC )" statement inside the CTE ORDER BY outerAlias.ROWID -- << Or, to keep is more "trim", ORDER BY the ROWID, which will of course be the same as the "ROW_NUMBER() OVER ( ORDER BY" inside the CTE
Comments (1)
TrackbacksThe trackback URL for this entry is: http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!595.trak Weblogs that reference this entry
|
|
|
