What Else?

Introduction

We saw that the primary way to address a condition in code is with the help of the IF operator. As it happens, there could be many other options to consider when trying to solve a problem.

Practical LearningPractical Learning: Introducing Conditions

  1. Start Microsoft SQL Server Management Studio and connect
  2. On the Object Explorer, right-click the name of the computer and click New Query
  3. In the empty document, type: 
    -- Stellar Water Point
/* Types of Accounts
OTH - Other
BUS - General Business
RES - Residential Household
SGO - Social/Government/Non-Profit Organization
UUO - Unidentified or Unclassified Type of Organization
WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc */

DECLARE @acntNbr             NVarChar(15) = N'9279-570-8394';
DECLARE @type                nchar(3) = N'RES';
DECLARE @counterReadingStart numeric  = 5205;
DECLARE @counterReadingEnd   numeric  = 5222;

DECLARE @consumption int = @counterReadingEnd - @counterReadingStart;
DECLARE @HCFTotal numeric(10, 2) = @consumption * 748.05;
DECLARE @gallons numeric = @consumption * 748.05;;

DECLARE @firstTier numeric(10, 2) = @HCFTotal * 41.50 / 10000.00;
DECLARE @secondTier numeric(10, 2) = @HCFTotal * 32.50 / 10000.00;
DECLARE @lastTier numeric(10, 2) = @HCFTotal * 26.00 / 10000.00;

DECLARE @waterCharges numeric(10, 2) = @firstTier + @secondTier + @lastTier;
DECLARE @sewerCharges numeric(10, 2) = @waterCharges * 6.826941 / 100;

DECLARE @environmentCharges numeric(10, 2) = @waterCharges * 0.022724;
DECLARE @serviceCharges numeric(10, 2) = @waterCharges * 0.145748;

DECLARE @totalCharges numeric(10, 2) = @waterCharges + @sewerCharges + @environmentCharges + @serviceCharges;

DECLARE @localTaxes numeric(10, 2) = @totalCharges * 0.031574;
DECLARE @stateTaxes numeric(10, 2) = @totalCharges * 0.016724;

DECLARE @amountDue numeric(10, 2) = @totalCharges + @localTaxes + @stateTaxes;

PRINT '======================================================';
PRINT 'Stellar Water Point - Customer Invoice';
PRINT '------------------------------------------------------';
PRINT CONCAT('Account Number:             ', @acntNbr);
PRINT '======================================================';
PRINT 'Meter Reading';
PRINT '------------------------------------------------------';
PRINT CONCAT('Counter Reading Start:      ', @counterReadingStart);
PRINT CONCAT('Counter Reading End:        ', @counterReadingEnd);
PRINT CONCAT('Total Gallons Consumed:     ', @consumption);
PRINT CONCAT('HCF Total                   ', @HCFTotal);
PRINT CONCAT('Gallons                     ', @gallons);
PRINT '======================================================';
PRINT 'Therms Evaluation';
PRINT '------------------------------------------------------';
PRINT CONCAT('First Tier Consumption:     ', @firstTier);
PRINT CONCAT('Second Tier Consumption:    ', @secondTier);
PRINT CONCAT('Last Tier Consumption:      ', @lastTier);
PRINT '------------------------------------------------------';
PRINT CONCAT('Water Use Charges:          ', @waterCharges);
PRINT CONCAT('Sewer Charges:              ', @sewerCharges);
PRINT '======================================================';
PRINT 'Bill Values';
PRINT '------------------------------------------------------';
PRINT CONCAT('Environment Charges:        ', @environmentCharges);
PRINT CONCAT('Service Charges:            ', @serviceCharges);
PRINT CONCAT('Total Charges:              ', @totalCharges);
PRINT CONCAT('Local Taxes:                ', @localTaxes);
PRINT CONCAT('State Taxes:                ', @stateTaxes);
PRINT '------------------------------------------------------';
PRINT CONCAT('Amount Due:                 ', @amountDue);
PRINT '======================================================';
  4. To execute, on the main menu, click Query -> Execute: 
    ======================================================
Stellar Water Point - Customer Invoice
------------------------------------------------------
Account Number:             9279-570-8394
======================================================
Meter Reading
------------------------------------------------------
Counter Reading Start:      5205
Counter Reading End:        5222
Total Gallons Consumed:     17
HCF Total                   12716.85
Gallons                     12717
======================================================
Therms Evaluation
------------------------------------------------------
First Tier Consumption:     52.77
Second Tier Consumption:    41.33
Last Tier Consumption:      33.06
------------------------------------------------------
Water Use Charges:          127.16
Sewer Charges:              8.68
======================================================
Bill Values
------------------------------------------------------
Environment Charges:        2.89
Service Charges:            18.53
Total Charges:              157.26
Local Taxes:                4.97
State Taxes:                2.63
------------------------------------------------------
Amount Due:                 164.86
======================================================

Introduction to IF...ELSE Conditions

If you use an IF condition to perform an operation and if the result is true, we saw that you could execute the statement. Any other result would be ignored. To address an alternative to an IF condition, you can use a keyword named ELSE. The formula to use it is:

IF condition statement_1;
ELSE statement_2;

As you can see, the new section uses the ELSE keyword. That section must come after an IF clause. That section doesn't have a condition, only a statement. If its

As seen with the IF conditional statement, if statement_2 is short, you can write it on the same line with the ELSE condition. Here is an example:

DECLARE @value numeric(6, 2);
DECLARE @choice nvarchar(12) = N'Residential';

IF @choice = N'Residential' SET @value = 16.55;
ELSE SET @value = 19.25;

PRINT 'Account Type: Residential';
PRINT CONCAT('Value:        ', @value);
PRINT '==================================';

This would produce:

Account Type: Residential
Value:        16.55
==================================

To make your code easy to read, you should write statement_2 on its own line.

Practical LearningPractical Learning: Introducing IF...ELSE Condition

  1. Change the document as follows: 
    -- Stellar Water Point
/* Types of Accounts
OTH - Other
BUS - General Business
RES - Residential Household
SGO - Social/Government/Non-Profit Organization
UUO - Unidentified or Unclassified Type of Organization
WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc */

DECLARE @acntNbr             NVarChar(15) = N'6003-386-3955';
DECLARE @type                nchar(3) = N'OTH';
DECLARE @counterReadingStart numeric  = 5205;
DECLARE @counterReadingEnd   numeric  = 5222;

DECLARE @consumption int = @counterReadingEnd - @counterReadingStart;
DECLARE @HCFTotal numeric(10, 2) = @consumption * 748.05;
DECLARE @gallons numeric = @consumption * 748.05;;

DECLARE @firstTier numeric(10, 2);
DECLARE @secondTier numeric(10, 2);
DECLARE @lastTier numeric(10, 2);

DECLARE @sewerCharges numeric(10, 2);
DECLARE @environmentCharges numeric(10, 2);
DECLARE @serviceCharges numeric(10, 2);

IF @type = N'RES'
    SET @firstTier = @HCFTotal * 41.50 / 10000.00;
ELSE
    SET @firstTier = @HCFTotal * 48.00 / 10000.00;

IF @type = N'RES'
    SET @secondTier = @HCFTotal * 32.50 / 10000.00;
ELSE
    SET @secondTier = @HCFTotal * 32.00 / 10000.00;

IF @type = N'RES'
    SET @lastTier   = @HCFTotal * 26.00 / 10000.00;
ELSE
    SET @lastTier   = @HCFTotal * 20.00 / 10000.00;

DECLARE @waterCharges numeric(10, 2) = @firstTier + @secondTier + @lastTier;

IF @type = N'RES'
    SET @sewerCharges = @waterCharges * 6.826941 / 100;
ELSE
    SET @sewerCharges = @waterCharges * 9.202615 / 100;

IF @type = N'RES'
    SET @environmentCharges = @waterCharges * 0.022724;
ELSE
    SET @environmentCharges = @waterCharges * 0.221842;

IF @type = N'RES'
    SET @serviceCharges = @waterCharges * 0.145748;
ELSE
    SET @serviceCharges = @waterCharges * 0.210248;

DECLARE @totalCharges numeric(10, 2) = @waterCharges + @sewerCharges + @environmentCharges + @serviceCharges;

DECLARE @localTaxes numeric(10, 2) = @totalCharges * 0.031574;
DECLARE @stateTaxes numeric(10, 2) = @totalCharges * 0.016724;

DECLARE @amountDue numeric(10, 2) = @totalCharges + @localTaxes + @stateTaxes;

PRINT '======================================================';
PRINT 'Stellar Water Point - Customer Invoice';
PRINT '------------------------------------------------------';
PRINT CONCAT('Account Number:             ', @acntNbr);
PRINT '======================================================';
PRINT 'Meter Reading';
PRINT '------------------------------------------------------';
PRINT CONCAT('Counter Reading Start:      ', @counterReadingStart);
PRINT CONCAT('Counter Reading End:        ', @counterReadingEnd);
PRINT CONCAT('Total Gallons Consumed:     ', @consumption);
PRINT CONCAT('HCF Total                   ', @HCFTotal);
PRINT CONCAT('Gallons                     ', @gallons);
PRINT '======================================================';
PRINT 'Therms Evaluation';
PRINT '------------------------------------------------------';
PRINT CONCAT('First Tier Consumption:     ', @firstTier);
PRINT CONCAT('Second Tier Consumption:    ', @secondTier);
PRINT CONCAT('Last Tier Consumption:      ', @lastTier);
PRINT '------------------------------------------------------';
PRINT CONCAT('Water Use Charges:          ', @waterCharges);
PRINT CONCAT('Sewer Charges:              ', @sewerCharges);
PRINT '======================================================';
PRINT 'Bill Values';
PRINT '------------------------------------------------------';
PRINT CONCAT('Environment Charges:        ', @environmentCharges);
PRINT CONCAT('Service Charges:            ', @serviceCharges);
PRINT CONCAT('Total Charges:              ', @totalCharges);
PRINT CONCAT('Local Taxes:                ', @localTaxes);
PRINT CONCAT('State Taxes:                ', @stateTaxes);
PRINT '------------------------------------------------------';
PRINT CONCAT('Amount Due:                 ', @amountDue);
PRINT '======================================================';
  2. To execute the application, on the main menu, click Query -> Execute: 
    ======================================================
Stellar Water Point - Customer Invoice
------------------------------------------------------
Account Number:             6003-386-3955
======================================================
Meter Reading
------------------------------------------------------
Counter Reading Start:      5205
Counter Reading End:        5222
Total Gallons Consumed:     17
HCF Total                   12716.85
Gallons                     12717
======================================================
Therms Evaluation
------------------------------------------------------
First Tier Consumption:     61.04
Second Tier Consumption:    40.69
Last Tier Consumption:      25.43
------------------------------------------------------
Water Use Charges:          127.16
Sewer Charges:              11.70
======================================================
Bill Values
------------------------------------------------------
Environment Charges:        28.21
Service Charges:            26.74
Total Charges:              193.81
Local Taxes:                6.12
State Taxes:                3.24
------------------------------------------------------
Amount Due:                 203.17
======================================================
    Notice that the counter reading values are the same, but the calculated results are different

Beginning and Ending an ELSE Clause

As seen with an IF condition, if an ELSE section involves a long or many statements, you must add a body to it. Its body must start with a BEGIN keyword and end with the END keyword. The formula to follow would be:

IF condition
    statement(s);
ELSE
    BEGIN
        statement_1;
        statement_2;
        . . .
        statement_n;
    END

Of course, the IF section can have its own BEGIN...END body too:

IF condition
    BEGIN
        if-statement1;
        if-statement2;
         . . .
        if-statement_n;
    END
ELSE
    BEGIN
        statement_1;
        statement_2;
        . . .
        statement_n;
    END

Variants of an ELSE Conditional Statement

IF...ELSE IF

If you use an IF...ELSE situation, you can process only two statements. In some cases, you may deal with more than two conditions. In this case, you can use an IF...ELSE IF condition. The primary formula to follow is:

IF condition_1 statement_1;
ELSE IF condition_2 statement_2;

If a statement is long or the section includes many statement, you must create a BEGIN...END body for it. This would be done as follows:

IF condition_1
    statement_1;
ELSE IF condition_2
    BEGIN
        statement_1;
        statement_2;
        . . .
       statement_n;
    END

The first condition, condition_1, would first be checked. If condition_1 is true, then statement_1 would execute. If condition_1 is false, then condition_2 would be checked. If condition_2 is true, then the statement(s) in its section would execute. Any other result would be ignored. Here is an example:

DECLARE @employmentStatus NVARCHAR(150) = 'This employment requires C# certification. We will get back to you.';

DECLARE @certification char = 'y';

IF @certification = 'y'
    SET @employmentStatus = 'The employment applicant fullfils the basic requirements.';
ELSE IF @certification = 'Y'
    SET @employmentStatus = 'Employment requirements validated.';

PRINT '==================================================================================================';
PRINT 'Employment Verification';
PRINT '--------------------------------------------------------------------------------------------------';
PRINT CONCAT('Candition holds certification: ', @certification);
PRINT CONCAT('Decision Status:               ', @employmentStatus);
PRINT '==================================================================================================';

This would produce:

==================================================================================================
Employment Verification
--------------------------------------------------------------------------------------------------
Candition holds certification: y
Decision Status:               The employment applicant fullfils the basic requirements.
==================================================================================================

Practical LearningPractical Learning: Using ELSE IF

  1. Change the document as follows: 
    DECLARE @taxRate numeric(8, 2) = 0.00;

DECLARE @grossSalary numeric(8, 2) = 582.97;

-- Mississippi
IF @grossSalary >= 10000
    SET @taxRate = 5.00;
ELSE IF @grossSalary >= 5000
    SET @taxRate = 4.00;
ELSE IF @grossSalary >= 1000
    SET @taxRate = 3.00;

DECLARE @taxAmount numeric(8, 2) = @grossSalary * @taxRate / 100.00;
DECLARE @netPay    numeric(8, 2) = @grossSalary - @taxAmount;

PRINT '==============================================';
PRINT ' - Mississippi - State Income Tax -';
PRINT '----------------------------------------------';
PRINT CONCAT('Gross Salary: ', @grossSalary);
PRINT CONCAT('Tax Rate:     ', @taxRate, '%');
PRINT CONCAT('Tax Amount:   ', @taxAmount);
PRINT CONCAT('Net Pay:      ', @netPay);
PRINT '==============================================';
  2. To execute the application, on the main menu, click Query -> Execute: 
    ==============================================
 - Mississippi - State Income Tax -
----------------------------------------------
Gross Salary: 582.97
Tax Rate:     0.00%
Tax Amount:   0.00
Net Pay:      582.97
==============================================
  3. In the code change the @grossSalary value to 3582.97 
    DECLARE @taxRate numeric(8, 2) = 0.00;

DECLARE @grossSalary numeric(8, 2) = 3582.97;

-- Mississippi
IF @grossSalary >= 10000
    SET @taxRate = 5.00;
ELSE IF @grossSalary >= 5000
    SET @taxRate = 4.00;
ELSE IF @grossSalary >= 1000
    SET @taxRate = 3.00;

DECLARE @taxAmount numeric(8, 2) = @grossSalary * @taxRate / 100.00;
DECLARE @netPay    numeric(8, 2) = @grossSalary - @taxAmount;

PRINT '==============================================';
PRINT ' - Mississippi - State Income Tax -';
PRINT '----------------------------------------------';
PRINT CONCAT('Gross Salary: ', @grossSalary);
PRINT CONCAT('Tax Rate:     ', @taxRate, '%');
PRINT CONCAT('Tax Amount:   ', @taxAmount);
PRINT CONCAT('Net Pay:      ', @netPay);
PRINT '==============================================';
  4. To execute the application, on the main menu, click Query -> Execute: 
    ==============================================
 - Mississippi - State Income Tax -
----------------------------------------------
Gross Salary: 3582.97
Tax Rate:     3.00%
Tax Amount:   107.49
Net Pay:      3475.48
==============================================
  5. In the code, change the @grossSalary value to 7582.97 and press Enter 
    DECLARE @taxRate numeric(8, 2) = 0.00;

DECLARE @grossSalary numeric(8, 2) = 7582.97;

-- Mississippi
IF @grossSalary >= 10000
    SET @taxRate = 5.00;
ELSE IF @grossSalary >= 5000
    SET @taxRate = 4.00;
ELSE IF @grossSalary >= 1000
    SET @taxRate = 3.00;

DECLARE @taxAmount numeric(8, 2) = @grossSalary * @taxRate / 100.00;
DECLARE @netPay    numeric(8, 2) = @grossSalary - @taxAmount;

PRINT '==============================================';
PRINT ' - Mississippi - State Income Tax -';
PRINT '----------------------------------------------';
PRINT CONCAT('Gross Salary: ', @grossSalary);
PRINT CONCAT('Tax Rate:     ', @taxRate, '%');
PRINT CONCAT('Tax Amount:   ', @taxAmount);
PRINT CONCAT('Net Pay:      ', @netPay);
PRINT '==============================================';
  6. To execute the application, on the main menu, click Query -> Execute: 
    ==============================================
 - Mississippi - State Income Tax -
----------------------------------------------
Gross Salary: 7582.97
Tax Rate:     4.00%
Tax Amount:   303.32
Net Pay:      7279.65
==============================================
  7. In the code, change the @grossSalary value to 17582.97 and press Enter 
    DECLARE @taxRate numeric(8, 2) = 0.00;

DECLARE @grossSalary numeric(8, 2) = 17582.97;

-- Mississippi
IF @grossSalary >= 10000
    SET @taxRate = 5.00;
ELSE IF @grossSalary >= 5000
    SET @taxRate = 4.00;
ELSE IF @grossSalary >= 1000
    SET @taxRate = 3.00;

DECLARE @taxAmount numeric(8, 2) = @grossSalary * @taxRate / 100.00;
DECLARE @netPay    numeric(8, 2) = @grossSalary - @taxAmount;

PRINT '==============================================';
PRINT ' - Mississippi - State Income Tax -';
PRINT '----------------------------------------------';
PRINT CONCAT('Gross Salary: ', @grossSalary);
PRINT CONCAT('Tax Rate:     ', @taxRate, '%');
PRINT CONCAT('Tax Amount:   ', @taxAmount);
PRINT CONCAT('Net Pay:      ', @netPay);
PRINT '==============================================';
  8. To execute the application, on the main menu, click Query -> Execute: 
    ==============================================
 - Mississippi - State Income Tax -
----------------------------------------------
Gross Salary: 17582.97
Tax Rate:     5.00%
Tax Amount:   879.15
Net Pay:      16703.82
==============================================

IF... ELSE IF... ELSE

Because there can be other alternatives, you can provide a last ELSE section. The formula to use it is:

IF condition_1
    statement_1;
ELSE IF condition_2
    statement_2;
ELSE
    statement-n;

Here is an example:

DECLARE @employmentStatus NVARCHAR(120);
DECLARE @certification nchar = N'y';

IF @certification = 'y'
    SET @employmentStatus = N'The employment applicant fullfils the basic requirements.';
ELSE IF @certification = 'Y'
    SET @employmentStatus = N'Employment requirements validated.';
ELSE
    SET @employmentStatus = N'This employment requires C# certification. We will get back to you.';

PRINT '========================================================================================';
PRINT 'Employment Verification';
PRINT '----------------------------------------------------------------------------------------';
PRINT CONCAT('Candition holds certification: ', @certification);
PRINT CONCAT('Decision Status:               ', @employmentStatus);
PRINT '========================================================================================';

This would produce:

========================================================================================
Employment Verification
----------------------------------------------------------------------------------------
Candition holds certification: y
Decision Status:               The employment applicant fullfils the basic requirements.
========================================================================================

Here is another version of the code:

DECLARE @employmentStatus NVARCHAR(120);
DECLARE @certification nchar = N'I am not sure';

IF @certification = 'y'
    SET @employmentStatus = N'The employment applicant fullfils the basic requirements.';
ELSE IF @certification = 'Y'
    SET @employmentStatus = N'Employment requirements validated.';
ELSE
    SET @employmentStatus = N'This employment requires C# certification. We will get back to you.';

PRINT '===================================================================================================';
PRINT 'Employment Verification';
PRINT '---------------------------------------------------------------------------------------------------';
PRINT CONCAT('Candition holds certification: ', @certification);
PRINT CONCAT('Decision Status:               ', @employmentStatus);
PRINT '===================================================================================================';

This would produce:

Employment Verification
---------------------------------------------------------------------------------------------------
Candition holds certification: I
Decision Status:               This employment requires C# certification. We will get back to you.
===================================================================================================

If necessary, you can or must add a BEGIN...END body to the ELSE section

IF condition_1
    statement_1;

ELSE IF condition_2
    statement_2;

ELSE
    BEGIN
        statement_1;
        statement_2;
        . . .
       statement_n;
    END

IF...ELSE IF ... ELSE IF and ELSE

Remember that you can start a condition examination with with an IF condition. If there are many possibilities to check, you can add as many ELSE IF section as you judge necessary, then add a last ELSE section.

Practical LearningPractical Learning: Introducing if...else if...else

  1. Change the document as follows: 
    -- Stellar Water Point
/* Types of Accounts
OTH - Other
BUS - General Business
RES - Residential Household
SGO - Social/Government/Non-Profit Organization
UUO - Unidentified or Unclassified Type of Organization
WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc */

DECLARE @acntNbr             NVarChar(15) = N'9279-570-8394';
DECLARE @type                nchar(3) = N'RES';
DECLARE @counterReadingStart numeric  = 5205;
DECLARE @counterReadingEnd   numeric  = 5222;

DECLARE @consumption int = @counterReadingEnd - @counterReadingStart;
DECLARE @HCFTotal numeric(10, 2) = @consumption * 748.05;
DECLARE @gallons numeric = @consumption * 748.05;;

DECLARE @firstTier numeric(10, 2);
DECLARE @secondTier numeric(10, 2);
DECLARE @lastTier numeric(10, 2);

DECLARE @sewerCharges numeric(10, 2);
DECLARE @environmentCharges numeric(10, 2);
DECLARE @serviceCharges numeric(10, 2);

DECLARE @acntType NVARCHAR(120);

IF @type = N'RES'
    SET @acntType = N'RES - Residential Household';
ELSE IF @type = N'SGO'
    SET @acntType = N'SGO - Social/Government/Non-Profit Organization';
ELSE IF @type = N'BUS'
    SET @acntType = N'BUS - General Business';
ELSE IF @type = N'UUO'
    SET @acntType = N'UUO - Unidentified or Unclassified Type of Organization';
ELSE IF @type = N'WAT'
    SET @acntType = N'WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc';
ELSE
    SET @acntType = N'OTH - Other';

IF @type = N'RES'
    SET @firstTier = @HCFTotal * 41.50 / 10000.00;
ELSE IF @type = N'SGO'
    SET @firstTier = @HCFTotal * 46.00 / 10000.00;
ELSE IF @type = N'BUS'
    SET @firstTier = @HCFTotal * 45.00 / 10000.00;
ELSE IF @type = N'UUO'
    SET @firstTier = @HCFTotal * 25.00 / 10000.00;
ELSE IF @type = N'WAT'
    SET @firstTier = @HCFTotal * 50.00 / 10000.00;
ELSE
    SET @firstTier = @HCFTotal * 48.00 / 10000.00;

IF @type = N'RES'
    SET @secondTier = @HCFTotal * 32.50 / 10000.00;
ELSE IF @type = N'SGO'
    SET @secondTier = @HCFTotal * 50.00 / 10000.00;
ELSE IF @type = N'BUS'
    SET @secondTier = @HCFTotal * 45.00 / 10000.00;
ELSE IF @type = N'UUO'
    SET @secondTier = @HCFTotal * 35.00 / 10000.00;
ELSE IF @type = N'WAT'
    SET @secondTier = @HCFTotal * 40.00 / 10000.00;
ELSE
    SET @secondTier = @HCFTotal * 32.00 / 10000.00;

IF @type = N'RES'
    SET @lastTier   = @HCFTotal * 26.00 / 10000.00;
ELSE IF @type = N'SGO'
    SET @lastTier   = @HCFTotal * 4.00 / 10000.00;
ELSE IF @type = N'BUS'
    SET @lastTier   = @HCFTotal * 25.00 / 10000.00;
ELSE IF @type = N'UUO'
    SET @lastTier   = @HCFTotal * 40.00 / 10000.00;
ELSE IF @type = N'WAT'
    SET @lastTier   = @HCFTotal * 10.00 / 10000.00;
ELSE
    SET @lastTier   = @HCFTotal * 20.00 / 10000.00;

DECLARE @waterCharges numeric(10, 2) = @firstTier + @secondTier + @lastTier;

IF @type = N'RES'
    SET @sewerCharges = @waterCharges * 6.826941 / 100;
ELSE IF @type = N'SGO'
    SET @sewerCharges = @waterCharges * 4.162522 / 100;
ELSE IF @type = N'BUS'
    SET @sewerCharges = @waterCharges * 8.315136 / 100;
ELSE IF @type = N'UUO'
    SET @sewerCharges = @waterCharges * 10.626147 / 100;
ELSE IF @type = N'WAT'
    SET @sewerCharges = @waterCharges * 12.025135 / 100;
ELSE
    SET @sewerCharges = @waterCharges * 9.202615 / 100;

IF @type = N'RES'
    SET @environmentCharges = @waterCharges * 0.022724;
ELSE IF @type = N'SGO'
    SET @environmentCharges = @waterCharges * 0.118242;
ELSE IF @type = N'BUS'
    SET @environmentCharges = @waterCharges * 0.161369;
ELSE IF @type = N'UUO'
    SET @environmentCharges = @waterCharges * 0.082477;
ELSE IF @type = N'WAT'
    SET @environmentCharges = @waterCharges * 0.413574;
ELSE
    SET @environmentCharges = @waterCharges * 0.221842;

IF @type = N'RES'
    SET @serviceCharges = @waterCharges * 0.145748;
ELSE IF @type = N'SGO'
    SET @serviceCharges = @waterCharges * 0.102246;
ELSE IF @type = N'BUS'
    SET @serviceCharges = @waterCharges * 0.242627;
ELSE IF @type = N'UUO'
    SET @serviceCharges = @waterCharges * 0.186692;
ELSE IF @type = N'WAT'
    SET @serviceCharges = @waterCharges * 0.412628;
ELSE
    SET @serviceCharges = @waterCharges * 0.210248;

DECLARE @totalCharges numeric(10, 2) = @waterCharges + @sewerCharges + @environmentCharges + @serviceCharges;

DECLARE @localTaxes numeric(10, 2);
DECLARE @stateTaxes numeric(10, 2);

IF @type = N'RES'
    SET @localTaxes = @totalCharges * 0.031574;
ELSE IF @type = N'SGO'
    SET @localTaxes = @totalCharges * 0.035026;
ELSE IF @type = N'BUS'
    SET @localTaxes = @totalCharges * 0.122517;
ELSE IF @type = N'UUO'
    SET @localTaxes = @totalCharges * 0.105737;
ELSE IF @type = N'WAT'
    SET @localTaxes = @totalCharges * 0.153248;
ELSE
    SET @localTaxes = @totalCharges * 0.125148;

IF @type = N'RES'
    SET @stateTaxes = @totalCharges * 0.016724;
ELSE IF @type = N'SGO'
    SET @stateTaxes = @totalCharges * 0.008779;
ELSE IF @type = N'BUS'
    SET @stateTaxes = @totalCharges * 0.042448;
ELSE IF @type = N'UUO'
    SET @stateTaxes = @totalCharges * 0.067958;
ELSE IF @type = N'WAT'
    SET @stateTaxes = @totalCharges * 0.081622;
ELSE
    SET @stateTaxes = @totalCharges * 0.013746;

DECLARE @amountDue numeric(10, 2) = @totalCharges + @localTaxes + @stateTaxes;

DECLARE @lateAmountDue numeric(10, 2);

IF @type = N'RES'
    SET @lateAmountDue = @amountDue + 8.95;
ELSE IF @type = N'SGO'
    SET @lateAmountDue = @amountDue + (@amountDue / 4.575);
ELSE IF @type = N'BUS'
    SET @lateAmountDue = @amountDue + (@amountDue / 12.315);
ELSE IF @type = N'UUO'
    SET @lateAmountDue = @amountDue + (@amountDue / 7.425);
ELSE IF @type = N'WAT'
    SET @lateAmountDue = @amountDue + (@amountDue / 15.225);
ELSE
    SET @lateAmountDue = @amountDue + (@amountDue / 6.735);

PRINT '===================================================================================================';
PRINT 'Stellar Water Point - Customer Invoice';
PRINT '---------------------------------------------------------------------------------------------------';
PRINT CONCAT('Account Number:             ', @acntNbr);
PRINT CONCAT('Account Type:               ', @acntType);
PRINT '===================================================================================================';
PRINT 'Meter Reading';
PRINT '---------------------------------------------------------------------------------------------------';
PRINT CONCAT('Counter Reading Start:      ', @counterReadingStart);
PRINT CONCAT('Counter Reading End:        ', @counterReadingEnd);
PRINT CONCAT('Total Gallons Consumed:     ', @consumption);
PRINT CONCAT('HCF Total                   ', @HCFTotal);
PRINT CONCAT('Gallons                     ', @gallons);
PRINT '===================================================================================================';
PRINT 'Therms Evaluation';
PRINT '---------------------------------------------------------------------------------------------------';
PRINT CONCAT('First Tier Consumption:     ', @firstTier);
PRINT CONCAT('Second Tier Consumption:    ', @secondTier);
PRINT CONCAT('Last Tier Consumption:      ', @lastTier);
PRINT '---------------------------------------------------------------------------------------------------';
PRINT CONCAT('Water Use Charges:          ', @waterCharges);
PRINT CONCAT('Sewer Charges:              ', @sewerCharges);
PRINT '===================================================================================================';
PRINT 'Bill Values';
PRINT '---------------------------------------------------------------------------------------------------';
PRINT CONCAT('Environment Charges:        ', @environmentCharges);
PRINT CONCAT('Service Charges:            ', @serviceCharges);
PRINT CONCAT('Total Charges:              ', @totalCharges);
PRINT CONCAT('Local Taxes:                ', @localTaxes);
PRINT CONCAT('State Taxes:                ', @stateTaxes);
PRINT '---------------------------------------------------------------------------------------------------';
PRINT CONCAT('Amount Due:                 ', @amountDue);
PRINT CONCAT('Late Amount Due:            ', @lateAmountDue);
PRINT '===================================================================================================';
  2. To execute, on the main menu, click Query -> Execute: 
    ===================================================================================================
Stellar Water Point - Customer Invoice
---------------------------------------------------------------------------------------------------
Account Number:             9279-570-8394
Account Type:               RES - Residential Household
===================================================================================================
Meter Reading
---------------------------------------------------------------------------------------------------
Counter Reading Start:      5205
Counter Reading End:        5222
Total Gallons Consumed:     17
HCF Total                   12716.85
Gallons                     12717
===================================================================================================
Therms Evaluation
---------------------------------------------------------------------------------------------------
First Tier Consumption:     52.77
Second Tier Consumption:    41.33
Last Tier Consumption:      33.06
---------------------------------------------------------------------------------------------------
Water Use Charges:          127.16
Sewer Charges:              8.68
===================================================================================================
Bill Values
---------------------------------------------------------------------------------------------------
Environment Charges:        2.89
Service Charges:            18.53
Total Charges:              157.26
Local Taxes:                4.97
State Taxes:                2.63
---------------------------------------------------------------------------------------------------
Amount Due:                 164.86
Late Amount Due:            173.81
===================================================================================================
  3. In the code, change the value of the @acntNbr variable to 9249-379-6848
  4. In the code, change the @type value to BUS
  5. To execute, on the main menu, click Query -> Execute: 
    ===================================================================================================
Stellar Water Point - Customer Invoice
---------------------------------------------------------------------------------------------------
Account Number:             9249-379-6848
Account Type:               BUS - General Business
===================================================================================================
Meter Reading
---------------------------------------------------------------------------------------------------
Counter Reading Start:      5205
Counter Reading End:        5222
Total Gallons Consumed:     17
HCF Total                   12716.85
Gallons                     12717
===================================================================================================
Therms Evaluation
---------------------------------------------------------------------------------------------------
First Tier Consumption:     57.23
Second Tier Consumption:    57.23
Last Tier Consumption:      31.79
---------------------------------------------------------------------------------------------------
Water Use Charges:          146.25
Sewer Charges:              12.16
===================================================================================================
Bill Values
---------------------------------------------------------------------------------------------------
Environment Charges:        23.60
Service Charges:            35.48
Total Charges:              217.49
Local Taxes:                26.65
State Taxes:                9.23
---------------------------------------------------------------------------------------------------
Amount Due:                 253.37
Late Amount Due:            273.94
===================================================================================================
  6. In the code, change the value of the @acntNbr variable to 4820-375-2842
  7. In the code, change the @type value to SGO
  8. To execute, on the main menu, click Query -> Execute: 
    ===================================================================================================
Stellar Water Point - Customer Invoice
---------------------------------------------------------------------------------------------------
Account Number:             4820-375-2842
Account Type:               SGO - Social/Government/Non-Profit Organization
===================================================================================================
Meter Reading
---------------------------------------------------------------------------------------------------
Counter Reading Start:      5205
Counter Reading End:        5222
Total Gallons Consumed:     17
HCF Total                   12716.85
Gallons                     12717
===================================================================================================
Therms Evaluation
---------------------------------------------------------------------------------------------------
First Tier Consumption:     58.50
Second Tier Consumption:    63.58
Last Tier Consumption:      5.09
---------------------------------------------------------------------------------------------------
Water Use Charges:          127.17
Sewer Charges:              5.29
===================================================================================================
Bill Values
---------------------------------------------------------------------------------------------------
Environment Charges:        15.04
Service Charges:            13.00
Total Charges:              160.50
Local Taxes:                5.62
State Taxes:                1.41
---------------------------------------------------------------------------------------------------
Amount Due:                 167.53
Late Amount Due:            204.15
===================================================================================================
  9. In the code, change the value of the @acntNbr variable to 9618-579-2577
  10. In the code, change the @type value to UUO
  11. To execute, on the main menu, click Query -> Execute: 
    ===================================================================================================
Stellar Water Point - Customer Invoice
---------------------------------------------------------------------------------------------------
Account Number:             9618-579-2577
Account Type:               UUO - Unidentified or Unclassified Type of Organization
===================================================================================================
Meter Reading
---------------------------------------------------------------------------------------------------
Counter Reading Start:      5205
Counter Reading End:        5222
Total Gallons Consumed:     17
HCF Total                   12716.85
Gallons                     12717
===================================================================================================
Therms Evaluation
---------------------------------------------------------------------------------------------------
First Tier Consumption:     31.79
Second Tier Consumption:    44.51
Last Tier Consumption:      50.87
---------------------------------------------------------------------------------------------------
Water Use Charges:          127.17
Sewer Charges:              13.51
===================================================================================================
Bill Values
---------------------------------------------------------------------------------------------------
Environment Charges:        10.49
Service Charges:            23.74
Total Charges:              174.91
Local Taxes:                18.49
State Taxes:                11.89
---------------------------------------------------------------------------------------------------
Amount Due:                 205.29
Late Amount Due:            232.94
===================================================================================================
  12. Change the value of the @acntNbr variable to 7028-405-9381 and press Enter
  13. Change the value of the @type variable to WAT and press Enter
  14. To execute, on the main menu, click Query -> Execute: 
    ===================================================================================================
Stellar Water Point - Customer Invoice
---------------------------------------------------------------------------------------------------
Account Number:             7028-405-9381
Account Type:               WAT - Water Intensive Business (Laudromat, Hair Salon, Restaurant, etc
===================================================================================================
Meter Reading
---------------------------------------------------------------------------------------------------
Counter Reading Start:      5205
Counter Reading End:        5222
Total Gallons Consumed:     17
HCF Total                   12716.85
Gallons                     12717
===================================================================================================
Therms Evaluation
---------------------------------------------------------------------------------------------------
First Tier Consumption:     63.58
Second Tier Consumption:    50.87
Last Tier Consumption:      12.72
---------------------------------------------------------------------------------------------------
Water Use Charges:          127.17
Sewer Charges:              15.29
===================================================================================================
Bill Values
---------------------------------------------------------------------------------------------------
Environment Charges:        52.59
Service Charges:            52.47
Total Charges:              247.52
Local Taxes:                37.93
State Taxes:                20.20
---------------------------------------------------------------------------------------------------
Amount Due:                 305.65
Late Amount Due:            325.73
===================================================================================================
  15. 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