insert statement with select subquery calculations

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

SQL Server 2000 Forums

Transact-SQL (2000)

insert statement with select subquery calculations

Author

Topic

fredong
Yak Posting Veteran

80 Posts

Posted - 2005-06-13 : 12:29:26

Is it possible to to do an insert with select subquery calculation on TL/SQL? Here is my syntax and I am running into error. Pleasev advise. Thanks.

Create table #TempCIT(
VendName varchar(40) null
,VendID char(12)null
,ThreeMthsCOGS Decimal(15,3) null
,SixMthsCOGS Decimal(15,3) null
,TwelveMthsCOGS Decimal(15,3) null
)

Insert #TempCIT(VendName,Vendid,ThreeMthsCOGS,SixMthsCOGS,TwelveMthsCOGS)

select
Vendid
,(select sum(ExtUnitCost) from #temp where #temp1.vendid = #temp.vendid and #temp.postdate >= DateAdd(day, -91,@MonthEND)
AND #temp.postdate <= @MonthEND group by vendid) as #temp1
,(select sum(ExtUnitCost) from #temp where #temp1.vendid = #temp.vendid and #temp.postdate >= DateAdd(day, -182,@MonthEND)
AND #temp.postdate <= @MonthEND group by vendid) as #temp1
,(select sum(ExtUnitCost) from #temp where #temp1.vendid = #temp.vendid and #temp.postdate >= DateAdd(day, -365,@MonthEND)
AND #temp.postdate <= @MonthEND group by vendid) as #temp1
from #temp
group by vendid

k

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-13 : 14:27:18

I don't get a syntax error, but I can't run it without all the #temp, #temp1 definitions.

Post your error and the rest of the code if it isn't too long?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-13 : 14:35:36

Ditto to what Sam said, but without waiting for your reply, I'm gonna guess that you need to do something like this:

Insert #TempCIT(VendName,Vendid,ThreeMthsCOGS,SixMthsCOGS,TwelveMthsCOGS)

SELECT
Vendid,
SUM(CASE WHEN postdate BETWEEN DateAdd(day, -91,@MonthEND) AND @MonthEND THEN ExtUnitCost END) AS ThreeMthsCOGS,
SUM(CASE WHEN postdate BETWEEN DateAdd(day, -182,@MonthEND) AND @MonthEND THEN ExtUnitCost END) AS SixMthsCOGS,
SUM(CASE WHEN postdate BETWEEN DateAdd(day, -365,@MonthEND) AND @MonthEND THEN ExtUnitCost END) AS TwelveMthsCOGS
FROM #temp
GROUP BY Vendid

BTW, I think it would be better to DATEADD months instead of days to get accurate cutoff points for your monthly calculations, unless for some weird reason using days is more accurate for your data.

fredong
Yak Posting Veteran

80 Posts

Posted - 2005-06-13 : 14:54:02

Thanks, that works.

k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources