Monday, February 26, 2018

TSQL - While Loop in SQL Server


As we know that SQL Server does not contain for loop or do while loop to control of flow for the data processing. In SQL Server, we can use a T-SQL feature called WHILE loop to set single or multiple conditions or for the repeated execution of an SQL statement or statement block.
If we are using T-SQL While loop then inner statements will be executed repeatedly as long as the specified condition is true. SQL Server allows that inner execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.
So, we can take some ongoing examples to use While loop in this tutorial. First of all, we have to know the syntax of the while loop as given below -
-- Syntax for SQL Server and Azure SQL Database 
WHILE Boolean_expression  
     { sql_statement | statement_block | BREAK | CONTINUE } 

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse 
WHILE Boolean_expression  
     { sql_statement | statement_block | BREAK } 
Arguments
Boolean_expression is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.
{sql_statement | statement_block} is any Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block, use the control-of-flow keywords BEGIN and END.
BREAK causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.
CONTINUE causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.
Example – Print table of any number
In this example, we are print a table of any number by using while loop as given below -
---- Declare local variable
---- Incremental number counter
DECLARE @ictr int=1
---- Variable to print for table
DECLARE @inum int=2
---- While condtion
WHILE @ictr<11
BEGIN
---- multiply number and incremental number
PRINT @inum * @ictr
---- set incremental of number
SET @ictr= @ictr + 1
END
Output Result
1
2
3
4
5
6
7
8
9
10
Example – While loop with BREAK Statement
If a BREAK statement is executed within a WHILE loop, then it causes the control to go out of the while loop and start executing the first statement immediately after the while loop. In this example, we are printing the table till 100 but set the break condition by stating that if incremental number is just reached at 1o then T-SQL statement should be break means statements come out from While loop and just go the next statements of while loop as given below -
---- Declare local variable
---- Incremental number counter
DECLARE @ictr int=1
---- Variable to print for table
DECLARE @inum int=2
---- While condtion till 100
WHILE @ictr<100
BEGIN
---- multiply number and incremental number
PRINT @inum * @ictr
---- set incremental of number
SET @ictr= @ictr + 1
---- break statement if counter reached till 11
IF @ictr=11
BEGIN
PRINT 'Break: Counter is now at 11'
---- then break statement
BREAK;
END
END
---- next statements after While looop
PRINT 'Next statement after While loop'
Output Result
2
4
6
8
10
12
14
16
18
20
Break: Counter is now at 11
Next statement after While loop
Example – While loop with CONTINUE and BREAK Statement
If a CONTINUE statement is executed within a WHILE loop, then it skips executing the statements following it and transfers control to the beginning of while loop to start the execution of the next iteration. In this example, we have just put CONTINUE and BREAK together but using CONTINUE just before BREAK statement. If CONTINUE comes before BREAK then BREAK statement will not be executed because CONTINUE will transfer control to the beginning of while loop to start the execution of the next iteration as given below -
---- Declare local variable
---- Incremental number counter
DECLARE @ictr int=1
---- Variable to print for table
DECLARE @inum int=2
---- While condtion till 100
WHILE @ictr<100
BEGIN
---- multiply number and incremental number
PRINT @inum * @ictr
---- set incremental of number
SET @ictr= @ictr + 1
---- Continue instruction to program
CONTINUE ----
---- break statement if counter reached till 11
IF @ictr=11 ---- -- This will never executed
BEGIN
PRINT 'Break: Counter is now at 11'
---- then break statement
BREAK;
END
END
---- next statement after While loop
PRINT 'Next statement after While loop'
Output Result
2
4
6
8
10
198
Next statement after While loop
Conclusion
While loop is very important feature of SQL server to execute T-SQL statements which are executed repeatedly as long as the specified business condition is true. We can use it for data tables by identifying that if the conditional expression is true, the statement is executed, and then the expression is evaluated again to determine if the statement should be executed again. This process repeats until the expression evaluates to false.

Wednesday, February 21, 2018

TSQL- SQL Query to count poll results

This is very common requirement to generate a poll result in SQL Server. To get the users opinion against any topic or subject, we generally create online polls.
To generate a poll grid or poll results, we store the user input into the database against any poll. We can take a common example to store poll information and user responses into the database as given below:

In Poll Master Table, we have the following data –
Pid
Question
1
Who is the Bollywood Star?
2
How many days in a year?
3
Which number does come before 18?
In Poll Option Table, we could have the multiple options against any specific poll/question as given below –
Pid
Oid
Options
1
1
Virat Kohli
1
2
Rahul Gandhi
1
3
Zac Effron
1
4
Aamir Khan
In User Response Table, a single user can choose only one option against any poll/question and he/she can vote once a while against any poll/question. This table can contain the following data as given below –
Usid
Pid
Oid
1
1
1
1
2
1
2
1
2
2
2
2
Logic to calculate the options percentage against any poll/question:
We have to calculate the followings –
1.       Total Responses = count every response against any Poll question
2.      Option Response =count option response within the same poll question
3.      Percentage = (Option Response/ Total Responses) x 100

Query at a Glance

---- Poll Master Data
;with Poll_Master as
( select Pid=1, Question='Who is the Bollywood Star?'
UNION select Pid=2, Question='How many days in a year?'
UNION select Pid=3, Question='Which number does come before 18?'
),
---- Poll Options data
Poll_Options as
( select Pid=1, Oid=1, Options='Virat Kohli'
union select Pid=1, Oid=2, Options='Rahul Gandhi'
union select Pid=1, Oid=3, Options='Zac Effron'
union select Pid=1, Oid=4, Options='Aamir Khan'
union select Pid=2, Oid=1, Options='367'
union select Pid=2, Oid=2, Options='356'
union select Pid=2, Oid=3, Options='363'
union select Pid=2, Oid=4, Options='365'
union select Pid=3, Oid=1, Options='16'
union select Pid=3, Oid=2, Options='15'
union select Pid=3, Oid=3, Options='17'
union select Pid=3, Oid=4, Options='19'
),
---- User responses against poll options
User_Response as
(
select Usid=1, Pid=1, Oid=
union select Usid=2, Pid=1, Oid=
union select Usid=3, Pid=1, Oid=3
union select Usid=6, Pid=1, Oid=4
union select Usid=1, Pid=2, Oid=
union select Usid=2, Pid=2, Oid=
union select Usid=3, Pid=2, Oid=3
union select Usid=7, Pid=2, Oid=4
union select Usid=5, Pid=1, Oid=
union select Usid=6, Pid=1, Oid=
union select Usid=5, Pid=2, Oid=3
union select Usid=6, Pid=2, Oid=4
union select Usid=7, Pid=1, Oid=
union select Usid=8, Pid=2, Oid=
union select Usid=7, Pid=2, Oid=3
union select Usid=8, Pid=3, Oid=4
)
----- Pull Total Response against every poll
,Total_Response as
(
SELECT O.PID,
Tot_Response=Count(*) from User_Response O
Group By O.Pid
)
  
SELECT PID, Tot_Response,
[Option1]=Sum([Option1]),
[Option2]=Sum([Option2]),
[Option3]=Sum([Option3]),
[Option4]=Sum([Option4])
FROM
(
SELECT PID, Tot_Response,[Option1]=[1],
[Option2]=[2],[Option3]=[3],[Option4]=[4]
FROM (
   ----- Pull the data from above table
SELECT O.PID,O.Oid,
----- Pull all responses
Tot_Response=Isnull( TR.Tot_Response,0),
---- count option responses
Options_Response=Count(R.Pid) ,
---- Percentage = (Option Response/ Total Responses) x 100
Per_Result= Round(Cast(Count(R.Pid) as float)/cast(Isnull( TR.Tot_Response,1) as float)*100,2)
from Poll_Master P
JOIN Poll_Options O On P.Pid=O.Pid
LEFT JOIN User_Response R On O.Pid=R.Pid and O.Oid=R.Oid
LEFT JOIN Total_Response TR on O.Pid=TR.Pid
GROUP BY O.PID,O.Oid,TR.Tot_Response
) as s
----- Using Pivot to convert rows into columns
PIVOT
(
    Sum(Per_Result) FOR [Oid] IN ([1],[2],[3],[4])
)AS pvt
)X
Group by PID, Tot_Response
order by PID
PID
Tot_Response
Option1
Option2
Option3
Option4
1
7
14.29
28.57
14.29
42.86
2
8
12.50
25.00
37.50
25.00
3
1
0.00
0.00
0.00
100.00
Conclusion
To generate the polls results, we should also keep in mind that if any poll does not get any response from user then it should be display on the grid also.

Popular Posts