|
A view is a list of columns or a series of records retrieved
from one or more existing tables, or as a combination of one or more views and
one or more tables. Based on this, before creating a view, you must first decide
where its columns and records would come from. Obviously the easiest view is one whose
columns and records come from one table.
|
Practical Learning: Introducing Views
|
|
- Start Microsoft SQL Server and connect to the server.
Make sure you have the RealEstate2
database created in the previous lesson. If you didn't
create them, do it now
|
Fundamentals of Creating Views |
|
To create a view, you can use the Object Explorer or a query window.
Before starting the view, you would have to specify the table(s) that would be involved.
To create a view from the Object Explorer,
you can expand the database, right-click Views and click
New View. This would open the Add Table dialog box:
The basic functionality is
exactly the same as we reviewed in the previous lesson:
- To specify the table that would be used as the source, you
can click it in the list box of the Tables property page
- If you would be using
another existing view, from the Views property page, you can click the name of
the desired view
- If a function would be used to generate the records, you can
locate it in the Functions property page. After selecting the source object, you
can either double-click it or you can click it once and click Add. In in the
previous lesson, we saw that you could add more than one existing table. the same
way, you can add more than one view or functions
- After
selecting the source(s), you can click Close on the Add Table dialog box
- After
selecting the objects, as we saw in the previous lesson, they would display in the
window
- As seen in the previous lesson, if you are using more than one table and
they are not (yet) related, you can drag a column from one table and drop it
on another table to create a JOIN between them
- As we saw in previous lessons, to select a column, you can click its check box in the top
list. This would display it in the first empty box under the Column column and
would add its name to the SELECT statement. Alternatively, you can click
an empty box in the Column column to reveal its combo box, then click the arrow
of the combo box and select the desired column from the list
- After selecting the column, its check box would be checked
in the top section of the window, its name would be displayed in the Column
column, and it would be added to the SELECT statement. If you know the
name of the column you want to add, you can manually type it in the SELECT
statement.
To structure of a view can be considered complete when the
SELECT statement is as complete as possible. At any time, to test the results of a view, you can run it.
To do this, you can click the Execute SQL button  .
This would cause the bottom section of the view to display the results of the
query. Here is an example:
As stated already, one of the reasons for creating a view is to be able to use it over and over again. To
achieve this, the view must be saved. Like most objects in SQL Server, a view
must have a name and it is saved in its own file. To save a view from the view window, you can click the Save button on the toolbar. You can also attempt
to close the window. You would then be prompted to save it. When saving a view,
you should give it a name that follows the rules and suggestions of SQL.
|
Practical Learning: Visually Creating a View
|
|
- In the Object Explorer, expand the Databases and the RealEstate2 nodes
- Right-click Views and click New View
- In the Add Table dialog box, double-click PropertyTypes, Properties, and
Conditions
- Click Close
- From the PropertyTypes table, drag
PropertyTypeID and drop it on the PropertyTypeID field of the
Properties table
- From the Conditions table, drag ConditionID and drop it on the
ConditionID field of the
Properties table
- On the tables, select the following fields: PropertyNumber,
PropertyType, Condition, City, ZIPCode, Bedrooms, Bathrooms,
FinishedBasement, Stories, YearBuilt, and MarketValue
- In the Criteria section, click PropertyType and press Tab 6 times.
In its Filter field, type Single Family
- Press Tab and, in its Or field, type Townhouse
In our lessons, here are the rules we will use to name our
views:
- A name will start with a letter. Examples are n, act, or Second
- After the first letter, the name will have combinations of underscores,
letters, and digits. Examples are n24, act_52_t
- A name will not include special characters such as !, @, #, $, %, ^,
&, or *
- A name will not have spaces
- If the name is a combination of words, each word will start in uppercase.
Examples are DateHired, RealSport, or DriversLicenseNumber
After saving a view, it becomes part of the Views node of
its database: a node would be created for it and its name would appear in the
Views node of its database.
|
Practical Learning: Saving a View
|
|
- To save the view, on the Standard toolbar, click the Save button
- Set the Name to Homes and click OK
- Close the view window
- In the Object Explorer, under RealEstate2, right-click Views and click New View
- In the Add Table dialog box, double-click PropertyTypes, Properties, and
Conditions
- Click Close
- From the PropertyTypes table, drag
PropertyTypeID and drop it on the PropertyTypeID field of the
Properties table
- From the Conditions table, drag ConditionID and drop it on the
ConditionID field of the
Properties table
- On the tables, select the following fields: PropertyNumber,
PropertyType, Condition, City, ZIPCode, Bedrooms, Bathrooms, and MarketValue
- In the Criteria section, click PropertyType. Press Tab 3 times and
press the Space bar to remove the check mark of its Output field
- Press Tab 3 times. In its Filter field, type Condominiums
and press Enter
- On the main menu, click File -> Save
- Set the Name to Condos and click OK
- Close the view window
To programmatically create a view, you use the following SQL
syntax:
CREATE VIEW ViewName
AS
SELECT Statement
Microsoft SQL Server can generate skeleton code of a view
for you. To use it, first create an empty query window. Display the Template
Explorer. In the Template Explorer, expand the View node. From the View node,
drag Create View and drop it in the query window.
The creation of a view starts with the CREATE VIEW
expression followed by a name. The name of a view follows the rules
and suggestions we reviewed above. After the name of the view, use the AS
keyword to indicate that you are ready to define the view.
Because a view is primarily a SQL statement, it is defined using a SELECT
statement, using the same rules we studied for data analysis. Here is an example
of a view:
CREATE VIEW dbo.ListOfMen
AS
SELECT dbo.Genders.Gender,
dbo.Persons.FirstName, dbo.Persons.LastName
FROM dbo.Genders INNER JOIN dbo.Persons
ON dbo.Genders.GenderID = dbo.Persons.GenderID
WHERE (dbo.Genders.Gender = 'Male');
GO
After creating the SQL statement that defines the view, you
must execute the statement. If using a query window, you can do this by pressing
F5. Once the statement is executed, its name is automatically added to the Views
node of its database even if you don't save its code.
|
Practical Learning: Creating a View
|
|
- Make sure you have the Yugo National Bank
database created in the Lesson13. If you didn't create it, do it now.
In the Object Explorer, right-click YugoNationalBank and click New Query
- To create a new view, type the following code
Use YugoNationalBank;
GO
CREATE VIEW PayrollPreparation
AS
SELECT dbo.Employees.EmployeeNumber,
dbo.Employees.LastName + ', ' +
dbo.Employees.FirstName AS [Full Name],
dbo.Employees.HourlySalary,
dbo.TimeSheets.TimeSheetCode, dbo.TimeSheets.Week1Monday,
dbo.TimeSheets.Week1Tuesday, dbo.TimeSheets.Week1Wednesday,
dbo.TimeSheets.Week1Thursday, dbo.TimeSheets.Week1Friday,
dbo.TimeSheets.Week1Saturday, dbo.TimeSheets.Week1Sunday,
dbo.TimeSheets.Week2Monday, dbo.TimeSheets.Week2Tuesday,
dbo.TimeSheets.Week2Wednesday, dbo.TimeSheets.Week2Thursday,
dbo.TimeSheets.Week2Friday, dbo.TimeSheets.Week2Saturday,
dbo.TimeSheets.Week2Sunday
FROM dbo.Employees INNER JOIN dbo.TimeSheets
ON dbo.Employees.EmployeeNumber = dbo.TimeSheets.EmplNumber;
GO
|
- To execute the code, press F5
- Delete the content of the whole view window
|
Opening or Executing a View |
|
As stated already, a view is a technique of selecting
records to view or use over an over again. After a view has been created,
you can open it. You have two main options.
- To see the structure of a view, such as the table(s) on which it is
based and the relationships, if any that compose it, in the Object
Explorer, right-click the view and click Design
To see the SQL code that makes up a view, in the Object Explorer,
right-click the view and click Edit
Executing a view consists of seeing its results. To do this,
you have various options. To view the results of a view:
- Open an empty query window associated with the database that contains the
view. In the query window, write a SELECT statement using the same
formulas and rules we saw for tables. Here is an example:
- From the Object Explorer,
expand the database and its Views node. Right-click the name of the
view and click Open View
|
Practical Learning: Executing a View
|
|
- In the Object Explorer, under RealEste2, expand Views if necessary.
Right-click Condos and click Open View
- Close the view window
- In the Object Explorer, right-click YugoNationalBank and click New Query
- To execute the view, in the empty view window, type the following
USE YugoNationalBank;
GO
SELECT PayrollPreparation.* FROM PayrollPreparation;
GO
|
- To execute the view, press F5
- Delete the content of the window
In Transact-SQL, a view is considered an object. As
such, it can be viewed, changed, or deleted. Like any regular object, a
view has its own characteristics. To see them, you can right-click the
view and click Properties. A View Properties dialog box would come up. It
can give you information such as the name of the database the view belongs
to, the date the view was created, etc.
|
Practical Learning: Accessing the Properties of a View
|
|
- To create another view, type the following:
-- =============================================
-- Database: YugoNationalBank
-- View: TimeSheet
-- =============================================
USE YugoNationalBank
GO
IF object_id(N'TimeSheet', 'V') IS NOT NULL
DROP VIEW dbo.TimeSheet
GO
CREATE VIEW dbo.TimeSheet
AS
SELECT EmplNumber, StartDate,
Week1Monday, Week1Tuesday, Week1Wednesday,
Week1Thursday, Week1Friday,
Week1Saturday, Week1Sunday,
Week2Monday, Week2Tuesday, Week2Wednesday,
Week2Thursday, Week2Friday, Week2Saturday,
Week2Sunday, Notes
FROM TimeSheets;
|
- Press F5 to execute the statement
- Delete the content of the window
- In the Object Explorer, under YugoNationalBank, right-click Views and
click Refresh
- Expand Views if necessary. Right-click TimeSheet and click Properties
- Press Esc to close the Properties dialog box
After a view has been created, either by you or
someone else, you may find out that it has an unnecessary column, it needs
a missing column, it includes unnecessary records, or some records are
missing. Fortunately, you can change the structure or the code of a view. This is
referred to as altering a view. You have two main options:
- To visually change a view, in the Object Explorer, right-click the view
and click Design. From the view window, you can add or remove the columns.
You can also change any options in one of the sections of the window. After
modifying the view, save it and close it
- To change the code of a view, in the Object Explorer, right-click it and
view Edit. After editing the
view's code, you can save it
- From the Object Explorer, right-click the view, position the mouse on
Script View As -> ALTER To -> New Query Editor Window
The basic formula to programmatically modify a view
is:
ALTER VIEW ViewName
AS
SELECT Statement
You start the alteration with the ALTER VIEW
expression followed by the name of the view. After the name of the view,
use the AS keyword to specify that you are ready to show the
change. After the AS keyword, you can then define the view as you see fit.
For example, you can create a SELECT statement that includes a
modification of the existing code or a completely new statement.
In the view we created to show a list of men of a table, we
included a column for the gender. This column is useless or redundant because we
already know that the list includes only men. Here is an example of altering the
view to remove (or rather omit) the Gender column of the Persons table:
ALTER VIEW dbo.ListOfMen
AS
SELECT dbo.Persons.FirstName, dbo.Persons.LastName
FROM dbo.Genders INNER JOIN dbo.Persons
ON dbo.Genders.GenderID = dbo.Persons.GenderID
WHERE (dbo.Genders.Gender = 'Male');
Instead of modifying a view, if you find it altogether
useless, you can remove it from its database. You have various options. To delete a view:
- In the Object Explorer, in a database, right-click the name of the view and click Delete. You would be given the
opportunity to confirm your intention or to change your mind
- In the Object Explorer, right-click the view, position the mouse on Script
View As -> DROP To New Query Editor Window
- Open an empty query window associated with the database that has the
undesired view. From the Template Explorer, in the View node, drag Drop View
and drop it in the query window
The formula to programmatically delete a view is:
DROP VIEW ViewName
On the right side of the DROP VIEW expression, enter
the name of the undesired view and execute the statement. You will not be
warned before the interpreter deletes the view.
As seen so far, a view is a selected list of records from a
table. As you may suspect, the easiest view is probably one created from one
table. Imagine you have a table of employees and you want to create a view that
lists only their names. You may create a view as follows:
CREATE VIEW dbo.EmployeesNames
AS
SELECT FirstName,
LastName,
LastName + ', ' + FirstName AS FullName FROM Persons;
GO
On such a view that is based on one table, you can perform
data entry, using the view, rather than the table. To do this, you follow the
same rules we reviewed in Lesson 9. Here is an example:
INSERT INTO dbo.EmployeesNames(FirstName, LastName)
VALUES('Peter', 'Justice');
If you perform data entry using a view, the data you provide
would be entered on the base table; this means that the table would be updated
automatically. Based on this feature, you can create a view purposely intended
to update a table so that, in the view, you would include only the columns that
need to be updated.
|
Practical Learning: Performing Data Entry Using a View
|
|
- To create a function we will use, enter the following code:
--==================================================
-- Database: YugoNationalBank
-- Function: CreateTimeSheetCode
-- Purpose: This function takes an employee number
-- and the start date of a time sheet.
-- Then it creates a unique number
-- in the format 0000000000000
-- The first 5 digits represent the
-- employee number,
-- the second 4 digits represent the year,
-- the 2 digits represent the month,
-- that last 2 digits represent the day
--==================================================
USE YugoNationalBank
GO
IF OBJECT_ID (N'dbo.CreateTimeSheetCode') IS NOT NULL
DROP FUNCTION dbo.CreateTimeSheetCode
GO
CREATE FUNCTION dbo.CreateTimeSheetCode(@EmplNbr varchar(6),
@dteStart datetime)
RETURNS varchar(15)
AS
BEGIN
DECLARE @strMonth AS varchar(20);
DECLARE @strDay AS varchar(20);
DECLARE @iMonth AS int;
DECLARE @iDay AS int;
DECLARE @strTimeSheetCode varchar(20);
SET @iMonth = CONVERT(varchar(20), MONTH(@dteStart));
SET @iDay = CONVERT(varchar(20), DAY(@dteStart));
IF @iMonth < 10
SET @strMonth = CONVERT(varchar(20), YEAR(@dteStart)) +
'0' + CONVERT(varchar(20), @iMonth);
ELSE
SET @strMonth = CONVERT(varchar(20), YEAR(@dteStart)) +
CONVERT(varchar(20), @iMonth);
IF @iDay < 10
SET @strDay = @strMonth + '0' +
CONVERT(varchar(20), @iDay);
ELSE
SET @strDay = @strMonth + CONVERT(varchar(2), @iDay);
SET @strTimeSheetCode = @EmplNbr + @strDay;
RETURN @strTimeSheetCode;
END
GO
|
- Press F5 to execute
- To perform data entry using a view, enter the following code:
USE YugoNationalBank
GO
-- The following code performs data entry using a view
INSERT INTO dbo.TimeSheet
VALUES('46288', '1/1/2007',
0.00, 8.50, 9.50, 8.50, 9.00, 0.00, 0.00,
10.00, 9.50, 8.50, 10.50, 9.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('66286', '1/1/2007',
0.00, 8.50, 6.50, 5.50, 6.50, 0.00, 0.00,
4.00, 6.00, 6.50, 6.00, 5.50, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('92493', '1/1/2007',
0.00, 8.00, 9.00, 8.50, 9.50, 0.00, 0.00,
5.50, 6.50, 4.50, 6.00, 4.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('27199', '1/15/2007',
6.00, 8.50, 0.00, 4.00, 6.50, 0.00, 0.00,
4.00, 0.00, 6.00, 4.00, 0.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('39538', '1/15/2007',
8.00, 8.00, 6.00, 8.50, 6.00, 0.00, 0.00,
9.50, 10.50, 8.00, 8.00, 8.50, 0.00, 0.00,
'There were a few missing times in the time sheet. ' +
'They have been recorded.');
GO
INSERT INTO dbo.TimeSheet
VALUES('40550', '1/15/2007',
8.50, 8.00, 0.00, 8.50, 0.00, 0.00, 0.00,
6.00, 6.50, 6.50, 0.00, 4.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('66286', '1/29/2007',
8.00, 6.50, 9.50, 8.00, 7.50, 0.00, 0.00,
10.50, 9.50, 8.50, 8.00, 10.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('90026', '2/12/2007',
8.50, 6.50, 8.00, 8.00, 9.50, 0.00, 0.00,
9.50, 8.00, 8.50, 8.00, 8.00, 0.00, 0.00, '');
GO
INSERT INTO dbo.TimeSheet
VALUES('92493', '2/12/2007',
4.00, 6.50, 5.50, 8.00, 6.50, 0.00, 0.00,
8.00, 8.00, 8.00, 6.00, 8.00, 0.00, 0.00, '');
GO
-- The following code updates a table using a function
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('46288', '1/1/2007')
WHERE (EmplNumber = '46288') AND (StartDate = '1/1/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('66286', '1/1/2007')
WHERE (EmplNumber = '66286') AND (StartDate = '1/1/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('92493', '1/1/2007')
WHERE (EmplNumber = '92493') AND (StartDate = '1/1/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('27199', '1/15/2007')
WHERE (EmplNumber = '27199') AND (StartDate = '1/15/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('39538', '1/15/2007')
WHERE (EmplNumber = '39538') AND (StartDate = '1/15/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('40550', '1/15/2007')
WHERE (EmplNumber = '40550') AND (StartDate = '1/15/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('66286', '1/29/2007')
WHERE (EmplNumber = '66286') AND (StartDate = '1/29/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('90026', '2/12/2007')
WHERE (EmplNumber = '90026') AND (StartDate = '2/12/2007');
GO
UPDATE dbo.TimeSheets
SET TimeSheetCode = dbo.CreateTimeSheetCode('92493', '2/12/2007')
WHERE (EmplNumber = '92493') AND (StartDate = '2/12/2007');
GO
|
- Press F5 to execute
- Delete the content of the window
It is important to know that a view is more of a table
type than any other object. This means that a view is not a function but
it can use a function. The word argument here only means that some values
can be passed to a view but these values can be specified only when
creating the view. They are not real arguments.
When structuring a view, you can create placeholders for
columns and pass them in the parentheses of the view. This would be done
as follows:
CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
. . .
If you use this technique, the names passed in the
parentheses of the view are the captions that would be displayed in place
of the columns of the view. This technique allows you to specify the
strings of your choice for the columns. If you want a column header to
display the actual name of the column, write it the same. Otherwise, you
can use any string you want for the column. If the name is in one word,
you can just type it. If the name includes various words, include them
between an opening square bracket "[" and a closing square
bracket "]".
After listing the necessary strings as the captions of
columns, in your SELECT statement of the view, you must use the
exact same number of columns as the number of arguments of the view. In
fact, each column of your SELECT statement should correspond to an
argument of the same order.
Here is an example:
CREATE VIEW dbo.MenAndWomen([First Name], [Last Name], Gender)
AS
SELECT dbo.Persons.FirstName,
dbo.Persons.LastName,
dbo.Genders.Gender
FROM dbo.Genders INNER JOIN dbo.Persons
ON dbo.Genders.GenderID = dbo.Persons.GenderID;
GO
Because, as we stated already, a view is not a
function and the values passed to the view are not real arguments, when
executing the view, don't specify the names of arguments. Simply create a
SELECT statement and specify the name of the view as the source. Here
is an example:
|
Views and Conditional Statements |
|
Besides its querying characteristics that allow it to
perform data analysis, probably the most important feature of a query is
its ability to be as complex as possible by handling conditional
statements. This makes it possible to use a view instead of a table in
operations and expressions that would complicate the code or structure of
a table. When creating a view, in its SELECT statement, you can
perform column selections, order them, and set criteria to exclude some
records.
Here is an example:
To create more complex or advanced views, you can
involve functions. As always, probably the easiest functions to use are
those built-in.
If there is no built-in function that performs the
operation you want, you can create your own. Here is an example: USE People;
GO
CREATE FUNCTION dbo.GetFullName
(
@FName varchar(20),
@LName varchar(20)
)
RETURNS varchar(41)
AS
BEGIN
RETURN @LName + ', ' + @FName;
END
GO
Once you have a function you want to use, you
can call it in the body of your view as you judge it necessary. Here is an
example: CREATE VIEW dbo.MyPeople
AS
SELECT dbo.GetFullName(FirstName, LastName) AS [Full Name],
dbo.Genders.Gender
FROM Genders INNER JOIN dbo.Persons
ON dbo.Genders.GenderID = dbo.Persons.GenderID;
This would produce:
|
Practical Learning: Using a Function in a View
|
|
- In the Object Explorer, under YugoNationalBank, expand Views if
necessary.
Right-click PayrollPreparation, position the mouse on Script View As
-> ALTER To -> New Query Editor Window
- Change the code as follows:
USE [YugoNationalBank]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[PayrollPreparation]
AS
SELECT dbo.Employees.EmployeeNumber,
dbo.Employees.LastName + ', ' +
dbo.Employees.FirstName AS [Full Name],
dbo.Employees.HourlySalary,
dbo.TimeSheets.TimeSheetCode, dbo.TimeSheets.Week1Monday,
dbo.TimeSheets.Week1Tuesday, dbo.TimeSheets.Week1Wednesday,
dbo.TimeSheets.Week1Thursday, dbo.TimeSheets.Week1Friday,
dbo.TimeSheets.Week1Saturday, dbo.TimeSheets.Week1Sunday,
dbo.TimeSheets.Week2Monday, dbo.TimeSheets.Week2Tuesday,
dbo.TimeSheets.Week2Wednesday, dbo.TimeSheets.Week2Thursday,
dbo.TimeSheets.Week2Friday, dbo.TimeSheets.Week2Saturday,
dbo.TimeSheets.Week2Sunday,
CAST(dbo.TimeSheets.Week1Monday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Tuesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Wednesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Thursday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Friday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Saturday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Sunday AS SmallMoney)
AS [Total Week1],
CAST(dbo.TimeSheets.Week2Monday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Tuesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Wednesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Thursday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Friday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Saturday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Sunday AS SmallMoney)
AS [Total Week2],
CAST(dbo.TimeSheets.Week1Monday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Tuesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Wednesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Thursday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Friday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Saturday AS SmallMoney) +
CAST(dbo.TimeSheets.Week1Sunday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Monday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Tuesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Wednesday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Thursday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Friday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Saturday AS SmallMoney) +
CAST(dbo.TimeSheets.Week2Sunday AS SmallMoney)
AS [Total Time Sheet]
FROM dbo.Employees INNER JOIN dbo.TimeSheets
ON dbo.Employees.EmployeeNumber = dbo.TimeSheets.EmplNumber;
|
- To update the query, on the main menu, click Query -> Execute
- Delete the content of the window
- To see the result of the query, type the following:
Use YugoNationalBank;
GO
USE YugoNationalBank;
GO
SELECT PayrollPreparation.* FROM PayrollPreparation;
GO
|
- Delete the content of the window
- Delete the content of the window
|
|
