Notes

Enigmadice is a luck based dice game played between two teams.
The game involves throwing a nine faced dice, each face with a certain score or outcome.
The team scoring the most amount of points wins the game.

The rules of the game are as follows:

- It is a turn based game. The first team will play the entire game followed by the second teams turn. In the end the points will be calculated
and the result will be declared.
- The nine faces of the dice are: 0,1,2,3,4,5,6,extra,penalty.
- A game is divided into 5 phases. Each phase involves 6 legal dice rolls.
- A legal dice roll means that the dice will be rolled only once during that turn.
- So in total, a team will have 30 legal dice rolls. (5 phases x 6 rolls per phase)
- There can be total 8 legal outcomes of a dice roll and 1 extra outcome with special conditions.
- The faces 0,1,2,3,4,5,6,penalty falls under the legal rolls.
- Rolling any of the number between 0 to 6 will simply add that number to the points table.
- Rolling penalty will add 0 to the points table during the game. But after the game, the total number of penalties will be subtracted from the
total points scored.
- Apart from these 8 legal rolls, the dice roll can result in a outcome known as extra.
- An extra will not be counted as a legal roll. In case of extra, the team will roll a 7 faced dice with 0 to 6 as outcome. (This entire process
will not be counted as a turn)
- That number (between 0 to 6) will be added to the total points but the entire turn will not be counted as a legal turn.
- Apart from that number, each extra roll will also result in addition of 1 point.
- So a phase will end after 6 legal rolls. But including extra, a phase can have more than 6 rolls as extra is not a legal roll.


Refer to the tables teamA_score and teamB_score.
The tables provided to you has your username prefixed to the table name.
For example, if your SQL Server username is HRM1746EV, the name of the tables provided to you will be: H_R_M_1_7_4_6_E_V_teamA_score and
H_R_M_1_7_4_6_E_V_teamB_score.

These two tables contains the score of teamA and teamB during the five phases. Both the tables follow the same schema.

The tables contain three columns move_number, points_scored and roll_type.
The column move_number contains the total number of moves thrown during the five phases of the game.
The points_scored column contains the points scored during that game.
The roll_type column contains the type of roll. Both legal and penalty falls under the legal roll and will be counted. The mechanics of extra rolls
are same as explained in the rules.

Your objective is to create three result tables: teamA_score_result, teamB_score_result, and final_game_result

The tables teamA_score_result and teamB_score_result will have the same schema.
These two tables will have the following columns: phase_no, total_points, penalty_count, final_phase_score

Your objective is to the divide the move_number into 5 phases (5 partitions) based on the roll_type and calculate the total points, penalty count
and final score per phase.
So your teamA_score_result table and teamB_score_result table should have 5 rows each representing a phase.

Create a dynamic stored procedure called sp_calculate_team_score to insert the data into your 2 result tables.
The procedure should take 2 input parameters: score table provided to you from which you are taking the data and the score_result table in which
the final result is being inserted.

Sample Output teamA_score_result :

phase_no total_points penalty_count final_phase_score
1 19 1 18

Explaination: A phase ends when 6 legal rolls are made. In the teamA_score table, the phase 1 ends at move_number 8
as the 6th legal move is made at the move_number 8. This is because roll_type extra is not counted as move and only legal and penalty are included
in the count of legal moves.
So till move_number 8, the total_points = 1+0+3+0+6+6+0+1+extra+extra = 19 (each extra is equal to 1 point).
penalty_count = 1 at move_number 7. final_phase_score = 19-1 = 18



Sample Output teamB_score_result :

phase_no total_points penalty_count final_phase_score
3 28 0 28


Now after creating the above two score_result tables, you are required to create a final table called game_result.
This game_result column will have a single column called result and will have a single row as output.

If the total final_phase_score of teamA > teamB then the result column will display: The winner of the game is teamA with a margin of x points.
If the total final_phase_score of teamA < teamB then the result column will display: The winner of the game is teamB with a margin of x points.
If the total final_phase_score of teamA = teamB then the result column will display: The game ended in a tie.

(x here is the difference of the total final_phase_score between the two teams.)

You are supposed to create a Stored Procedure called sp_generate_result_table.
This stored procedure should be used to execute the sp_calculate_team_score procedure and then to insert the data in the game_result table.

Please keep object name as follows:
Stored Procedures -> sp_calculate_team_score, sp_generate_result_table
Tables -> teamA_score_result, teamB_score_result, game_result

(Tags: Dynamic SQL,Window Functions,Joins,Stored Procedure,CTE)


*/
GO