Introduction to Conditions

A Boolean Value

A value is characterized as Boolean if that value is said to be true or false.

A Comparison of Two Values

A comparison consists of establishing a relationship between two values, such as to find out whether both values are equal or one of them is greater than the other, etc. To perform a comparison, you use two operands and an operator as in the following formula:

operand_1 operator operand_2

This is the basic formula of a Boolean expression.

Practical Learning: Introducing Conditions

1. Start Microsoft SQL Server Management Studio and connect
2. In the Object Explorer, right-click the name of the computer and click New Query
3. In the Query Editor, type:

   DECLARE @discountRate int;
   DECLARE @discountAmount numeric(6, 2);
   DECLARE @discountedPrice numeric(6, 2);
   
   DECLARE @itemName nvarchar(100) = 'Tulip Sleeved Sheath Dress';
   DECLARE @originalPrice numeric(6, 2) = 89.95;
   DECLARE @daysInStore int = 28;
   
   SET @discountAmount  = @originalPrice * @discountRate / 100;
   SET @discountedPrice = @originalPrice - @discountAmount;
   
   SELECT @itemName AS [Item Name];
   SELECT @originalPrice AS [Original Price];
   SELECT @daysInStore AS [Days in Store];
   SELECT @discountRate AS [Discount Rate];
   SELECT @discountAmount AS [Discount Amount];
   SELECT @discountedPrice AS [Discounted Price];
   GO

4. To execute, on the main menu, click Query -> Execute:

   

IF a Condition Applies

To perform a comparison, the primary keyword you use is named IF. The formula to follow is:

IF condition statement[;]

As you can see, you start with the IF keyword. The condition can have the following formula:

operand_1 Boolean-operator operand_2

An operand can be a value or the name of a variable. The operator is a logical one. The whole expression is the condition. If the expression produces a true result, then the statement would execute.

The Body of a Condition Statement

In the above formula, we saw that you start with the IF keyword, add a space, and a condition. The section after that condition is referred to as the body of the conditional statement. If its statement is short, you can write it on the same line with the IF condition. If the statement is long, you should write it on the next line. In that case, to make your code easy to read, the statement should be indented (four character spaces to the right). This would be done as follows:

IF operand_1 Boolean-operator operand_2
    statement[;]

You can also write the statement on its own line even if the IF condition is short.

Beginning and Ending a Body of a Statement

As is the case in many programming languages, a section that contains a statement may need many lines of code. In Transact-SQL, you may be tempted to write those statements below the IF condition and simply indent them as follows:

IF operand_1 Boolean-operator operand_2
    statement_1[;]
    statement_2[;]
    . . .
    statement_n[;]

In the languages that require indentation (such as F#), that indentation would be enough. In other languages such as SQL, if you write such code, only the first line of code below the IF condition would execute; the other lines would be considered as not belonging to the IF condition. In those languages, you must explicitly indicate the Beginning and end of the section.

To indicate the Beginning of a section in Transact-SQL (in fact in most SQL implementations), you must use a keyword named BEGIN. To indicate the end of a section, you must use a keyword named END. To make your code easier to read:

• The BEGIN and the END keywords should written each on its other line
• The lines that contain the BEGIN and the END keywords should be indented with regards to the parent of their section
• The lines of code between the BEGIN and the END lines should be indented with regards to the BEGIN and the END lines

Based on this, if your condition must include many statements, the formula to use becomes:

IF operand_1 Boolean-operator operand_2
    BEGIN
        statement_1[;]
        statement_2[;]
        . . .
        statement_n[;]
    BEGIN

You can also create a body even if the section of code contains only one line of code:

IF operand_1 Boolean-operator operand_2
    BEGIN
        statement[;]
    BEGIN

Fundamentals of Boolean Values

Introduction

A value is said to be Boolean if it is true or it is false. In fact, the two available Boolean values are TRUE and FALSE.

A Boolean Variable

If you want to declare a Boolean variable, you can use a data type named BIT. Here is an example of declaring a Boolean variable:

DECLARE @DrinkingUnderAge BIT

To initialize a Boolean variable or to change its value, assign 0 or another natural number to the variable. Here is an example:

DECLARE @rinkingUnderAge BIT = 1;

If you assign 0, the variable is considered to hold a FALSE value. If you assign any natural number, it would be reduced to 1 and considered to hold a TRUE value. Consider the following code:

1> DECLARE @DrinkingUnderAge BIT = 398;
2> SELECT @DrinkingUnderAge AS [Drinking Under Ager];
3> GO
Drinking Under Ager
-------------------
                  1

(1 rows affected)
1> DECLARE @DrinkingUnderAge BIT = 0;
2> SELECT @DrinkingUnderAge AS [Drinking Under Ager];
3> GO
Drinking Under Ager
-------------------
                  0

(1 rows affected)
1> DECLARE @DrinkingUnderAge BIT = 584952;
2> SELECT @DrinkingUnderAge AS [Drinking Under Ager];
3> GO
Drinking Under Ager
-------------------
                  1

(1 rows affected)

Assigning a Logical Expression to a Boolean Variable

A Boolean variable can be initialized with a Boolean expression. This would be done as follows:

@variable-name = operand_1 Boolean-operator operand_2;

To make your code easy to read, you should include the logical expression in parentheses. This can be done as follows:

@variable-name variable = (operand_1 Boolean-operator operand_2);

Fundamentals of Logical Operators

Introduction

A logical comparison is used to establish the logical relationship between two values, a variable and a value, or two variables. There are various operators available to perform such comparisons.

A Value Less Than Another: <

To find out whether one value is lower than another, the operator to use is <. Its formula is:

value_1 < value_2

This operation can be illustrated as follows:

For a "Less Than" operation, the formula of the conditional statement is:

IF operand_1 < operand_2
    statement(s);

Here is an example:

DECLARE @salary numeric = 36000;
DECLARE @employmentStatus NVARCHAR(12) = N'Full-Time';

IF @salary < 40000
    @employmentStatus = "Part-Time";

SELECT @salary AS [Yearly Salary];
SELECT @employmentStatus AS [Employment Status];
GO

This would produce:

Employee Record
-----------------------------
Yearly Salary:     36000
Employment Status: Full-Time
=============================

Press any key to close this window . . .

Practical Learning: Comparing for a Lesser Value

1. Change the document as follows:

   DECLARE @discountRate int;
   DECLARE @discountAmount numeric(6, 2);
   DECLARE @discountedPrice numeric(6, 2);
   
   DECLARE @itemName nvarchar(100) = 'Tulip Sleeved Sheath Dress';
   DECLARE @originalPrice numeric(6, 2) = 89.95;
   DECLARE @daysInStore int = 28;
   
   IF @daysInStore < 60
       SET @discountRate = 50;
   IF @daysInStore < 45
       SET @discountRate = 35;
   IF @daysInStore < 35
       SET @discountRate = 15;
   IF @daysInStore < 15
       SET @discountRate = 0;
   
   SET @discountAmount  = @originalPrice * @discountRate / 100;
   SET @discountedPrice = @originalPrice - @discountAmount;
   
   SELECT @itemName AS [Item Name],
          @originalPrice AS [Original Price],
          @daysInStore AS [Days in Store],
          @discountRate AS [Discount Rate],
          @discountAmount AS [Discount Amount],
          @discountedPrice AS [Discounted Price];
   GO

2. To execute, on the main menu, click Query -> Execute
3. In the code, change the number of days to 46

   DECLARE @discountRate int;
   DECLARE @discountAmount numeric(6, 2);
   DECLARE @discountedPrice numeric(6, 2);
   
   DECLARE @itemName nvarchar(100) = 'Tulip Sleeved Sheath Dress';
   DECLARE @originalPrice numeric(6, 2) = 89.95;
   DECLARE @daysInStore int = 46;
   
   IF @daysInStore < 60
       SET @discountRate = 50;
   IF @daysInStore < 45
       SET @discountRate = 35;
   IF @daysInStore < 35
       SET @discountRate = 15;
   IF @daysInStore < 15
       SET @discountRate = 0;
   
   SET @discountAmount  = @originalPrice * @discountRate / 100;
   SET @discountedPrice = @originalPrice - @discountAmount;
   
   SELECT @itemName AS [Item Name],
          @originalPrice AS [Original Price],
          @daysInStore AS [Days in Store],
          @discountRate AS [Discount Rate],
          @discountAmount AS [Discount Amount],
          @discountedPrice AS [Discounted Price];
   GO

4. To execute, on the main menu, click Query -> Execute
5. Change the document as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Michael';
   DECLARE @lastName  NVARCHAR(12)  = N'Carlock';
   DECLARE @hSalary   NUMERIC(6, 2) = 28.25;
   
   -- Time worked
   DECLARE @mon DECIMAL(5, 2) = 7;
   DECLARE @tue DECIMAL(5, 2) = 8;
   DECLARE @wed DECIMAL(5, 2) = 6.5;
   DECLARE @thu DECIMAL(5, 2) = 8.5;
   DECLARE @fri DECIMAL(5, 2) = 6.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   DECLARE @netPay     numeric(6, 2) = @hSalary * @timeWorked;
           
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '=======================================================';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                      Pay Summary';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                      Total Time:  ', @timeWorked);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                      Net Pay:     ', @netPay);
   PRINT '=======================================================';
   

6. To execute, on the main menu, click Query -> Execute:

   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   FUN DEPARTMENT STORE
   =======================================================
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Michael Carlock
   Hourly Salary: 28.25
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     7.00  |   8.00  |    6.50   |   8.50   |  6.50
   ========+=========+===========+==========+=============
                         Pay Summary
   -------------------------------------------------------
                         Total Time:  36.50
   -------------------------------------------------------
                         Net Pay:     1031.13
   =======================================================

7. Change the values as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Catherine';
   DECLARE @lastName  NVARCHAR(12)  = N'Busbey';
   DECLARE @hSalary   NUMERIC(6, 2) = 24.37;
   
   -- Time worked
   DECLARE @mon DECIMAL(5, 2) = 9.5;
   DECLARE @tue DECIMAL(5, 2) = 8;
   DECLARE @wed DECIMAL(5, 2) = 10.5;
   DECLARE @thu DECIMAL(5, 2) = 9;
   DECLARE @fri DECIMAL(5, 2) = 10.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   DECLARE @netPay     numeric(6, 2) = @hSalary * @timeWorked;
           
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '=======================================================';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                      Pay Summary';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                      Total Time:  ', @timeWorked);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                      Net Pay:     ', @netPay);
   PRINT '=======================================================';

8. To execute, on the main menu, click Query -> Execute:

   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   FUN DEPARTMENT STORE
   =======================================================
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Catherine Busbey
   Hourly Salary: 24.37
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     9.50  |   8.00  |    10.50   |   9.00   |  10.50
   ========+=========+===========+==========+=============
                         Pay Summary
   -------------------------------------------------------
                         Total Time:  47.50
   -------------------------------------------------------
                         Net Pay:     1157.58

A Value Greater Than Another: >

To find out whether one value is greater than the other, the operator to use is >. Its formula is:

value1 > value2

Both operands, in this case value1 and value2, can be variables or the left operand can be a variable while the right operand is a constant. This operation can be illustrated as follows:

Practical Learning: Finding Out Whether a Value is Greater Than Another

1. Change the document as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Michael';
   DECLARE @lastName  NVARCHAR(12)  = N'Carlock';
   DECLARE @hSalary   NUMERIC(6, 2) = 28.25;
   
   -- Time worked
   DECLARE @mon DECIMAL(5, 2) = 7;
   DECLARE @tue DECIMAL(5, 2) = 8;
   DECLARE @wed DECIMAL(5, 2) = 6.5;
   DECLARE @thu DECIMAL(5, 2) = 8.5;
   DECLARE @fri DECIMAL(5, 2) = 6.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   
   DECLARE @regTime  numeric(6, 2) = @timeWorked;
   DECLARE @overtime numeric(6, 2) = 0.00;
   DECLARE @overPay  numeric(6, 2) = 0.00;
   DECLARE @regPay   numeric(6, 2) = @hSalary * @timeWorked;
           
   IF @timeWorked > 40.00
       BEGIN
           SET @regTime  = 40.00;
           SET @regPay   = @hSalary * 40.00;
           SET @overtime = @timeWorked - 40.00;
           SET @overPay  = @hSalary * 1.50 * @overtime;
       END
   
   DECLARE @netPay numeric(6, 2) = @regPay + @overPay;
           
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '=======================================================';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                                 Time    Pay';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Regular:    ', @regTime, '   ', @regPay);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Overtime:    ', @overtime, '   ', @overPay);
   PRINT '=======================================================';
   PRINT CONCAT('                      Net Pay:          ', @netPay);
   PRINT '=======================================================';

2. To execute the project, press F5:

   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   FUN DEPARTMENT STORE
   =======================================================
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Michael Carlock
   Hourly Salary: 28.25
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     7.00  |   8.00  |    6.50   |   8.50   |  6.50
   ========+=========+===========+==========+=============
                                    Time    Pay
   -------------------------------------------------------
                        Regular:    36.50   1031.13
   -------------------------------------------------------
                        Overtime:    0.00   0.00
   =======================================================
                         Net Pay:          1031.13
   =======================================================

3. Change the values as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Catherine';
   DECLARE @lastName  NVARCHAR(12)  = N'Busbey';
   DECLARE @hSalary   NUMERIC(6, 2) = 24.37;
   
   -- Time worked
   DECLARE @mon DECIMAL(5, 2) = 9.5;
   DECLARE @tue DECIMAL(5, 2) = 8;
   DECLARE @wed DECIMAL(5, 2) = 10.5;
   DECLARE @thu DECIMAL(5, 2) = 9;
   DECLARE @fri DECIMAL(5, 2) = 10.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   
   DECLARE @regTime  numeric(6, 2) = @timeWorked;
   DECLARE @overtime numeric(6, 2) = 0.00;
   DECLARE @overPay  numeric(6, 2) = 0.00;
   DECLARE @regPay   numeric(6, 2) = @hSalary * @timeWorked;
           
   IF @timeWorked > 40.00
       SET @regTime  = 40.00;
       SET @regPay   = @hSalary * 40.00;
       SET @overtime = @timeWorked - 40.00;
       SET @overPay  = @hSalary * 1.50 * @overtime;
   
   DECLARE @netPay numeric(6, 2) = @regPay + @overPay;
           
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '=======================================================';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                                 Time    Pay';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Regular:    ', @regTime, '   ', @regPay);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Overtime:    ', @overtime, '   ', @overPay);
   PRINT '=======================================================';
   PRINT CONCAT('                      Net Pay:          ', @netPay);
   PRINT '=======================================================';

4. Change the values as follows:

   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   FUN DEPARTMENT STORE
   =======================================================
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Catherine Busbey
   Hourly Salary: 24.37
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     9.50  |   8.00  |    10.50   |   9.00   |  10.50
   ========+=========+===========+==========+=============
                                    Time    Pay
   -------------------------------------------------------
                        Regular:    40.00   974.80
   -------------------------------------------------------
                        Overtime:    7.50   274.16
   =======================================================
                         Net Pay:           1248.96
   =======================================================

Conditions for Equality

Introduction

We are now familiar with the ability to find out whether one of two values is higher or lower than the other. In some cases, you want to know whether two values share a similarity.

Practical Learning: Introducing Conditions

1. Start Microsoft SQL Server Management Studio and connect
2. In the Object Explorer, right-click the name of the computer and click New Query
3. Change the document as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Catherine';
   DECLARE @lastName  NVARCHAR(12)  = N'Busbey';
   DECLARE @hSalary   NUMERIC(6, 2) = 24.37;
   
   -- Time worked
   DECLARE @mon DECIMAL(5, 2) = 9.5;
   DECLARE @tue DECIMAL(5, 2) = 8;
   DECLARE @wed DECIMAL(5, 2) = 10.5;
   DECLARE @thu DECIMAL(5, 2) = 9;
   DECLARE @fri DECIMAL(5, 2) = 10.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   
   DECLARE @regTime  numeric(6, 2) = @timeWorked;
   DECLARE @overtime numeric(6, 2) = 0.00;
   DECLARE @overPay  numeric(6, 2) = 0.00;
   DECLARE @regPay   numeric(6, 2) = @hSalary * @timeWorked;
   
   DECLARE @netPay numeric(6, 2) = @regPay + @overPay;
   
   PRINT '=======================================================';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                                 Time    Pay';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Regular:    ', @regTime, '   ', @regPay);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Overtime:    ', @overtime, '   ', @overPay);
   PRINT '=======================================================';
   PRINT CONCAT('                      Net Pay:           ', @netPay);
   PRINT '=======================================================';

4. To execute, on the main menu, click Query -> Execute:

   =======================================================
   FUN DEPARTMENT STORE
   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Catherine Busbey
   Hourly Salary: 24.37
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     9.50  |   8.00  |    10.50   |   9.00   |  10.50
   ========+=========+===========+==========+=============
                                    Time    Pay
   -------------------------------------------------------
                        Regular:    47.50   1157.58
   -------------------------------------------------------
                        Overtime:    0.00   0.00
   =======================================================
                         Net Pay:           1157.58
   =======================================================

5. Change the values in the code as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Michael';
   DECLARE @lastName  NVARCHAR(12)  = N'Carlock';
   DECLARE @hSalary   NUMERIC(6, 2) = 28.25;
   
   -- Time worked
   DECLARE @mon DECIMAL(5, 2) = 7;
   DECLARE @tue DECIMAL(5, 2) = 8;
   DECLARE @wed DECIMAL(5, 2) = 6.5;
   DECLARE @thu DECIMAL(5, 2) = 8.5;
   DECLARE @fri DECIMAL(5, 2) = 6.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   
   DECLARE @regTime  numeric(6, 2) = @timeWorked;
   DECLARE @overtime numeric(6, 2) = 0.00;
   DECLARE @overPay  numeric(6, 2) = 0.00;
   DECLARE @regPay   numeric(6, 2) = @hSalary * @timeWorked;
   
   DECLARE @netPay numeric(6, 2) = @regPay + @overPay;
   
   PRINT '=======================================================';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                                 Time    Pay';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Regular:    ', @regTime, '   ', @regPay);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Overtime:    ', @overtime, '   ', @overPay);
   PRINT '=======================================================';
   PRINT CONCAT('                      Net Pay:           ', @netPay);
   PRINT '=======================================================';

6. To execute, on the main menu, click Query -> Execute:

   =======================================================
   FUN DEPARTMENT STORE
   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Michael Carlock
   Hourly Salary: 28.25
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     7.00  |   8.00  |    6.50   |   8.50   |  6.50
   ========+=========+===========+==========+=============
                                    Time    Pay
   -------------------------------------------------------
                        Regular:    36.50   1031.13
   -------------------------------------------------------
                        Overtime:    0.00   0.00
   =======================================================
                         Net Pay:           1031.13
   =======================================================

The Equality Operator =

To compare two variables for equality, You can use the = operator. The formula to use it is:

value_1 = value_2

The equality operation is used to find out whether two variables (or one variable and a constant) hold the same value. The operation can be illustrated as follows:

Logical Difference

The opposite of the equality operator is to find out whether two values are different. Transact-SQL provides two operators for this operation. They are != and <>. The operation can be illustrated as follows:

A typical Boolean expression involves two operands separated by a logical operator. Both operands must be of the same type. These rules apply to the logical difference. It can be used on numbers, strings, etc. If both operands are different, the operation produces a True result. If they are the exact same, the operation produces False. Here is an example:

1> DECLARE @certification char = 'yes';
2> DECLARE @employmentStatus NVARCHAR(8) = N'Hired';
3> IF @certification != 'y'
4>     BEGIN
5>         SET @employmentStatus = N'This job requires SQL certification. We will get back to you.';
6>     END
7> PRINT '============================================';
8> PRINT CONCAT('Candidate holds SQL certification: ', @certification);
9> PRINT CONCAT('Decision Status:                   ', @employmentStatus);
10> GO
============================================
Candidate holds SQL certification: y
Decision Status:                   Hired

Remember that you can use either the != or the <> operator.

Less Than Or Equal To: <=

The Equality (=) and the Less Than (<) operations can be combined to compare two values. This allows you to know if two values are the same or if the first value is lower than the second value. The operator used is <=. Its syntax is:

value_1 <= value_2

The <= operation performs a comparison. If both value_1 and value_2 hold the same value, the result is True. If the left operand, in this case value_1, holds a value lower than the second operand, in this case value_2, the result is still True. The <= operation can be illustrated as follows:

Practical Learning: Comparing for a Lesser or Equal Value

1. Change the document as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Michael';
   DECLARE @lastName  NVARCHAR(12)  = N'Carlock';
   DECLARE @hSalary   NUMERIC(6, 2) = 28.25;
   
   -- Time worked
   DECLARE @mon DECIMAL(6, 2) = 7;
   DECLARE @tue DECIMAL(6, 2) = 8;
   DECLARE @wed DECIMAL(6, 2) = 6.5;
   DECLARE @thu DECIMAL(6, 2) = 8.5
   DECLARE @fri DECIMAL(6, 2) = 6.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   
   DECLARE @regTime  numeric(6, 2) = 40.00;
   DECLARE @regPay   numeric(6, 2) = @hSalary * 40.00;
   DECLARE @overtime numeric(6, 2) = @timeWorked - 40.00;
   DECLARE @overPay  numeric(6, 2) = @hSalary * 1.50 * @overtime;
   
   IF @timeWorked <= 40.00
       BEGIN
           SET @regTime  = @timeWorked;
           SET @regPay   = @hSalary * @timeWorked;
           SET @overtime = 0.00;
           SET @overPay  = 0.00;
       END
   
   DECLARE @netPay numeric(8, 2) = @regPay + @overPay;
   
   PRINT '=======================================================';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                                 Time    Pay';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Regular:    ', @regTime, '   ', @regPay);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Overtime:    ', @overtime, '   ', @overPay);
   PRINT '=======================================================';
   PRINT CONCAT('                      Net Pay:           ', @netPay);
   PRINT '=======================================================';

2. To execute, press F5:

   =======================================================
   FUN DEPARTMENT STORE
   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Michael Carlock
   Hourly Salary: 28.25
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     7.00  |   8.00  |    6.50   |   8.50   |  6.50
   ========+=========+===========+==========+=============
                                    Time    Pay
   -------------------------------------------------------
                        Regular:    36.50   1031.13
   -------------------------------------------------------
                        Overtime:    0.00   0.00
   =======================================================
                         Net Pay:           1031.13
   =======================================================

3. Change the values as follows:

   DECLARE @firstName NVARCHAR(12)  = N'Catherine';
   DECLARE @lastName  NVARCHAR(12)  = N'Busbey';
   DECLARE @hSalary   NUMERIC(6, 2) = 24.37;
   
   -- Time worked
   DECLARE @mon DECIMAL(6, 2) = 9.5;
   DECLARE @tue DECIMAL(6, 2) = 8;
   DECLARE @wed DECIMAL(6, 2) = 10.5;
   DECLARE @thu DECIMAL(6, 2) = 9
   DECLARE @fri DECIMAL(6, 2) = 10.5;
   
   DECLARE @timeWorked numeric(6, 2) = @mon + @tue + @wed + @thu + @fri;
   
   DECLARE @regTime  numeric(6, 2) = 40.00;
   DECLARE @regPay   numeric(6, 2) = @hSalary * 40.00;
   DECLARE @overtime numeric(6, 2) = @timeWorked - 40.00;
   DECLARE @overPay  numeric(6, 2) = @hSalary * 1.50 * @overtime;
   
   IF @timeWorked <= 40.00
       BEGIN
           SET @regTime  = @timeWorked;
           SET @regPay   = @hSalary * @timeWorked;
           SET @overtime = 0.00;
           SET @overPay  = 0.00;
       END
   
   DECLARE @netPay numeric(8, 2) = @regPay + @overPay;
   
   PRINT '=======================================================';
   PRINT 'FUN DEPARTMENT STORE';
   PRINT '+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+';
   PRINT 'Payroll Evaluation';
   PRINT '=======================================================';
   PRINT 'Employee Information';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('Full Name:     ', @firstName, ' ', @lastName);
   PRINT CONCAT('Hourly Salary: ', @hSalary);
   PRINT '=======================================================';
   PRINT 'Time Worked Summary';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT ' Monday | Tuesday | Wednesday | Thursday | Friday';
   PRINT '--------+---------+-----------+----------+-------------';
   PRINT CONCAT('  ', @mon, '  |   ', @tue, '  |    ', @wed, '   |   ', @thu, '   |  ', @fri);
   PRINT '========+=========+===========+==========+=============';
   PRINT '                                 Time    Pay';
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Regular:    ', @regTime, '   ', @regPay);
   PRINT '-------------------------------------------------------';
   PRINT CONCAT('                     Overtime:    ', @overtime, '   ', @overPay);
   PRINT '=======================================================';
   PRINT CONCAT('                      Net Pay:           ', @netPay);
   PRINT '=======================================================';

4. To execute, press F5:

   =======================================================
   FUN DEPARTMENT STORE
   +=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
   Payroll Evaluation
   =======================================================
   Employee Information
   -------------------------------------------------------
   Full Name:     Catherine Busbey
   Hourly Salary: 24.37
   =======================================================
   Time Worked Summary
   --------+---------+-----------+----------+-------------
    Monday | Tuesday | Wednesday | Thursday | Friday
   --------+---------+-----------+----------+-------------
     9.50  |   8.00  |    10.50   |   9.00   |  10.50
   ========+=========+===========+==========+=============
                                    Time    Pay
   -------------------------------------------------------
                        Regular:    40.00   974.80
   -------------------------------------------------------
                        Overtime:    7.50   274.16
   =======================================================
                         Net Pay:           1248.96
   =======================================================

5. Change the document as follows:

   DECLARE @machineCost             numeric(8, 2)  = 8568.95;
   DECLARE @salvageValue            numeric(8, 2)  = 550;
   DECLARE @estimatedLife           int            = 5;
   
   DECLARE @depreciationRate         numeric(8, 2) = 100 / @estimatedLife;
   DECLARE @yearlyDepreciation       numeric(8, 2) = (@machineCost - @salvageValue) / @estimatedLife;
   
   DECLARE @bookValueYear0           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  0);
   DECLARE @bookValueYear1           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  1);
   DECLARE @bookValueYear2           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  2);
   DECLARE @bookValueYear3           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  3);
   DECLARE @bookValueYear4           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  4);
   DECLARE @bookValueYear5           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  5);
   DECLARE @bookValueYear6           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  6);
   DECLARE @bookValueYear7           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  7);
   DECLARE @bookValueYear8           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  8);
   DECLARE @bookValueYear9           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  9);
   DECLARE @bookValueYear10          numeric(8, 2) = @machineCost - (@yearlyDepreciation * 10);
   
   DECLARE @accumulatedDepreciation1  numeric(8, 2) = @yearlyDepreciation *  1;
   DECLARE @accumulatedDepreciation2  numeric(8, 2) = @yearlyDepreciation *  2;
   DECLARE @accumulatedDepreciation3  numeric(8, 2) = @yearlyDepreciation *  3;
   DECLARE @accumulatedDepreciation4  numeric(8, 2) = @yearlyDepreciation *  4;
   DECLARE @accumulatedDepreciation5  numeric(8, 2) = @yearlyDepreciation *  5;
   DECLARE @accumulatedDepreciation6  numeric(8, 2) = @yearlyDepreciation *  6;
   DECLARE @accumulatedDepreciation7  numeric(8, 2) = @yearlyDepreciation *  7;
   DECLARE @accumulatedDepreciation8  numeric(8, 2) = @yearlyDepreciation *  8;
   DECLARE @accumulatedDepreciation9  numeric(8, 2) = @yearlyDepreciation *  9;
   DECLARE @accumulatedDepreciation10 numeric(8, 2) = @yearlyDepreciation * 10;
   
   PRINT '===================================================================';
   PRINT 'Machine Depreciation Evaluation - Straight-Line Method';
   PRINT '===================================================================';
   PRINT CONCAT('Machine Cost:         ', @machineCost);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Salvage Calue:        ', @salvageValue);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Estimated Life:       ', @estimatedLife, ' years');
   PRINT '===================================================================';
   PRINT CONCAT('Depreciation Rate:    ', @depreciationRate, '%');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Yearly Depreciation:  ', @yearlyDepreciation, '/year');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Monthly Depreciation: ', (@yearlyDepreciation / 12), '/month');
   PRINT '=====+=====================+============+==========================';
   PRINT 'Year | Yearly Depreciation | Book Value | Accumulated Depreciation';
   PRINT '-----+---------------------+------------+--------------------------';
   
   DECLARE @year int = 0;
   
   PRINT CONCAT(@year, '    |                     |  ', @bookValueYear0, '   |');
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 1, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear1, '   |        ', @accumulatedDepreciation1);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 2, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear2, '   |        ', @accumulatedDepreciation2);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 3, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear3, '   |        ', @accumulatedDepreciation3);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 4, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear4, '   |        ', @accumulatedDepreciation4);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 5, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear5, '    |        ', @accumulatedDepreciation5);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 6, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear6, '  |        ', @accumulatedDepreciation6);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 7, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear7, '  |        ', @accumulatedDepreciation7);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 8, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear8, '  |        ', @accumulatedDepreciation8);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 9, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear9, '  |        ', @accumulatedDepreciation9);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 10, '   |        ', @yearlyDepreciation, '      |  ', @bookValueYear10,  '  |        ', @accumulatedDepreciation10); 
   PRINT '=====+=====================+============+==========================';

6. To execute, on the main menu, click Query -> Execute:

   ===================================================================
   Machine Depreciation Evaluation - Straight-Line Method
   ===================================================================
   Machine Cost:         8568.95
   -------------------------------------------------------------------
   Salvage Calue:        550.00
   -------------------------------------------------------------------
   Estimated Life:       5 years
   ===================================================================
   Depreciation Rate:    20.00%
   -------------------------------------------------------------------
   Yearly Depreciation:  1603.79/year
   -------------------------------------------------------------------
   Monthly Depreciation: 133.649166/month
   =====+=====================+============+==========================
   Year | Yearly Depreciation | Book Value | Accumulated Depreciation
   -----+---------------------+------------+--------------------------
   0    |                     |  8568.95   |
   -----+---------------------+------------+--------------------------
   1    |        1603.79      |  6965.16   |        1603.79
   -----+---------------------+------------+--------------------------
   2    |        1603.79      |  5361.37   |        3207.58
   -----+---------------------+------------+--------------------------
   3    |        1603.79      |  3757.58   |        4811.37
   -----+---------------------+------------+--------------------------
   4    |        1603.79      |  2153.79   |        6415.16
   -----+---------------------+------------+--------------------------
   5    |        1603.79      |  550.00    |        8018.95
   -----+---------------------+------------+--------------------------
   6    |        1603.79      |  -1053.79  |        9622.74
   -----+---------------------+------------+--------------------------
   7    |        1603.79      |  -2657.58  |        11226.53
   -----+---------------------+------------+--------------------------
   8    |        1603.79      |  -4261.37  |        12830.32
   -----+---------------------+------------+--------------------------
   9    |        1603.79      |  -5865.16  |        14434.11
   -----+---------------------+------------+--------------------------
   10   |        1603.79      |  -7468.95  |        16037.90
   =====+=====================+============+==========================

7. Change the values of Machine Cost, Salvage Value, and Estimated Life as follows:

   DECLARE @machineCost             numeric(8, 2)  = 15888.65;
   DECLARE @salvageValue            numeric(8, 2)  = 1250;
   DECLARE @estimatedLife           int            = 10;
   
   DECLARE @depreciationRate         numeric(8, 2) = 100 / @estimatedLife;
   DECLARE @yearlyDepreciation       numeric(8, 2) = (@machineCost - @salvageValue) / @estimatedLife;
   
   DECLARE @bookValueYear0           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  0);
   DECLARE @bookValueYear1           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  1);
   DECLARE @bookValueYear2           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  2);
   DECLARE @bookValueYear3           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  3);
   DECLARE @bookValueYear4           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  4);
   DECLARE @bookValueYear5           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  5);
   DECLARE @bookValueYear6           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  6);
   DECLARE @bookValueYear7           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  7);
   DECLARE @bookValueYear8           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  8);
   DECLARE @bookValueYear9           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  9);
   DECLARE @bookValueYear10          numeric(8, 2) = @machineCost - (@yearlyDepreciation * 10);
   
   DECLARE @accumulatedDepreciation1  numeric(8, 2) = @yearlyDepreciation *  1;
   DECLARE @accumulatedDepreciation2  numeric(8, 2) = @yearlyDepreciation *  2;
   DECLARE @accumulatedDepreciation3  numeric(8, 2) = @yearlyDepreciation *  3;
   DECLARE @accumulatedDepreciation4  numeric(8, 2) = @yearlyDepreciation *  4;
   DECLARE @accumulatedDepreciation5  numeric(8, 2) = @yearlyDepreciation *  5;
   DECLARE @accumulatedDepreciation6  numeric(8, 2) = @yearlyDepreciation *  6;
   DECLARE @accumulatedDepreciation7  numeric(8, 2) = @yearlyDepreciation *  7;
   DECLARE @accumulatedDepreciation8  numeric(8, 2) = @yearlyDepreciation *  8;
   DECLARE @accumulatedDepreciation9  numeric(8, 2) = @yearlyDepreciation *  9;
   DECLARE @accumulatedDepreciation10 numeric(8, 2) = @yearlyDepreciation * 10;
   
   PRINT '===================================================================';
   PRINT 'Machine Depreciation Evaluation - Straight-Line Method';
   PRINT '===================================================================';
   PRINT CONCAT('Machine Cost:         ', @machineCost);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Salvage Calue:        ', @salvageValue);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Estimated Life:       ', @estimatedLife, ' years');
   PRINT '===================================================================';
   PRINT CONCAT('Depreciation Rate:    ', @depreciationRate, '%');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Yearly Depreciation:  ', @yearlyDepreciation, '/year');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Monthly Depreciation: ', (@yearlyDepreciation / 12), '/month');
   PRINT '=====+=====================+============+==========================';
   PRINT 'Year | Yearly Depreciation | Book Value | Accumulated Depreciation';
   PRINT '-----+---------------------+------------+--------------------------';
   
   DECLARE @year int = 0;
   
   PRINT CONCAT(@year, '    |                     |  ', @bookValueYear0, '  |');
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 1, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear1, '  |        ', @accumulatedDepreciation1);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 2, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear2, '  |        ', @accumulatedDepreciation2);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 3, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear3, '  |        ', @accumulatedDepreciation3);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 4, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear4, '  |        ', @accumulatedDepreciation4);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 5, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear5, '   |        ', @accumulatedDepreciation5);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 6, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear6, '   |        ', @accumulatedDepreciation6);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 7, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear7, '   |        ', @accumulatedDepreciation7);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 8, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear8, '   |        ', @accumulatedDepreciation8);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 9, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear9, '   |        ', @accumulatedDepreciation9);
   PRINT '-----+---------------------+------------+--------------------------';
   PRINT CONCAT(@year + 10, '   |        ', @yearlyDepreciation, '      |  ', @bookValueYear10,  '   |        ', @accumulatedDepreciation10);
   PRINT '=====+=====================+============+==========================';

8. To execute again, on the main menu, click Query -> Execute:

   ===================================================================
   Machine Depreciation Evaluation - Straight-Line Method
   ===================================================================
   Machine Cost:         15888.65
   -------------------------------------------------------------------
   Salvage Calue:        1250.00
   -------------------------------------------------------------------
   Estimated Life:       10 years
   ===================================================================
   Depreciation Rate:    10.00%
   -------------------------------------------------------------------
   Yearly Depreciation:  1463.87/year
   -------------------------------------------------------------------
   Monthly Depreciation: 121.989166/month
   =====+=====================+============+==========================
   Year | Yearly Depreciation | Book Value | Accumulated Depreciation
   -----+---------------------+------------+--------------------------
   0    |                     |  15888.65  |
   -----+---------------------+------------+--------------------------
   1    |        1463.87      |  14424.78  |        1463.87
   -----+---------------------+------------+--------------------------
   2    |        1463.87      |  12960.91  |        2927.74
   -----+---------------------+------------+--------------------------
   3    |        1463.87      |  11497.04  |        4391.61
   -----+---------------------+------------+--------------------------
   4    |        1463.87      |  10033.17  |        5855.48
   -----+---------------------+------------+--------------------------
   5    |        1463.87      |  8569.30   |        7319.35
   -----+---------------------+------------+--------------------------
   6    |        1463.87      |  7105.43   |        8783.22
   -----+---------------------+------------+--------------------------
   7    |        1463.87      |  5641.56   |        10247.09
   -----+---------------------+------------+--------------------------
   8    |        1463.87      |  4177.69   |        11710.96
   -----+---------------------+------------+--------------------------
   9    |        1463.87      |  2713.82   |        13174.83
   -----+---------------------+------------+--------------------------
   10   |        1463.87      |  1249.95   |        14638.70
   =====+=====================+============+==========================

A Value Greater Than or Equal to Another: >=

The greater than or the equality operators can be combined to produce an operator as follows: >=. This is the "greater than or equal to" operator. The formula to follow is:

value_1 >= value_2

This operation can be illustrated as follows:

Practical Learning: Comparing for a Value Greater Than or Equal to Another

1. Change the document as follows:

   DECLARE @machineCost             numeric(8, 2)  = 8568.95;
   DECLARE @salvageValue            numeric(8, 2)  = 550;
   DECLARE @estimatedLife           int            = 5;
   
   DECLARE @depreciationRate         numeric(8, 2) = 100 / @estimatedLife;
   DECLARE @yearlyDepreciation       numeric(8, 2) = (@machineCost - @salvageValue) / @estimatedLife;
   
   DECLARE @bookValueYear0           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  0);
   DECLARE @bookValueYear1           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  1);
   DECLARE @bookValueYear2           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  2);
   DECLARE @bookValueYear3           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  3);
   DECLARE @bookValueYear4           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  4);
   DECLARE @bookValueYear5           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  5);
   DECLARE @bookValueYear6           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  6);
   DECLARE @bookValueYear7           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  7);
   DECLARE @bookValueYear8           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  8);
   DECLARE @bookValueYear9           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  9);
   DECLARE @bookValueYear10          numeric(8, 2) = @machineCost - (@yearlyDepreciation * 10);
   
   DECLARE @accumulatedDepreciation1  numeric(8, 2) = @yearlyDepreciation *  1;
   DECLARE @accumulatedDepreciation2  numeric(8, 2) = @yearlyDepreciation *  2;
   DECLARE @accumulatedDepreciation3  numeric(8, 2) = @yearlyDepreciation *  3;
   DECLARE @accumulatedDepreciation4  numeric(8, 2) = @yearlyDepreciation *  4;
   DECLARE @accumulatedDepreciation5  numeric(8, 2) = @yearlyDepreciation *  5;
   DECLARE @accumulatedDepreciation6  numeric(8, 2) = @yearlyDepreciation *  6;
   DECLARE @accumulatedDepreciation7  numeric(8, 2) = @yearlyDepreciation *  7;
   DECLARE @accumulatedDepreciation8  numeric(8, 2) = @yearlyDepreciation *  8;
   DECLARE @accumulatedDepreciation9  numeric(8, 2) = @yearlyDepreciation *  9;
   DECLARE @accumulatedDepreciation10 numeric(8, 2) = @yearlyDepreciation * 10;
   
   PRINT '===================================================================';
   PRINT 'Machine Depreciation Evaluation - Straight-Line Method';
   PRINT '===================================================================';
   PRINT CONCAT('Machine Cost:         ', @machineCost);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Salvage Calue:        ', @salvageValue);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Estimated Life:       ', @estimatedLife, ' years');
   PRINT '===================================================================';
   PRINT CONCAT('Depreciation Rate:    ', @depreciationRate, '%');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Yearly Depreciation:  ', @yearlyDepreciation, '/year');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Monthly Depreciation: ', (@yearlyDepreciation / 12), '/month');
   PRINT '=====+=====================+============+==========================';
   PRINT 'Year | Yearly Depreciation | Book Value | Accumulated Depreciation';
   PRINT '-----+---------------------+------------+--------------------------';
   
   DECLARE @year int = 0;
   
   PRINT CONCAT(@year, '    |                     |  ', @bookValueYear0, '   |');
   PRINT '-----+---------------------+------------+--------------------------';
   
   IF @bookValueYear1 >= 0
       BEGIN
           PRINT CONCAT(@year + 1, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear1, '   |        ', @accumulatedDepreciation1);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear2 >= 0
       BEGIN
           PRINT CONCAT(@year + 2, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear2, '   |        ', @accumulatedDepreciation2);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear3 >= 0
       BEGIN
           PRINT CONCAT(@year + 3, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear3, '   |        ', @accumulatedDepreciation3);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear4 >= 0
       BEGIN
           PRINT CONCAT(@year + 4, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear4, '   |        ', @accumulatedDepreciation4);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear5 >= 0
       BEGIN
           PRINT CONCAT(@year + 5, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear5, '    |        ', @accumulatedDepreciation5);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear6 >= 0
       BEGIN
           PRINT CONCAT(@year + 6, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear6, '    |        ', @accumulatedDepreciation6);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear7 >= 0
       BEGIN
           PRINT CONCAT(@year + 7, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear7, '    |        ', @accumulatedDepreciation7);
       PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear8 >= 0
       BEGIN
           PRINT CONCAT(@year + 8, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear8, '    |        ', @accumulatedDepreciation8);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear9 >= 0
       BEGIN
           PRINT CONCAT(@year + 9, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear9, '    |        ', @accumulatedDepreciation9);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear10 >= 0
       BEGIN
           PRINT CONCAT(@year + 10, '   |        ', @yearlyDepreciation, '      |  ', @bookValueYear10,  '    |        ', @accumulatedDepreciation10);
           PRINT '=====+=====================+============+==========================';
       END

2. To execute, on the main menu, click Query -> Execute:

   ===================================================================
   Machine Depreciation Evaluation - Straight-Line Method
   ===================================================================
   Machine Cost:         8568.95
   -------------------------------------------------------------------
   Salvage Calue:        550.00
   -------------------------------------------------------------------
   Estimated Life:       5 years
   ===================================================================
   Depreciation Rate:    20.00%
   -------------------------------------------------------------------
   Yearly Depreciation:  1603.79/year
   -------------------------------------------------------------------
   Monthly Depreciation: 133.649166/month
   =====+=====================+============+==========================
   Year | Yearly Depreciation | Book Value | Accumulated Depreciation
   -----+---------------------+------------+--------------------------
   0    |                     |  8568.95   |
   -----+---------------------+------------+--------------------------
   1    |        1603.79      |  6965.16   |        1603.79
   -----+---------------------+------------+--------------------------
   2    |        1603.79      |  5361.37   |        3207.58
   -----+---------------------+------------+--------------------------
   3    |        1603.79      |  3757.58   |        4811.37
   -----+---------------------+------------+--------------------------
   4    |        1603.79      |  2153.79   |        6415.16
   -----+---------------------+------------+--------------------------
   5    |        1603.79      |  550.00    |        8018.95
   -----+---------------------+------------+--------------------------

3. Change the document as follows:

   DECLARE @machineCost             numeric(8, 2)  = 15888.65;
   DECLARE @salvageValue            numeric(8, 2)  = 1250;
   DECLARE @estimatedLife           int            = 10;
   
   DECLARE @depreciationRate         numeric(8, 2) = 100 / @estimatedLife;
   DECLARE @yearlyDepreciation       numeric(8, 2) = (@machineCost - @salvageValue) / @estimatedLife;
   
   DECLARE @bookValueYear0           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  0);
   DECLARE @bookValueYear1           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  1);
   DECLARE @bookValueYear2           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  2);
   DECLARE @bookValueYear3           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  3);
   DECLARE @bookValueYear4           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  4);
   DECLARE @bookValueYear5           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  5);
   DECLARE @bookValueYear6           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  6);
   DECLARE @bookValueYear7           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  7);
   DECLARE @bookValueYear8           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  8);
   DECLARE @bookValueYear9           numeric(8, 2) = @machineCost - (@yearlyDepreciation *  9);
   DECLARE @bookValueYear10          numeric(8, 2) = @machineCost - (@yearlyDepreciation * 10);
   
   DECLARE @accumulatedDepreciation1  numeric(8, 2) = @yearlyDepreciation *  1;
   DECLARE @accumulatedDepreciation2  numeric(8, 2) = @yearlyDepreciation *  2;
   DECLARE @accumulatedDepreciation3  numeric(8, 2) = @yearlyDepreciation *  3;
   DECLARE @accumulatedDepreciation4  numeric(8, 2) = @yearlyDepreciation *  4;
   DECLARE @accumulatedDepreciation5  numeric(8, 2) = @yearlyDepreciation *  5;
   DECLARE @accumulatedDepreciation6  numeric(8, 2) = @yearlyDepreciation *  6;
   DECLARE @accumulatedDepreciation7  numeric(8, 2) = @yearlyDepreciation *  7;
   DECLARE @accumulatedDepreciation8  numeric(8, 2) = @yearlyDepreciation *  8;
   DECLARE @accumulatedDepreciation9  numeric(8, 2) = @yearlyDepreciation *  9;
   DECLARE @accumulatedDepreciation10 numeric(8, 2) = @yearlyDepreciation * 10;
   
   PRINT '===================================================================';
   PRINT 'Machine Depreciation Evaluation - Straight-Line Method';
   PRINT '===================================================================';
   PRINT CONCAT('Machine Cost:         ', @machineCost);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Salvage Calue:        ', @salvageValue);
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Estimated Life:       ', @estimatedLife, ' years');
   PRINT '===================================================================';
   PRINT CONCAT('Depreciation Rate:    ', @depreciationRate, '%');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Yearly Depreciation:  ', @yearlyDepreciation, '/year');
   PRINT '-------------------------------------------------------------------';
   PRINT CONCAT('Monthly Depreciation: ', (@yearlyDepreciation / 12), '/month');
   PRINT '=====+=====================+============+==========================';
   PRINT 'Year | Yearly Depreciation | Book Value | Accumulated Depreciation';
   PRINT '-----+---------------------+------------+--------------------------';
   
   DECLARE @year int = 0;
   
   PRINT CONCAT(@year, '    |                     |  ', @bookValueYear0, '  |');
   PRINT '-----+---------------------+------------+--------------------------';
   
   IF @bookValueYear1 >= 0
       BEGIN
           PRINT CONCAT(@year + 1, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear1, '  |        ', @accumulatedDepreciation1);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear2 >= 0
       BEGIN
           PRINT CONCAT(@year + 2, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear2, '  |        ', @accumulatedDepreciation2);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear3 >= 0
       BEGIN
           PRINT CONCAT(@year + 3, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear3, '  |        ', @accumulatedDepreciation3);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear4 >= 0
       BEGIN
           PRINT CONCAT(@year + 4, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear4, '  |        ', @accumulatedDepreciation4);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear5 >= 0
       BEGIN
           PRINT CONCAT(@year + 5, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear5, '   |        ', @accumulatedDepreciation5);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear6 >= 0
       BEGIN
           PRINT CONCAT(@year + 6, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear6, '   |        ', @accumulatedDepreciation6);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear7 >= 0
       BEGIN
           PRINT CONCAT(@year + 7, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear7, '   |        ', @accumulatedDepreciation7);
       PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear8 >= 0
       BEGIN
           PRINT CONCAT(@year + 8, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear8, '   |        ', @accumulatedDepreciation8);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear9 >= 0
       BEGIN
           PRINT CONCAT(@year + 9, '    |        ', @yearlyDepreciation, '      |  ', @bookValueYear9, '   |        ', @accumulatedDepreciation9);
           PRINT '-----+---------------------+------------+--------------------------';
       END
   IF @bookValueYear10 >= 0
       BEGIN
           PRINT CONCAT(@year + 10, '   |        ', @yearlyDepreciation, '      |  ', @bookValueYear10,  '   |        ', @accumulatedDepreciation10);
           PRINT '=====+=====================+============+==========================';
       END

4. To close Microsoft SQL Server, on the main menu, click File Exit.
   If you are asked whether you want to save something, click No

Previous	Copyright © 2007-2025, FunctionX	Last Update: Sunday 06 April 2025, 14:35	Next