Hi Group,
I need to be able to sum the values of 2 different fields which are in 2 different databases on the SAME Server.
My original query summed just the value in one database. So I wrote a separate query for the other database as a check to see if when I do the final query the values would match.

They don't.

Here is my original query:

SELECT USERID2.dbo.tblBranch.brnBranchName,
SUM(ExportData.dbo.tblWeeklySales.wsrSales) AS Turnover,
SUM(ExportData.dbo.tblWeeklySales.wsrProfit)
AS Gross_Profit
FROM UserID2.dbo.tblRegion INNER JOIN
UserID2.dbo.tblArea ON
UserID2.dbo.tblRegion.regRegionID = UserID2.dbo.tblArea.areRegionID
INNER JOIN
UserID2.dbo.tblBranch ON
UserID2.dbo.tblArea.areAreaID = UserID2.dbo.tblBranch.brnAreaID
INNER JOIN
ExportData.dbo.tblWeeklySales ON
RIGHT(USERID2.dbo.tblBranch.brnBranchNumber, 3)
= RIGHT(ExportData.dbo.tblWeeklySales.wsrCusBrn, 3)
WHERE (RIGHT(ExportData.dbo.tblWeeklySales.wsrcusBrn, 3)
= '061') AND (DATEPART(m,
ExportData.dbo.tblWeeklySales.wsrWERun) >= 1) AND
(DATEPART(m, ExportData.dbo.tblWeeklySales.wsrWERun)
<= 4) AND (DATEPART(yy,
ExportData.dbo.tblWeeklySales.wsrWERun) = 2003)
GROUP BY USERID2.dbo.tblBranch.brnBranchName


This is the intermediate query I used to check the results from the second database BEFORE I try summing the values:

use kpidata
select branchno,
sum(ZCP) as Turnover
from tblkpiinfo
where BranchNo ='061'
AND KPIData.dbo.tblKPIInfo.MonthNo >= 1
AND KPIData.dbo.tblKPIInfo.MonthNo <= 4
AND KPIData.dbo.tblKPIInfo.YearNo = 2003
AND PCHVALUE > 0
group by BranchNo

I've checked the value returned from this query.

This is the query that sums(supposedly) the two fields from the 2 databases. However it does not give me the correct value.

SELECT USERID2.dbo.tblBranch.brnBranchName,
SUM(ExportData.dbo.tblWeeklySales.wsrSales)
+ SUM(kpidata.dbo.tblkpiinfo.ZCP) AS Turnover,
SUM(ExportData.dbo.tblWeeklySales.wsrProfit)
AS Gross_Profit
FROM UserID2.dbo.tblRegion INNER JOIN
UserID2.dbo.tblArea ON
UserID2.dbo.tblRegion.regRegionID = UserID2.dbo.tblArea.areRegionID
INNER JOIN
UserID2.dbo.tblBranch ON
UserID2.dbo.tblArea.areAreaID = UserID2.dbo.tblBranch.brnAreaID
INNER JOIN
ExportData.dbo.tblWeeklySales ON
RIGHT(USERID2.dbo.tblBranch.brnBranchNumber, 3)
= RIGHT(ExportData.dbo.tblWeeklySales.wsrCusBrn, 3)
INNER JOIN
KPIData.dbo.tblKPIInfo ON (KPIData.dbo.tblKPIInfo.BranchNo)
= RIGHT(USERID2.dbo.tblBranch.brnBranchNumber, 3)
WHERE (RIGHT(ExportData.dbo.tblWeeklySales.wsrcusBrn, 3)
= '061') AND KPIData.dbo.tblKPIInfo.BranchNo = '061' AND
(DATEPART(m, ExportData.dbo.tblWeeklySales.wsrWERun)
>= 1) AND (DATEPART(m,
ExportData.dbo.tblWeeklySales.wsrWERun) <= 4) AND
(DATEPART(yy, ExportData.dbo.tblWeeklySales.wsrWERun)
= 2003) AND KPIData.dbo.tblKPIInfo.MonthNo >= 1 AND
KPIData.dbo.tblKPIInfo.MonthNo <= 4 AND
KPIData.dbo.tblKPIInfo.YearNo = 2003
GROUP BY USERID2.dbo.tblBranch.brnBranchName
ORDER BY Gross_Profit DESC

FYI:The BranchNo in the tblKPIInfo is 3 characters. Slightly different convention used in this database!!
TIA