**Dynamic SQL**

Hi
I'm working with SQL 2000 and I have a table with following struncture and I
want to get the result as I described below ,but I don't know how?
PAP (PART1 NUMERIC(3) ,PART2 NUMERIC(3) ,QTY NUMERIC(2))
Primary Key (part1+part2)
part1 part2 qty
-- -- --
100 205 5
100 204 6
101 203 7
205 107 1
107 116 2
Result:
I want to show following result when we select data for part1=100 and
part2=205:
part1 part2 qty finalqty
-- -- -- --
100 205 5 2
I used following code to get the result and it was successfull
select a.par1,a.part2,a.qty,c.qty2
from pap a,pap b,pap c
where a.part2=b.part1
and b.part2=c.part1
but what I really s for,is the number of join
is not static,because if a new row inserted in the table the result would
be different for example:
new row : 116 114 3
then
the result:
part1 part2 qty finalqty
-- -- -- --
100 205 5 3
I want a sql statement to fetch the result witjout dependecy to the special
number of joins.
Thanks alot
new row:The very first question you'll have to answer is how do you know which row
is the "new" row. Once that is clear you can do something like this.
e.g.
create table pap (part1 numeric(3) ,part2 numeric(3) ,qty numeric(2),
primary key (part1,part2)
)
go
insert pap (part1,part2,qty)
select 100, 205, 5
union all select 100, 204, 6
union all select 101, 203, 7
union all select 205, 107, 1
union all select 107, 116, 2
go
--pick the last qty
select *,(select top 1 qty from pap order by part1 desc,part2 desc) lastqty
from pap
where part1=100 and part2=205
go
drop table pap
go
-oj
"M R" <rezvani@.parskhazar.net> wrote in message
news:eCGJAfbZFHA.1412@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm working with SQL 2000 and I have a table with following struncture and
> I
> want to get the result as I described below ,but I don't know how?
> PAP (PART1 NUMERIC(3) ,PART2 NUMERIC(3) ,QTY NUMERIC(2))
> Primary Key (part1+part2)
> part1 part2 qty
> -- -- --
> 100 205 5
> 100 204 6
> 101 203 7
> 205 107 1
> 107 116 2
> Result:
> I want to show following result when we select data for part1=100 and
> part2=205:
> part1 part2 qty finalqty
> -- -- -- --
> 100 205 5 2
> I used following code to get the result and it was successfull
> select a.par1,a.part2,a.qty,c.qty2
> from pap a,pap b,pap c
> where a.part2=b.part1
> and b.part2=c.part1
> but what I really s for,is the number of join
> is not static,because if a new row inserted in the table the result would
> be different for example:
> new row : 116 114 3
> then
> the result:
> part1 part2 qty finalqty
> -- -- -- --
> 100 205 5 3
> I want a sql statement to fetch the result witjout dependecy to the
> special
> number of joins.
> Thanks alot
> new row:
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details, but for now,
Google up the "Nested set model". Here is how to travel to the leaf
nodes from any point in the tree.
SELECT @.my_part, T1.qty
FROM Tree AS T1, Tree AS T2
WHERE T1.rgt-T1.lft =1 -- leaf node
AND T1.lft BETWEEN T2.lft AND T2.rgt
AND T2.part = @.my_part;|||It doesn't matter to recognize the new record ,because if we have a correct
sql statement it automatically will be fetched in the result,and your sql
statement is not suitable
select *,(select top 1 qty from pap order by part1 desc,part2 desc) lastqty
from pap
where part1=100 and part2=205
cause the last qty is not the qty with the greatest part1 and part2,we
select the qty as a last one by joining the part2 with part1 in this table
and repeat this action to find the ast one.
"oj" <nospam_ojngo@.home.com> wrote in message
news:#CR18acZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> The very first question you'll have to answer is how do you know which row
> is the "new" row. Once that is clear you can do something like this.
> e.g.
> create table pap (part1 numeric(3) ,part2 numeric(3) ,qty numeric(2),
> primary key (part1,part2)
> )
> go
> insert pap (part1,part2,qty)
> select 100, 205, 5
> union all select 100, 204, 6
> union all select 101, 203, 7
> union all select 205, 107, 1
> union all select 107, 116, 2
> go
> --pick the last qty
> select *,(select top 1 qty from pap order by part1 desc,part2 desc)
lastqty
> from pap
> where part1=100 and part2=205
> go
> drop table pap
> go
>
> --
> -oj
>
> "M R" <rezvani@.parskhazar.net> wrote in message
> news:eCGJAfbZFHA.1412@.TK2MSFTNGP12.phx.gbl...
and
would
>|||Well, you didn't mention the child/parent relationship in your post and I
didn't bother reading each value to see the trend. So, what you're after is
the hierachy problem. Y
You want to take a look at the example in book online or use Celko's or
this.
--adding another left join
--if you need to go level deeper.
select p1.*,coalesce(p3.qty,p2.qty,p1.qty) last_qty
from pap p1 left join pap p2 on p1.part2=p2.part1
left join pap p3 on p2.part2=p3.part1
where p1.part1=100 and p1.part2=205
-oj
"M R" <rezvani@.parskhazar.net> wrote in message
news:ulcBPvlZFHA.3732@.TK2MSFTNGP10.phx.gbl...
> It doesn't matter to recognize the new record ,because if we have a
> correct
> sql statement it automatically will be fetched in the result,and your sql
> statement is not suitable
> select *,(select top 1 qty from pap order by part1 desc,part2 desc)
> lastqty
> from pap
> where part1=100 and part2=205
> cause the last qty is not the qty with the greatest part1 and part2,we
> select the qty as a last one by joining the part2 with part1 in this table
> and repeat this action to find the ast one.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:#CR18acZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> lastqty
> and
> would
>

**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.