|
|
Posted by Erland Sommarskog on 10/01/32 11:42
(lukster@gmail.com) writes:
> I'm trying to create a view that has calculations dependent on
> calculations, where the problem resides is that each time I make a
> calculation I must create an intermediate view so I can reference a
> previous calculation.
>
> for example lets say I have my_table that has columns a & b. now I want
> a view that has a & b, c = a + b, and d = c + 1.
>
> this is grossly simplified, the calculations I actually use are fairly
> complex and copying / pasting them is out of the question.
>
> so what I have is my_view_a which makes column c, and my my_view_final
> which makes column d (however, in my real application I have 5 of these
> views, a/b/c/d/e/)
>
> is there anyway I can consolidate all these views into one? I was
> thinking of using a stored procedure with temp tables or something
> along those lines.
>
> I just which I can use the aliases that I create for c in d in one
> step.
I will have to take a long shot and guess what you are looking for.
I believe that the answer to your question is derived tables. A
derived table is sort of a temp table within the query, but only
logically. The actual computation order can often be different, as
long as the result is the same. Here is a quick example from the
Northind database that presents the number of orders per season.
The query features two derived tables. The innermost extracts day
and month from OrderDate, and the outer table translates the date
to a season.
SELECT Season, COUNT(*)
FROM (SELECT Season = CASE WHEN daymonth BETWEEN '0101' AND '0315' OR
daymonth BETWEEN '1201' AND '1231'
THEN 'Winter'
WHEN daymonth BETWEEN '0316' AND '0531'
THEN 'Spring'
WHEN daymonth BETWEEN '0601' AND '0831'
THEN 'Summer'
WHEN daymonth BETWEEN '0901' AND '1130'
THEN 'Autumn'
END
FROM (SELECT daymonth =
substring(convert(char(8), OrderDate, 112), 5, 4)
FROM Orders) AS O) AS O2
GROUP BY Season
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|