| Sloan's profileGranada Coder, C#, Sql S...PhotosBlogLists |  Tools  Help |
|
|
Granada Coder, C#, Sql Server Blog8/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
7/7/2009 SQLCMD (Simple Example)Download example HERE. (<< Right-click. "Save (Target) As" usually works best).
If I haven't mentioned it before, thank you Microsoft for the sqlcmd.exe utility. If you are new to sqlcmd or new to the concept of repeatability, below is a simple example. It creates 3 databases that are exactly the same except for the database-name. Can you say "repeatable" (as in every-single-time-repeatable-and-dependable)?
If you're still using the GUI in Sql Server Management Studio and doing a "Right-Click"/"Add Database" for than anything for casual development, then please stop. It is not repeatable. It does not promote repeatability for "D-DAY" (that's what I call deployment-day).
My motto:
If you cannot deploy your code, your code and hard-work is essentially worthless. The demo is simple. A database, one table, and 2 stored procedures. But the skeleton is there.
The example zip file is a demo example of creating a basic “OrganizationDB” (OrganizationDB01, OrganizationDB02, and OrganizationDB03).
Again, this is a sqlcmd.exe example of using $(Variable)’s to deploy databases….which emphasizes repeatability but with configurability as well. Instructions: Unzip the files……….read the README.txt file (the top portion contains setup information). If you have ".\SqlExpress" setup as your development environment, you should be able to just click:
Then you can look at the README.txt file for explanation about what is happening. There are also output log files you can look at (mentioned in the README.txt file) which will provide clues if anything does not work.
After you get it down, you’re essentially (always) one copy/paste/edit (the variable file) and a second copy/paste/edit (the .bat file) away from deploying a new database.
ISQLW OSQL << These are the early versions of this tool, FYI.
7/6/2009 Update TOP (N) (ORDER BY) ExampleWe all know a SELECT TOP(N) (ORDER BY) statement will work.
Use NorthwindGO select top(10) * from dbo.Orders ORDER BY CustomerID , OrderDate GO However, if you've tried to do a:
Update TOP (10) ...... ORDER BY X
That does not work.
Here is a workaround example to do an Update TOP(N) (ORDER BY) query with one (cte) statement.
----------START TSQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Television]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[Television]
END
GO
CREATE TABLE [dbo].[Television] (
TelevisionUUID [uniqueidentifier] not null default NEWSEQUENTIALID() ,
TelevisionName varchar(64) not null ,
TelevisionKey int not null ,
IsCheckedOut bit default 0
)
GO
ALTER TABLE dbo.Television ADD CONSTRAINT PK_Television_TelevisionUUID
PRIMARY KEY CLUSTERED (TelevisionUUID)
GO
ALTER TABLE dbo.Television ADD CONSTRAINT CK_Television_TelevisionName_UNIQUE
UNIQUE (TelevisionName)
GO
set nocount on
declare @counter int
select @counter = 11000
declare @currentTVName varchar(24)
declare @TopSize int
select @TopSize = 10 while @counter > 10000 -- this loop counter is ONLY here for fake data,....do not use this syntax for production code
begin
select @currentTVName = 'TV: '+ convert(varchar(24) , @counter)
INSERT into dbo.Television ( TelevisionName , TelevisionKey ) values ( @currentTVName , @counter)
select @counter = @counter - 1
end
select count(*) as TV_Total_COUNT from dbo.Television
/*
--Does not Work! Update TOP (10) dbo.Television Set IsCheckedOut = 1 FROM dbo.Television tv ORDER BY tv.TelevisionKey */
declare @AuditTrail table ( TelevisionUUID uniqueidentifier , OldIsCheckedOut bit , NewIsCheckedOut bit )
;
WITH cte1 AS
( SELECT TOP (@TopSize) TelevisionUUID , --<<Note, the columns here must be available to the output IsCheckedOut FROM dbo.Television tv WITH ( UPDLOCK, READPAST , ROWLOCK ) --<<Optional Hints, but helps with concurrency issues WHERE IsCheckedOut = 0 ORDER BY tv.TelevisionKey DESC ) UPDATE cte1
SET IsCheckedOut = 1 output inserted.TelevisionUUID , deleted.IsCheckedOut , inserted.IsCheckedOut into @AuditTrail ( TelevisionUUID , OldIsCheckedOut , NewIsCheckedOut ) ; print ''
print 'Newly Checked Out Items'
select * from dbo.Television tv where tv.IsCheckedOut <> 0 print 'Output AuditTrail' select * from @AuditTrail print 'Not checked out items' select count(*) as TVCOUNTIsNOTCheckedOut from dbo.Television tv where tv.IsCheckedOut = 0 System.Threading.Tasks.TaskThreads are now (or soon will be) very "Old School".
Well, that statement may be a little strong, but there will soon be a new option.
I attended a local user group and was pleased to learn about the:
.NET 4 --
System.Threading.Tasks.TaskHere is a URL describing it (better than I can re-hash it)It's still a little off into the future, but it is good to know nonetheless.Did I inspire a Sql Server 2008 feature?
Did I inspire a Sql Server 2008 feature? You be the judge: Go here: http://blogs.msdn.com/sqlcat/archive/2006/02/17/Partition-Elimination-in-SQL-Server-2005.aspx#5276553 And find this post dated on:
Now go here to this URL. Ok, now the heart of the matter. And read the 3-4 paragraphs below it.
Did I inspire a Sql Server 2008 feature? Well, I don't care how it got there, but the 2008 version is 100 times better than the 2005 version. ======================================================================== Below are the snipplets that I cite above....But I wanted to give the real URL's so this does not look made up.
October 4, 2007 1:50 PM sholliday said:
|
|||
|
|
