Advanced queries & merging temptables ???

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Hi Eric, I have another question for you that is a little more complex than the last one I asked......... I need to query a table twice to find the maximum data passed on a particular day which great for one link only, but I need to add together two #temptables to produce one table for that day. Here is the script I have so far. The query is copied for temptable and temptable1 with the exception of the hubslot being different. A solution or advise will be appreciated as if I crack this I can set up the stored procedures to publish traffic stats on the internal web site automatically. This would automate the whole process and free up 5 hours of my week. Many thanks for listening,

Dave Stout

select distinct collperid, outbps, inbps, outpps, inpps, percent_outroute = sum(outbps) / 4800 into #temptable from hubdata where datediff (day,collperid,getdate()) < 7 and chassis = 6 and hubslot = 1 group by collperid, outbps, inbps, outpps, inpps select date=convert(datetime, datename(month, collperid)+' '+datename(day, collperid)+' '+datename(year, collperid)), outbps = max(outbps), inbps = max(inbps), outpps = max(outpps), inpps = max(inpps), percent_outroute = max(percent_outroute) from #temptable group by convert(datetime, datename(month, collperid)+' '+datename(day, collperid)+' '+datename(year, collperid)) order by convert(datetime, datename(month, collperid)+' '+datename(day, collperid)+' '+datename(year, collperid)) go select distinct collperid, outbps, inbps, outpps, inpps, percent_outroute = sum(outbps) / 4800 into #temptable1 from hubdata where datediff (day,collperid,getdate()) < 7 and chassis = 6 and hubslot = 2 group by collperid, outbps, inbps, outpps, inpps select date=convert(datetime, datename(month, collperid)+' '+datename(day, collperid)+' '+datename(year, collperid)), outbps = max(outbps), inbps = max(inbps), outpps = max(outpps), inpps = max(inpps), percent_outroute = max(percent_outroute) from #temptable1 group by convert(datetime, datename(month, collperid)+' '+datename(day, collperid)+' '+datename(year, collperid)) order by convert(datetime, datename(month, collperid)+' '+datename(day, collperid)+' '+datename(year, collperid)) go

-- Anonymous, July 30, 1999

Answers

Dave,

You need to use the UNION operator. You can read all about it in the article titled, appropriately enough, "The Union Operator" in (the 6.5 Version, at least) SQL Server Books Online.

The syntax of your final statement then is:

SELECT select_list1 [INTO clause] [FROM clause] [WHERE clause] [GROUP BY clause] UNION SELECT select_list2 [FROM clause] [WHERE clause] [GROUP BY clause] [ORDER BY clause]

Good Luck,

Eric

-- Anonymous, July 30, 1999


Moderation questions? read the FAQ