Udostępnij za pośrednictwem

Transact-SQL Guru - Gaps and Islands Problem

  • Artykuł

It's time for another July TechNet Guru winner!

Multi MCC award winner, three-time TechNet Guru Gold Medal winner, and Wiki Ninja Belt holder, Naomi N is our T-SQL Guru for July! See the TechNet Guru Contributions for July 2013.

 

Naomi N's avatar

ABOUT NAOMI: I am an IT professional with more than 15 years of experience in variety of programming languages and technologies. I am a Microsoft Community Award Recipient and Personality of the Year at UniversalThread.com forum in 2008,2009,2010,2011.  

 

   

Here is the gold-medal-winning article:

T-SQL: Gaps and Islands Problem

 

 Now let's look at all the winning articles:

Guru Award  Transact-SQL Technical Guru - July 2013  

Gold Award Winner

 

 Naomi N T-SQL: Gaps and Islands Problem Richard Mueller: "Good explanation with great references." Samuel Lester: "Great scope, content, background info, references, and solution! In my opinion, the perfect scope for a wiki article. I also love that it references a question posed by the SQL Server community. " Peter Laker: "Very useful snippet" DB: "This is a good summary of a topic that's been well covered in the referenced authorities."

Silver Award Winner

 

 Naomi N T-SQL: FIFO Inventory Problem - Cost of Goods Sold Peter Laker: "Deeply detailed and full of techy goodness." DB: "This is substantial, novel and important." Richard Mueller: "Advanced topic with lots of code. Suggestions: 1. Use third person when discussing. 2. Use descriptive aliases in the code to improve readability. 3. Eliminate columns not relevant to the discussion. 4. Show minimum code necessary to demonstrate how to calculate FIFO costs. " Samuel Lester: "Amazing level of detail! This article will be extremely useful for people with a similar problem, but it almost borders on the verge of too specific for this wiki/editable forum. I'm worried that even if someone with a nearly identical challenge makes updates that the flow/readability/usefulness may diminish. Would be spectacular as a blog entry as it is so incredibly detailed and full of useful content."

 

Naomi is holding up the T-SQL category for July, thanks Naomi. This should be a category many disciplines can contribute to, so maybe August will pick up. Thanks nonetheless to Naomi for some great contributions!

 

And here's an excerpt from the article:

Problem Definition

The thread originator was kind enough to provide DDL of the table and some data to describe the task:

Createtable T1

(Id ``int identity ``primary key ``,

VoucherNo ``varchar``(4),

TransNo ``varchar``(10)

)

 

Insertinto T1 ``values ( ``'V100'``,``'Trns1'``),(``'V101'``,``'Trns1'``),(``'V102'``,``'Trns1'``),(``'V103'``,``'Trns1'``),(``'V104'``,``'Trns1'``),(``'V106'``,``'Trns1'``)

And he also provided the desired output:

TransNo  FirstVoucher  LastVoucher  Quantity 
 Trns1 V100 V104 5
 Trns1 V106 V106 1

The problem is to find consecutive vouchers (100-104, then 106).
  

 

 

===================================

 

Read the rest here:

T-SQL: Gaps and Islands Problem

 

 

Thanks to Naomi N for your great contribution to the TechNet Guru contest! You can read about all the July winners here: TechNet Guru Awards - July 2013

 

Also, for the August Guru competition, see TechNet Guru Contributions - August 2013.

 

 

Are you a Wiki Ninja? https://technet.com/wiki

    - User Ed

Comments