**COMPLICATED RESULT**

Hi
I've a view in MS SQL 2000 with following structure and data sample, and I
want to get the following result ,how it's possible with a select statement?
table1: ( Code1 ,Price1 , Code2 ,Price2) , Code1+Code2 is uique
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
A 12 C 40 10
A 12 D 10 7
A 12 E 15 80
B 30 C 40 2
B 30 D 10 18
B 30 E 15 80
C 40 D 10 6
C 40 E 15 24
D 10 E 15 70
The Process:
At first I want to make the price(12) of code A to 0 by dividing it to code2
(from B to E) with special percent so the reslut of this part will be like
the following:
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
3%*12
A 12 C 40 10
10%*12
A 12 D 10 7
7%*12
A 12 E 15 80
80%*12
B 30 C 40 2
B 30 D 10 18
B 30 E 15 80
C 40 D 10 6
C 40 E 15 24
D 10 E 15 70
now we do the same for next code1 (B) ,but here there's a bit difeerence
cause here we sholud add (3%*12+30) this to the 30 which is located in
Price1 inront of B ,and then divide the whole amount to code2 (from C to E)
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
3%*12
A 12 C 40 10
10%*12
A 12 D 10 7
7%*12
A 12 E 15 80
80%*12
B 30 C 40 2
2%*(30+ 3%*12)
B 30 D 10 18
18%*(30+ 3%*12)
B 30 E 15 80
80%*(30+ 3%*12)
C 40 D 10 6
C 40 E 15 24
D 10 E 15 70
and for C:
Code1 Price1 Code2 Price2 percent
-- -- -- -- --
A 12 B 30 3
3%*12
A 12 C 40 10
10%*12
A 12 D 10 7
7%*12
A 12 E 15 80
80%*12
B 30 C 40 2
2%*(30+ 3%*12)
B 30 D 10 18
18%*(30+ 3%*12)
B 30 E 15 80
80%*(30+ 3%*12)
C 40 D 10 6
6%*(40+2%*(30+ 3%*12))
C 40 E 15 24
24%*(40+2%*(30+ 3%*12))
D 10 E 15 70
Final result is to get the final column,Any help would be thankful.assume ur table name is a1 i give a sample query try this logic for further
results
select CASE WHEN C1='A' THEN
CONVERT(VARCHAR,C1)+CONVERT(VARCHAR,P)+'
%*'+CONVERT(VARCHAR,P1)
WHEN C1='B' THEN
CONVERT(VARCHAR,P)+'%*('+(SELECT CONVERT(VARCHAR,P2)+ '+'
+CONVERT(VARCHAR,P)+'%*'+CONVERT(VARCHAR
,P1)+')' FROM A1 WHERE C2='B' AND
C1='A')
END
AS C1,P1,C2,P2,P from A1
regards
balram
"maryam rezvani" wrote:

> Hi
> I've a view in MS SQL 2000 with following structure and data sample, and I
> want to get the following result ,how it's possible with a select statemen
t?
> table1: ( Code1 ,Price1 , Code2 ,Price2) , Code1+Code2 is uique
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> A 12 C 40 10
> A 12 D 10 7
> A 12 E 15 80
> B 30 C 40 2
> B 30 D 10 18
> B 30 E 15 80
> C 40 D 10 6
> C 40 E 15 24
> D 10 E 15 70
>
> The Process:
> At first I want to make the price(12) of code A to 0 by dividing it to cod
e2
> (from B to E) with special percent so the reslut of this part will be like
> the following:
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> 3%*12
> A 12 C 40 10
> 10%*12
> A 12 D 10 7
> 7%*12
> A 12 E 15 80
> 80%*12
> B 30 C 40 2
> B 30 D 10 18
> B 30 E 15 80
> C 40 D 10 6
> C 40 E 15 24
> D 10 E 15 70
> now we do the same for next code1 (B) ,but here there's a bit difeerence
> cause here we sholud add (3%*12+30) this to the 30 which is located in
> Price1 inront of B ,and then divide the whole amount to code2 (from C to E
)
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> 3%*12
> A 12 C 40 10
> 10%*12
> A 12 D 10 7
> 7%*12
> A 12 E 15 80
> 80%*12
> B 30 C 40 2
> 2%*(30+ 3%*12)
> B 30 D 10 18
> 18%*(30+ 3%*12)
> B 30 E 15 80
> 80%*(30+ 3%*12)
> C 40 D 10 6
> C 40 E 15 24
> D 10 E 15 70
> and for C:
> Code1 Price1 Code2 Price2 percent
> -- -- -- -- --
> A 12 B 30 3
> 3%*12
> A 12 C 40 10
> 10%*12
> A 12 D 10 7
> 7%*12
> A 12 E 15 80
> 80%*12
> B 30 C 40 2
> 2%*(30+ 3%*12)
> B 30 D 10 18
> 18%*(30+ 3%*12)
> B 30 E 15 80
> 80%*(30+ 3%*12)
> C 40 D 10 6
> 6%*(40+2%*(30+ 3%*12))
> C 40 E 15 24
> 24%*(40+2%*(30+ 3%*12))
> D 10 E 15 70
> Final result is to get the final column,Any help would be thankful.
>
>
>|||On Mon, 18 Apr 2005 11:57:26 +0430, maryam rezvani wrote:
>Hi
>I've a view in MS SQL 2000 with following structure and data sample, and I
>want to get the following result ,how it's possible with a select statement?[/color
]
(snip)
Hi maryam,
I don't think it can be done with one single statement. Or rather: if
the maximum number of iterations is known in advance, it might be
possible, but you'd end up with a monster query that won't even print on
a single page. And if the maximum number of iterations is not known,
then it's impossible (though that might change with the introduction of
recursive CTEs in SQL Server 2005).
However, here's a procedure that will do this in as few passes over the
data as possible. I hope I did get the criteria for choosing which
prices should be added to which rows right - I assumed I should not base
this on the alphabetic order of the codes.
CREATE TABLE table1
(Code1 char(1) NOT NULL,
Price1 int NOT NULL,
Code2 char(1) NOT NULL,
Price2 int NOT NULL,
[Percent] int NOT NULL,
Total numeric (10,4) DEFAULT NULL,
PRIMARY KEY (Code1, Code2))
go
INSERT INTO table1 (Code1, Price1, Code2, Price2, [Percent])
SELECT 'A', 12, 'B', 30, 3 UNION ALL
SELECT 'A', 12, 'C', 40, 10 UNION ALL
SELECT 'A', 12, 'D', 10, 7 UNION ALL
SELECT 'A', 12, 'E', 15, 80 UNION ALL
SELECT 'B', 30, 'C', 40, 2 UNION ALL
SELECT 'B', 30, 'D', 10, 18 UNION ALL
SELECT 'B', 30, 'E', 15, 80 UNION ALL
SELECT 'C', 40, 'D', 10, 6 UNION ALL
SELECT 'C', 40, 'E', 15, 24 UNION ALL
SELECT 'D', 10, 'E', 15, 70
go
-- First step
UPDATE table1
SET Total = Price1 * [Percent] / 100.0
WHERE NOT EXISTS
(SELECT *
FROM table1 AS a
WHERE a.Code2 = table1.Code1)
-- Loop over remaining steps
Again:
UPDATE table1
SET Total = ((SELECT MAX(Total)
FROM table1 AS a
WHERE a.Code2 = table1.Code1)
+ Price1) * [Percent] / 100.0
WHERE NOT EXISTS
(SELECT *
FROM table1 AS a
WHERE a.Code2 = table1.Code1
AND a.Total IS NULL)
AND Total IS NULL
-- Repeat until done
IF @.@.ROWCOUNT > 0 GOTO Again
-- Show results
SELECT * FROM table1
go
DROP TABLE table1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The best way to do this would be to use a spreadsheet that queries for the r
aw
data. Barring that, what does the raw data on which the view is based look l
ike?
Does it look something like:
Create Table Codes
(
Code Char(1)
, Price SmallInt
)
How is the percentage calculated?
Thomas|||Your Engish is bad, but I think you are doing a hierarchical
calculation. You night want ot get a copy of TREES & HIERARCHIES IN
SQL and look at examples of such things in the book. These calculation
are much easier with the nested sets model.