Generate series using T-SQL

February 26, 2010

It is common developer requirement to get missing records from series. For that they generate temporary table and insert complete list of numbers and then find missing numbers using LEFT JOIN. In SQL Server 2005 and 2008, new keyword “WITH” introduced that works with SELECT, INSERT and UPDATE statement that use to create temporary resultset using Common Table Expression (CTE).

Example 1:

      BEGIN
            WITH mycte AS
            (
                  SELECT 1 id
                  UNION ALL
                  SELECT id + 1
                  FROM    mycte
                  WHERE  id + 1 < = 100
            )
            SELECT id
            FROM mycte
            OPTION (MAXRECURSION 0)
        End

Example 2:
A company awarded performance award to their employee each year. Requirement is to find out the list of year in which performance award did not given to any employee.

      BEGIN
            Create Table Performance (EmployeeID int, [Year] int);
            Insert Into Performance VALUES (100, 2001);
            Insert Into Performance VALUES (200, 2003);
            Insert Into Performance VALUES (150, 2007);
            Insert Into Performance VALUES (155, 2008);

            WITH mycte AS
            (
                  -- suppose 2001 is the first year of the company
                  SELECT 2001 As AwardedYear
                  UNION ALL
                  SELECT AwardedYear + 1
                  FROM    mycte
                  WHERE  AwardedYear + 1 < = Year(GetDate()) - 1
            )
            SELECT t.AwardedYear
            FROM mycte t LEFT JOIN Performance P ON (t.AwardedYear = P.Year)
            WHERE P.Year IS NULL
            OPTION (MAXRECURSION 0)
            -- MAXRECURSION 0 means there is no limit of query recursion
        End

Posted in Microsoft, Programming No Comments »
Tags: SQL Server, T-SQL

Penn Foster Coupons

February 14, 2010

Penn Foster Career School offers over 80 quality distance education programs designed to give you the skills you need for a new career in today’s fastest growing fields.
You can study online, in print, or in combination. You decide which method best suits your learning style.
Penn Foster Career School offering High School Diploma, Associates & Bachelor Degree, Career Diplomas and many Certifications.

Are you planning to get admission in Penn Foster Career School? Wait I’ve listed some admission discounts and promotions that save your money at the time of admission.

Penn Foster Coupons

• Train for a career in the AUTOMOTIVE or MOTORCYCLE industry with Penn Foster Career School
• Earn Your Degree in Early Childhood Education through Penn Foster College
• Build it, Fix it, Ride it … Become a Motorcycle Repair Technician through Penn Foster Career School
• Earn your career diploma as a PC Repair Technician through Penn Foster Career School

• Add new skills or learn a new TRADE through Penn Foster
• Start a Career in Animal Care … become a Veterinary Assistant through Penn Foster Career School
• Visit Penn Foster Career School to find out how you can make more money in a new career!
• Visit Penn Foster Career School, the world’s leader in distance learning.
• Earn Your VETERINARY TECHNICIAN DEGREE at home with Penn Foster College
• Earn your High School Diploma through Penn Foster High School
• Choose from over 80 different career programs at Penn Foster Career School
• Train at home to be a PHARMACY TECHNICIAN with Penn Foster Career School

Posted in General, Learning & Growth No Comments »
Tags: Discount Coupons

Google Wave Invitation

February 13, 2010

What is a Google Wave?

Google Wave is an online software application product of Google, described as a personal communication and collaboration tool. It was first announced at the Google I/O conference on May 27, 2009. It is a web-based service, computing platform, and communications protocol designed to merge e-mail, instant messaging, wikis, and social networking. It has a strong collaborative and real-time focus supported by extensions that can provide, for example, spelling/grammar checking, automated translation among 40 languages, and numerous other extensions. Initially released only to developers, a preview release of Google Wave was extended to 100,000 users in September 2009, each allowed to invite additional users. On the 29th of November 2009, Google accepted most requests submitted soon after the extended release of the technical preview in September 2009.

Google Wave service is currently in development and testing phase that is why this service is not available for everyone and this is only available for 100,000 users. If you want to avail this service you should be invited for that. I’ve 200+ Google Wave Invitations if you need it you may request it. Comment your Name and email address I’ll reply.

Have Google Wave

Posted in Google 8 Comments »
Tags: Google Wave

Count Number of Words in a Sentence using ASP

October 16, 2009

There is no function in ASP to count number of words in sentence or long string. Bellow is very simple function that count number of words and return that count.

< %
Function CountWords (StrValue)
  Dim iWords, mLengthWithSpaces, mLengthWithoutSpaces

  ' following line calculate the actual length of string
  mLengthWithSpaces = Len(StrValue)

  ' following line remove all spaces from string and then calculate the length of string
  mLengthWithoutSpaces = Len(Replace(StrValue, " ", ""))

  ' subtract length (without spaces) from actual string length
  iWords = mLengthWithSpaces - mLengthWithoutSpaces

  CountWords = iWords + 1
End Function

'example
Msg = "This is text string"
Response.Write CountWords(Msg)

'output will be 4
%>

Posted in Programming, Visual Basic No Comments »
Tags: ASP