Creating Reports Using SQL QUERY ANALYZER

I am a student of the University of Phoenix. In My SQL class I was unsuccessful in creating a report using basic SQL commands using the SQL Query Analyzer version 8.00.760.

I am using a version of Microsoft SQL Server 2000 Sold to me by the college. The Disk 1 said SQL Server 2000 Developer Edition. Disk 2 said SQL Server 2000 service pack 3a. The Third Disk Says SQL Server 2000 Reporting Services.

Since I am learning SQL on this platform I wanted to create reports using the SQL Query Analyzer. So how do I create reports using Basic Commands. This is what I have so far.

CREATE TABLE ACCOUNTS
(
Account_Number INT NOT NULL PRIMARY KEY,
Long_Description VarChar(500) NOT NULL,
Short_Description VarChar(500)NOT NULL,
Balance Money NULL,
);

Once I Imported the data from an excel file. I created a view.

CREATE VIEW Account_Report
(Account, Descript, Identifier, Balance) AS
SELECT Account_Number, Long_Description, Short_Description, Balance
FROM Accounts

Then from this view I pulled my report, and the best I could come up with was

SELECT *
FROM Account_Report
ORDER BY Account COMPUTE SUM(Balance)

I want to do more. Such as Create headers, Justify Left right or center, FORMAT Money Column to only have 2 decimal places, Trim the extra space on the right side of the columns, rename the columns, and scroll down 20 lines at a time.

Any help would be appreciated. My class is over so this is realy all just for the furthering of my own knowlegde.

Noctechie

if you are up to making professional reports you have make use of the reporting services

now for your needs, you have to integrate function in your select statement

here some to start with

String Functions

The following table contains samples of string functions. For more information, see String Functions and Using String Functions.

Function

Description

Example

LCASE( )¹,
LOWER( )

Converts strings to lowercase

SELECT UPPER(substring(lname, 1, 1)) +

LOWER(substring (lname, 2, 99))

FROM employee

Displays a last name after the first character is converted to uppercase and the remaining characters to lowercase.

LTRIM( )

Removes leading spaces from a string

SELECT stor_name, LTRIM(stor_address)

FROM stores

Displays an address column after extraneous spaces are removed from the front.

SUBSTRING( )

Extracts one or more characters from a string

SELECT SUBSTRING(phone,1,3)

FROM employee

Displays the first three characters (the area code) of a phone number.

UCASE( )¹,
UPPER( )

Converts strings to uppercase

SELECT * FROM employee

WHERE UPPER(lname) = 'SMITH'

Converts the contents of the lname column to uppercase before comparing them to a specific value (avoids mismatches if the search is case sensitive). For details about case sensitivity in SQL Server, see Query Designer Considerations .

¹ If calling as an ODBC function, use syntax such as: { fn LCASE(text) }.

Date Functions

The following table contains samples of date functions. For more information, see Date and Time Functions.

Function

Description

Example

DATEDIFF( )

Calculates an interval between two dates.

SELECT fname, lname, hire_date

FROM employee

WHERE DATEDIFF(year, hire_date, getdate()) > 5

Locates all employees hired more than five years ago.

DATEPART( )

Returns the specified portion of a date or datetime column, including the day, month, or year.

SELECT DATEPART(year, hire_date)

FROM employee

Displays only the year in which an employee was hired (not the full date).

CURDATE( )¹,
GETDATE( ) or DATE( )

Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today.

SELECT order_id

FROM orders

WHERE order_date = GETDATE()

Displays orders placed today.

¹ If calling as an ODBC function, use syntax such as: { fn CURDATE() }.

Mathematical Functions

The following functions are typical of those available in many databases. Refer to Mathematical Functions for more information.

Note You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM( ) to create averages and totals in your report. For details, see Summarizing and Grouping.

Function

Description

Example

ROUND( )

Rounds a number off to the specified number of decimal places

SELECT ROUND(qty * (price * discount), 2)

FROM sales

Displays a total price based on a discount, then rounds the results off to two decimal places.

FLOOR( )

Rounds a number down to the nearest (smallest) whole number

UPDATE titles

SET price = FLOOR(price)

Rounds all prices in the titles table down to the nearest whole number.

CEILING( )

Rounds a number up to the nearest whole number

INSERT INTO archivetitle

SELECT title, CEILING(price)

FROM titles

Copies the title and the price (rounded up to the nearest integer) from the titles table to the archivetitle table.

System Functions

The following functions are typical of those available in many databases. For more information, see System Functions.

Function

Description

Example

DATALENGTH( )

Returns the number of bytes used by the specified expression

SELECT DATALENGTH(au_lname + ', '

+ au_fname)

FROM authors

Lists the number of bytes required for the combination of last and first names.

USER( )¹,
USER_NAME( )

Returns the current user name

SELECT company_name, city, phone

FROM customers

WHERE salesperson = USER_NAME()

Creates a list of customers for the salesperson who runs the query.

¹ If calling as an ODBC function, use syntax such as: { fn USER() }.

Other Functions

The following functions illustrate utility functions available in many databases. For more information, see Functions.

Function

Description

Example

CONVERT( )

Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.

SELECT 'Hired: ' + CONVERT(char (11),

hire_date)

FROM employee

Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.

SOUNDEX( )

Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.

SELECT au_lname, au_fname

FROM authors

WHERE SOUNDEX(au_fname) = 'M240'

Searches for names that sound like "Michael".

STR( )

Converts numeric data into a character string so you can manipulate it with text operators.

SELECT str(job_id) + ' ' +

str(job_lvl)

FROM employee

Displays the job_id and job_lvl columns (both numeric) in a single string.