sql query

13 Aug 2005 2:44 AM

ichor

hi i can get the result mentioned below using a cursor but want to have a
single query to do it. can any one help.

drop table #cost1
create table #cost1
(
prod_id int ,
cost decimal(9)
)
insert into #cost1 values (1,10000)
insert into #cost1 values (2,10000)
select * From #cost1

drop table #episode_count
create table #episode_count
(
   prod_id int,
   episode_count int
)

insert into #episode_count values(1,4)
insert into #episode_count values(2,2)
select * From #episode_count

result i want
prod_id | episode   | cost
1                     1     2500
1                     2     2500
1                     3     2500
1                     4     2500
2                     1     5000
2                     1     5000

thanks

13 Aug 2005 4:53 AM

Roji. P. Thomas

You can get the desired result by using an auxilary Numbers table.

create table #cost1
(
prod_id int ,
cost decimal(9)
)
insert into #cost1 values (1,10000)
insert into #cost1 values (2,10000)
select * From #cost1

create table #episode_count
(
prod_id int,
episode_count int
)

insert into #episode_count values(1,4)
insert into #episode_count values(2,2)
select * From #episode_count

CREATE TABLE #Numbers(Number int IDENTITY(1,1))
WHILE 1 = 1
BEGIN
INSERT INTO #Numbers DEFAULT VALUES

IF @@IDENTITY = 100
BEGIN
BREAK
END
END
GO

SELECT E.prod_id,
N.Number as Episode,
C.cost / E.episode_count as cost
FROM #episode_count E
INNER JOIN #cost1 C
ON E.prod_id=C.Prod_id
CROSS JOIN #Numbers N
WHERE N.Number <= E.Episode_count

drop table #cost1
drop table #episode_count
drop table #Numbers

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

Show quote

"ichor" <ic***@hotmail.com> wrote in message
news:eguOxD7nFHA.2580@TK2MSFTNGP09.phx.gbl...
> hi i can get the result mentioned below using a cursor but want to have a
> single query to do it. can any one help.
>
> drop table #cost1
> create table #cost1
> (
> prod_id int ,
> cost decimal(9)
> )
> insert into #cost1 values (1,10000)
> insert into #cost1 values (2,10000)
> select * From #cost1
>
> drop table #episode_count
> create table #episode_count
> (
>   prod_id int,
>   episode_count int
> )
>
> insert into #episode_count values(1,4)
> insert into #episode_count values(2,2)
> select * From #episode_count
>
> result i want
> prod_id | episode   | cost
> 1                     1     2500
> 1                     2     2500
> 1                     3     2500
> 1                     4     2500
> 2                     1     5000
> 2                     1     5000
>
>
> thanks
>

13 Aug 2005 1:16 PM

--CELKO--

If you had posted tables with keys and constraints, would your schema
look like this?

CREATE TABLE TotalEpisodeCosts
(prod_id INTEGER NOT NULL PRIMARY KEY,
episode_cost DECIMAL(9) NOT NULL
      CHECK (episode_cost > 0));

CREATE TABLE EpisodeCount
(prod_id INTEGER NOT NULL PRIMARY KEY,
episode_count INTEGER NOT NULL
    CHECK (episode_count > 0));

Keeping a table of a simple integer sequence is a standard SQL
programming trick.

SELECT T.pro_id, SC.seq,
       (T.episode_cost/C.episode_count) AS cost
FROM TotalEpisodeCosts AS T,
       (SELECT T.pro_id, S.seq, C.episode_count
          FROM EpisodeCount AS C,
               Sequence AS S
          WHERE S.seq <= C.episode_count)
        AS SC(prod_id, seq, episode_count)
WHERE SC.prod_id = T.prod_id ;

untested.

22 Aug 2005 7:07 AM

ichor

hi,

this is a cool trick. i want to learn more of these tricks how do i do that?
thanks

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1123938964.795901.317170@g47g2000cwa.googlegroups.com...
> If you had posted tables with keys and constraints, would your schema
> look like this?
>
> CREATE TABLE TotalEpisodeCosts
> (prod_id INTEGER NOT NULL PRIMARY KEY,
> episode_cost DECIMAL(9) NOT NULL
>      CHECK (episode_cost > 0));
>
> CREATE TABLE EpisodeCount
> (prod_id INTEGER NOT NULL PRIMARY KEY,
> episode_count INTEGER NOT NULL
>    CHECK (episode_count > 0));
>
> Keeping a table of a simple integer sequence is a standard SQL
> programming trick.
>
> SELECT T.pro_id, SC.seq,
>       (T.episode_cost/C.episode_count) AS cost
> FROM TotalEpisodeCosts AS T,
>       (SELECT T.pro_id, S.seq, C.episode_count
>          FROM EpisodeCount AS C,
>               Sequence AS S
>          WHERE S.seq <= C.episode_count)
>        AS SC(prod_id, seq, episode_count)
> WHERE SC.prod_id = T.prod_id ;
>
> untested.
>

22 Aug 2005 1:58 PM

AK

>> this is a cool trick. i want to learn more of these tricks how do i do that?
<<

why dont you buy "SQL for Smarties" and/or "SQL Puzzles"