It can be useful to store a particular set of values within a SELECT statement or within a procedure. For example, suppose a company prefers to analyze the results of its sales staff by thirds of a year, instead of by quarter. Since there is no built-in date part for thirds, as there is for quarters, it is necessary to store the dates within the procedure.
WITH thirds ( q_name, q_start, q_end ) AS ( SELECT 'T1', '2000-01-01', '2000-04-30' UNION SELECT 'T2', '2000-05-01', '2000-08-31' UNION SELECT 'T3', '2000-09-01', '2000-12-31' ) SELECT q_name, SalesRepresentative, count(*) AS num_orders, SUM( p.UnitPrice * i.Quantity ) AS total_sales FROM thirds LEFT OUTER JOIN SalesOrders AS o ON OrderDate BETWEEN q_start and q_end KEY JOIN SalesOrderItems AS I KEY JOIN Products AS p GROUP BY q_name, SalesRepresentative ORDER BY q_name, SalesRepresentative; |
This method should be used with care, as the values may need periodic maintenance. For example, the above statement must be modified if it is to be used for any other year.
You can also apply this method within procedures. The following example declares a procedure that takes the year in question as an argument.
CREATE PROCEDURE sales_by_third ( IN y INTEGER ) BEGIN WITH thirds ( q_name, q_start, q_end ) AS ( SELECT 'T1', YMD( y, 01, 01), YMD( y, 04, 30 ) UNION SELECT 'T2', YMD( y, 05, 01), YMD( y, 08, 31 ) UNION SELECT 'T3', YMD( y, 09, 01), YMD( y, 12, 31 ) ) SELECT q_name, SalesRepresentative, count(*) AS num_orders, SUM( p.UnitPrice * i.Quantity ) AS total_sales FROM thirds LEFT OUTER JOIN SalesOrders AS o ON OrderDate BETWEEN q_start and q_end KEY JOIN SalesOrderItems AS I KEY JOIN Products AS p GROUP BY q_name, SalesRepresentative ORDER BY q_name, SalesRepresentative; END; |
The following statement calls the previous procedure.
CALL sales_by_third (2000); |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |