Want to Learn More About SQL Server Code Name “Denali”?


Then take a look at the Microsoft SQL Server code name ‘Denali’, Community Technology Preview 3 (CTP 3) Product Guide.

“The product guide download contains various datasheets, white papers, click-through and auto-running demonstrations, hands-on lab previews, technical presentations, and other useful links to help you evaluate the SQL Server code name ‘Denali’ CTP3 release.”

A detailed description of what’s included in the download can be found at LessThanDot.

Don’t forget to provide product feedback here and to visit the SQL Server (Pre-release) forums for up-to-date discussions on product features.

Creating MOD10V05 BPay Reference Numbers Using T-SQL


Introduction

BPAY is Australia’s most widely used bill payment service which enables bill payers to transfer funds electronically from their bank accounts to billers. It is offered as a payment channel by over 170 financial institutions through their phone and Internet banking services.

Since BPAY’s launch in 1997 electronic bill payment in Australia has grown at a phenomenal rate. BPAY can be used to pay over 19,000 different bills Australia wide and each month BPAY processes over 18 million individual payments.

Over the last couple of years there has been a substantial increase in the number of people who have access to internet banking which has resulted in a large increase in the number of people using BPAY. Now 72% of the Australian bill paying population use BPAY* with 87% of BPAY payments being made via the internet.

MSDN Gallery: http://code.msdn.microsoft.com/Creates-a-Mod10-MOD10V05-de0667c3

*source: TNS Research Nov 2009

Code

DECLARE @TestID BIGINT = 120319 
declare @TestIDLength TINYINT = 1 
declare @returnedVal int = 0 
declare @inputVal varchar(50) = @TestID
declare @odd bit = 0 
declare @sum int 
declare @numberLeft int 

WHILE @TestIDLength <= LEN(@inputVal) 
    BEGIN 
        SET @sum =  CAST(SUBSTRING(@inputval,@TestIDLength,1) AS TINYINT) 
          * @TestIDLength 
        
        SET @returnedVal = @returnedVal + @sum 
        SET @TestIDLength = @TestIDLength + 1 
    END 
	
SET @numberLeft = @returnedVal % 10 

SELECT CAST(CAST(@TestID AS VARCHAR(10)) 
  + CAST(@numberLeft AS CHAR(1)) AS BIGINT) AS BPayRefNumber 

Creating MOD10V01 BPay Reference Numbers Using T-SQL


Introduction

BPAY is Australia’s most widely used bill payment service which enables bill payers to transfer funds electronically from their bank accounts to billers. It is offered as a payment channel by over 170 financial institutions through their phone and Internet banking services.

Since BPAY’s launch in 1997 electronic bill payment in Australia has grown at a phenomenal rate. BPAY can be used to pay over 19,000 different bills Australia wide and each month BPAY processes over 18 million individual payments.

Over the last couple of years there has been a substantial increase in the number of people who have access to internet banking which has resulted in a large increase in the number of people using BPAY. Now 72% of the Australian bill paying population use BPAY* with 87% of BPAY payments being made via the internet.

MSDN Gallery: http://code.msdn.microsoft.com/Creates-a-Mod10-BPay-7ff5387a

*source: TNS Research Nov 2009

Code

DECLARE @TestID BIGINT = 1234567
DECLARE @TestIDLength TINYINT = 1
DECLARE @returnedVal INT = 0
DECLARE @inputVal VARCHAR(50) = REVERSE(@TestID)
DECLARE @odd BIT = 0
DECLARE @sum INT
DECLARE @numberLeft INT

WHILE @TestIDLength <= LEN(@inputVal)
    BEGIN
      IF @odd = 1
        BEGIN
          SET @sum = CAST(SUBSTRING(@inputval,@TestIDLength,1) AS TINYINT) * 1
          SET @odd = 0
        END
      ELSE
        BEGIN
          SET @sum = CAST(SUBSTRING(@inputval,@TestIDLength,1) AS TINYINT) * 2
          SET @odd = 1
        END
        
      IF LEN(CAST(@sum AS VARCHAR(10))) > 1
        SET @sum = CAST(SUBSTRING(CAST(@sum AS VARCHAR(10)),1,1) AS TINYINT) 
          + CAST(SUBSTRING(CAST(@sum AS VARCHAR(10)),2,1) AS TINYINT)
        
      SET @returnedVal = @returnedVal + @sum
      SET @TestIDLength = @TestIDLength + 1
    END

    IF @returnedVal % 10 = 0
      SET @numberLeft = 0
    ELSE
      SET @numberLeft = 10 - (@returnedVal % 10)

SELECT CAST(CAST(@TestID AS VARCHAR(10)) 
  + CAST(@numberLeft AS CHAR(1)) AS BIGINT) AS BPayRefNumber

 

 

%d bloggers like this: