SQL Server Performance Forum – Threads Archive

Summing Vaules across 2 databases

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

---

Kindly do not post multiple (duplicate) posts in different forums, its for your good only.
This thread is locked ashttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3739 this post is appropriate on other forum. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>