how to design this query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to design this query?

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-08-20 : 09:21:54

table a
id pct
1 0.1
2 0.24
3 0.5

table b
year amount
2001 10
2002 20
2003 30
2004 23
2005 67
2006 80

I want to create a table ret
wiht this
year newamount
2006 80*0.1+67*0.24+23*0.25
2005 67*0.1+27*0.24+30*0.25
2004 23*0.1+30*0.24+20*0.25
2003 30*0.1+20*0.24+10*0.25=9.9

How can i do that.
Thanks.
Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 09:37:33

[code]-- Prepare sample data
DECLARE @TableA TABLE (id INT, pct SMALLMONEY)

INSERT @TableA
SELECT 1, 0.10 UNION ALL
SELECT 2, 0.24 UNION ALL
SELECT 3, 0.25

DECLARE @TableB TABLE (yr SMALLINT, amount SMALLMONEY)

INSERT @TableB
SELECT 2001, 10 UNION ALL
SELECT 2002, 20 UNION ALL
SELECT 2003, 30 UNION ALL
SELECT 2004, 23 UNION ALL
SELECT 2005, 67 UNION ALL
SELECT 2006, 80

-- Show the expected output
SELECT b.yr,
SUM(q.amount * a.pct) AS theValue
FROM @TableB AS b
CROSS JOIN @TableA AS a
INNER JOIN @TableB AS q ON q.yr = b.yr - a.id + 1
GROUP BY b.yr
HAVING COUNT(*) = (SELECT COUNT(*) FROM @TableA)
ORDER BY b.yr DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

jeff06
Posting Yak Master

166 Posts

Posted - 2007-08-20 : 09:37:40

I have created two temp tables, thanks.

CREATE TABLE #a(
id int,
pct real
)
go
insert into #a values (1, 0.1)
insert into #a values (2, 0.24)
insert into #a values (3, 0.5)

select *
from #a

drop table #b
CREATE TABLE #b(
year int,
amount real
)
go
insert into #b values (2001, 10)
insert into #b values (2002, 20)
insert into #b values (2003, 30)
insert into #b values (2004, 23)
insert into #b values (2005, 67)
insert into #b values (2006, 80)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 09:38:11

[code]-- Prepare sample data
DECLARE @TableA TABLE (id INT, pct SMALLMONEY)

INSERT @TableA
SELECT 1, 0.10 UNION ALL
SELECT 2, 0.24 UNION ALL
SELECT 3, 0.5

DECLARE @TableB TABLE (yr SMALLINT, amount SMALLMONEY)

INSERT @TableB
SELECT 2001, 10 UNION ALL
SELECT 2002, 20 UNION ALL
SELECT 2003, 30 UNION ALL
SELECT 2004, 23 UNION ALL
SELECT 2005, 67 UNION ALL
SELECT 2006, 80

-- Show the expected output
SELECT b.yr,
SUM(q.amount * a.pct) AS theValue
FROM @TableB AS b
CROSS JOIN @TableA AS a
INNER JOIN @TableB AS q ON q.yr = b.yr - a.id + 1
GROUP BY b.yr
HAVING COUNT(*) = 3
ORDER BY b.yr DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

jeff06
Posting Yak Master

166 Posts

Posted - 2007-08-20 : 09:45:05

Thank you so much Peso.
I appreciate your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 10:39:31

You're welcome.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources