SQL Server Performance Forum – Threads Archive
Result from few Datasets output in 1 table
Hi all … I have a problem of putting all the results together from few datasets. Since I have a sql query statement which output more than 1 recordset, and the multiple results from the query will be passed to another new query as parameter.<br />So can I know anyone got any idea of that? thankssss in advance[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
You can try use a Union query
you are trying to do a rollup of data ? Rollup
=======
1) create a sp
2) In the sp create a #temp table
3) insert into #temp with your various SELECTS
4) use standard SELECT ( and sum(field)if needed) with GROUP BY from the temp table Note: if you are doing std star fact-table – dim-table datawarehousing create view its makes rollups much easier
Hi Knarf, Do you mind telling me in detail how to do the Rollup process that you mentioned above? Because I am beginner to this reporting service. I know how to create a sp, but how to create #temp table in sp? how to insert various SELECTs into #temp? Cheers…
OK sample of simple rollup getting YTD (year to date)
and PYTD (previous year to date) from an orders table. I’ve not checked or parsed code so expect errors #########################################
CREATE PROCEDURE usp_ytdrollup @Year Int
AS — CREATE TEMP TABLE create #temp ( customer, YTD real, PYTD real) — INSERT RECORDS INTO #temp insert into #temp
select customer, sum(ordervalue) as YTD, 0 as PYTD
FROM ORDERS
Group By customer
Where (DatePart(yyyy, OrderDate)) = @Year
insert into #temp
select customer, 0 as YTD, sum(ordervalue) as PYTD
FROM ORDERS
Group By customer
Where (DatePart(yyyy, OrderDate)) = (@Year – 1)
— GET RECORDS FROM TEMP TABLE select customer, sum(YTD), sum(PYTD)
from #temp
group by customer GO
==============================================
contents of # temp
——————
cust1 443 0
cust1 0 334 Output
———
cust1 433 334
So YTD and PTD appear on the same row.
ok .. now I have created a stored procedure that basically follow the example above.
Am I going to create the temptable manually before running the store procedure OR the temptable will be created once you run the stored procedure?? My temp table name is called "temptable". Because I got another problem when I run it in Reporting Service. Error is : Invalid object name temptable.
" Reporting Service. Error is : Invalid object name temptable." Are you creating a real temp table i.e. with # at the start, if you are you have run it witihn a SP or witihn an SP that calls other SPs.
Frank
Below is the sample of the store procedure that I created. *************************************************************
CREATE PROCEDURE usp_gettotalseats
@oprndate datetime
AS create #temp (filmcd,filmtitle,seats) insert into #temp
select bkg_film_cd, 0 as filmtitle, sum(bkg_total_seats)
FROM G2_TICKET_BOOKINGS
Group By bkg_film_cd
Where bkg_oprn_date>@oprndate insert into #temp
select flm_film_cd, flm_film_title, 0 as seats
from g2_Films
Group By flm_film_cd select filmcd,filmtitle,sum(seats)
from #temp
group by filmcd go
************************************************************** Any wrong there? cheers…..
Lots wrong with your SP,
You have to check the #temp table fields match your field form your
other tables This should work; Syntax checked OK ##############################################################
CREATE PROCEDURE usp_gettotalseats
@oprndate datetime
AS create table #temp (filmcd varchar(30), filmtitle varchar(30) ,seats int) insert into #temp
select bkg_film_cd, 0 as filmtitle, sum(bkg_total_seats)
FROM G2_TICKET_BOOKINGS
Where bkg_oprn_date>@oprndate
Group By bkg_film_cd insert into #temp
select flm_film_cd, flm_film_title, 0 as seats
from g2_Films
Group By flm_film_cd select filmcd,filmtitle,sum(seats)
from #temp
group by filmcd go
#################################################################################### Frank
Knarf, still the same thing happened after I change the declaration. Invalid object name ‘#temp’.
Any help? cheers
The exact error is – Could not generate a list of fields for the query. Invalid object name #temp.
cheers
Place it in query anylsizer and run it one statement at a time.
]]>