Combine 3 result sets into 1 - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Combine 3 result sets into 1

Author

Topic

RegTyler
Starting Member

11 Posts

Posted - 2007-04-18 : 08:36:41

This stored procedure generates three result sets. I'm trying to combine the three result sets into a single result set. I tried to use the UNION operator but this threw some errors.

This is what I have working so far

-- Add the parameters for the stored procedure here
(@StartDate VARCHAR(30),
@EndDate VARCHAR(30))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Pull the data from some views
SELECT cbe.USERID AS BEMPLOYEE, cbe.AMBASSADOR, cbe.TotalCustomers, acbe.ActiveCustomers AS [Active Customers],
cbe.TotalCustomers - acbe.ActiveCustomers AS [Inactive Customers], otb.NumBuyers AS [One Time Buyers]
FROM dbo.vwTotalCustomersByEmployee AS cbe INNER JOIN
dbo.vwSummaryOneTimeBuyersByEmployee AS otb ON cbe.USERID = otb.BEMPLOYEE LEFT OUTER JOIN
dbo.vwSummaryActiveCustomersByEmployee AS acbe ON cbe.USERID = acbe.BEMPLOYEE
--compute
SELECT SUM(PRICE) AS TotalSales, BEMPLOYEE
FROM dbo.vwSummaryCustReportTransactions AS crt
WHERE TRANSDATE BETWEEN @STARTDATE AND @ENDDATE
GROUP BY BEMPLOYEE
END

SELECT Count(dbo.LMCUST.CUSTID) AS [New Entries], BEMPLOYEE
FROM dbo.LMCUST
WHERE (FVISIT >= @StartDate AND FVISIT <= @EndDate)
GROUP BY BEMPLOYEE

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-18 : 10:58:01

You need to use a JOIN not a UNION. Something like this should work

SELECT cbe.USERID AS BEMPLOYEE, cbe.AMBASSADOR, cbe.TotalCustomers, acbe.ActiveCustomers AS [Active Customers], 
cbe.TotalCustomers - acbe.ActiveCustomers AS [Inactive Customers], otb.NumBuyers AS [One Time Buyers]
FROM dbo.vwTotalCustomersByEmployee AS cbe 
INNER JOIN dbo.vwSummaryOneTimeBuyersByEmployee AS otb 
    ON cbe.USERID = otb.BEMPLOYEE 
LEFT OUTER JOIN dbo.vwSummaryActiveCustomersByEmployee AS acbe 
    ON cbe.USERID = acbe.BEMPLOYEE
LEFT OUTER JOIN (SELECT SUM(PRICE) AS TotalSales, BEMPLOYEE
FROM dbo.vwSummaryCustReportTransactions AS crt
WHERE TRANSDATE BETWEEN @STARTDATE AND @ENDDATE
GROUP BY BEMPLOYEE) AS Totals 
    ON Totals.BEMPLOYEE = cbe.USERID
LEFT OUTER JOIN (SELECT Count(dbo.LMCUST.CUSTID) AS [New Entries], BEMPLOYEE
FROM dbo.LMCUST 
WHERE (FVISIT >= @StartDate AND FVISIT <= @EndDate) 
GROUP BY BEMPLOYEE) AS Counts
    ON Counts.BEMPLOYEE = cbe.USERID

RegTyler
Starting Member

11 Posts

Posted - 2007-04-20 : 05:53:19

Thanks for pointing me in the right direction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources