SQL Server Performance Forum – Threads Archive

Summing Values across different 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

---

dont know if it matters but you have
AND PCHVALUE > 0 in your validiation routine but not in the big merged query

---

It does not matter. Even if I had it in the merged query, it still does not return the correct records/values.

---

Okay, then I’ll take a closer look to see whats up. I’ll let you know. Chris

---

Silly me, should have seen this right away.
You’re joining on branch (only join to KPI data) and then summing.
So if you have 100 branch 61 records in the first set of tables and 10 records in the second, then each of the 10 will join to each of the 100 creating 1000 results and then it will sum them. Obviously not what you want. You want to sum each, join and then add the results together. So (and I may get some syntax a bit off)
SELECT USERID2.dbo.tblBranch.brnBranchName,
SUM(ExportData.dbo.tblWeeklySales.wsrSales) + KPITurn.Turnover 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)
JOIN (select branchno,sum(ZCP) as Turnover
from KPIData.dbo.tblkpiinfo
where BranchNo =’061′ –left in to make the subquery faster for testing
AND KPIData.dbo.tblKPIInfo.MonthNo >= 1
AND KPIData.dbo.tblKPIInfo.MonthNo <= 4
AND KPIData.dbo.tblKPIInfo.YearNo = 2003
AND PCHVALUE > 0
group by BranchNo) as KPITurn on KPITurn.BranchNo = RIGHT(USERID2.dbo.tblBranch.brnBranchNumber, 3)
WHERE (RIGHT(ExportData.dbo.tblWeeklySales.wsrcusBrn, 3) = ‘061’)
(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
ORDER BY Gross_Profit DESC
You dont need to use the where branch = ‘061’ for the KPI data in either the subquery or the outer query since you are joining on branch. If you only ever want branch 061 leave it in, but if this is just to validate data then you want all branches, remove it from the subquery (and of course the where on branch in export data too)

---

]]>