Monday, May 16, 2011

Rounding Off to the “Nearest Five”

Problem:

Given a real number, write a JavaScript code to round it off to the “nearest five”. Examples:

  • 12.4 ==> 10
  • 12.6 ==> 15

Analysis:

The result would be an integer which is a multiple of 5.

Solution:

We will write the JavaScript code as part of an HTML file:

<html>

<head>

<script type=”text/javascript”>

// PART 2: the rounding off javascript code will be here

</script>

</head>

<body>

</body>

<!-- PART 1: this section will contain the HTML elements to visually display to user the transformation -->

</html>

Part 1:

We need three elements: a text box for the inputting of the number, a button to trigger the rounding off javascript code, and another text box to display the rounded off.

<input type="text" id="txtInput">

<input type="button" onClick="doRoundItOff();" value="Round It!">

<input type="text" id="txtOutput" >

Here the trigger is on the onClick event of the button which calls the doRoundItOff JavaScript function.

Part 2:

Here is the JavaScript code:

function doRoundItOff() {

var txtInput = document.getElementById("txtInput");

var txtInputValue = parseInt(txtInput.value,10);

var remainder = txtInputValue % 5;

var roundedValue = 0;

if (remainder == 2 ) {

var offsetDecimal = parseInt(txtInput.value*10,10) % 10;

if (offsetDecimal < 5)

{

roundedValue = txtInputValue - remainder;

}

else

{

roundedValue = txtInputValue - remainder + 5;

}

}

else if (remainder > 2)

{

roundedValue = txtInputValue - remainder + 5;

}

else

{

roundedValue = txtInputValue - remainder;

}

var txtOutput = document.getElementById("txtOutput");

txtOutput.value = roundedValue;

}

Explain! Explain! Explain!

We started the doRoundItOff function with identifying the txtInput HTML element. It is noteworthy to say that I usually make use as var in the JavaScript the id in the HTML element.

Next we take into account the remainder when the integer part of the input is divided by five. This remainder will determine which multiple of five we have to choose.

If the remainder is two then we take into consideration the decimal value of the input. If it is less than five then the input is closer to the largest multiple of five less than the input. Hence, we round down. Otherwise the input would be closer to the smallest multiple of five greater than the input and we round up.

If remainder is greater than two, the input is closer to the smallest multiple of five that is greater than the input. Thus, we round up. Otherwise, the input is closer to the largest multiple of five less than the input and we round down.

Tuesday, May 10, 2011

Any fix to SQL Server Management Studio timing out when Adding a New Column to a Table

A while back I posted the inquiry http://nullpointer.ph/questions/3204/any-fix-to-sql-server-management-studio-timing-out-when-adding-a-new-column-to-a-table

Thanks to Alistair sharing his knowledge. After some research got the following fix

tools-options-designers-tables-and-database-designers

that is, navigating Tools > Options then looking at Designers > Table and Database Designers node you will have Table Options. Override the default value of Transaction time-out after: [____] seconds with your desired length of time.

Saturday, May 7, 2011

Extracting Data from an XML file in MS SQL Server

 

Assuming you have an XML file structured as follows

<questionnaire>

<question>

<id>1</id>

<text>Which of the following is TRUE?</text>

<option1>6 is a perfect number.</option1>

<option2>All prime numbers are odd.</option2>

<option3>None of the Above</option3>

<answer>B</answer>

<explanation>B is FALSE because 2 is prime and even.

Perfect Numbers are such that the sum of their prime factors and 1 equals the number. Since the prime factors of 6 are 2 and 3 and 2+3+1 = 6, then 6 must be perfect.</explanation>

</question>

</questionnaire>

 

and there are a thousand more <question> … </question> set in it but you are interested only in retrieving the id and answer. Using SQL Server 2008, what SQL statements should you use?

Here it is:

-------------------------------------------------

DECLARE @FilePath AS VARCHAR(50)
DECLARE @Query AS NVARCHAR(MAX)
DECLARE @QuestionData XML

-- set the path to xml file

SET @FilePath = 'C:\Users\test\questions.xml'

SET @Query = N'SELECT @data = CAST(BulkColumn AS XML) FROM OPENROWSET(BULK N''' + @FilePath + ''', SINGLE_BLOB) AS XmlData'

EXEC sp_executesql @Query, N'@data AS XML OUTPUT', @QuestionData OUTPUT

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @QuestionData

SELECT
    *
FROM
    OPENXML (@hdoc, '/questionnaire/question', 10)
    WITH (
        id INT,
        answer CHAR(1)
    )

EXEC sp_xml_removedocument @hdoc

-------------------------------------------------

Simple eh?