Query on Multiple Database - 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)

Query on Multiple Database

Author

Topic

casparjiang
Starting Member

5 Posts

Posted - 2011-02-13 : 18:40:14

I have some problem with sql query for PRS reporting system.
There are 2 situations :
• When I don’t sum “axDocument.InvoiceTotal -axDocument.GSTAmount” ,the results comes correctly, just display individually.
•
Now I need sum “axDocument.InvoiceTotal -axDocument.GSTAmount” Up, the result will affect everything, the values for Hours ,Time expenses and expenses become 6 times as before.

What I thought is I need some special Commands for report( within more than 3 Database), Because in other report ,people only use two databases so that it is easy to connect each other.

There are the code below
SELECT axUnit.Code OpsCentre,
axJob.JobNumber,
axJob.ShortName,
axJob.BudgetFees + axJob.BudgetExpenses as TotalBudget,
SUM(axItem.Hours) as 'Hours Spent',
sum(case when itemtypeid = 1 then chargeamount else 0 end) as 'Time Expenses',
sum(case when itemtypeid = 2 then chargeamount else 0 end) as 'Expenses',
sum(case when itemtypeid = 1 then chargeamount else 0 end) + sum(case when itemtypeid = 2 then chargeamount else 0 end) as 'Total $spent',
sum(axDocument.InvoiceTotal -axDocument.GSTAmount) as invoice,
axbusinessunit.code BusinessUnit,
CONVERT(char(12), axJob.Startdate, 3) Startdate,
CONVERT(char(12), axJob.Enddate, 3) as 'End Date',
axJobClassification.Code JobClassification,
axClient.Code Client,
axClient.ShortName ClientName,
axJobStatus.Code JobStatus,
axRateSet.Code RateSet,
Director.ShortName Director,
Manager.ShortName Manager,
SubManager.ShortName SubManager

FROM axJob ,
axJobLink,
axDocument,
axItem,
axJobClassification,
axClient,
axJobStatus,
axRateSet,
axUnit,
axEmployee Director,
axEmployee Manager,
axEmployee SubManager,
axemployeelink, axbusinessunit

WHERE axJob.JobID = axJobLink.JobID
AND axJob.JobID = axDocument.JobID
AND axJob.JobID = axItem.JobID
AND axJob.JobClassificationID = axJobClassification.JobClassificationID
AND axJob.JobStatusID = axJobStatus.JobStatusID
AND axJob.ClientID = axClient.ClientID
AND axJob.JobNumber='SH42919'
AND axJobLink.RateSetID = axRateSet.RateSetID
AND axJobLink.UnitID = axUnit.UnitID
AND axJobLink.JobRole3ID = Director.EmployeeID
AND axJobLink.JobRole2ID = Manager.EmployeeID
AND axJobLink.JobRole1ID = SubManager.EmployeeID
AND Manager.EmployeeID = axEmployeeLink.EmployeeID
AND axEmployeeLink.BusinessUnitID = axBusinessUnit.BusinessUnitID
AND axJob.JobStatusID > 0
AND axUnit.Code = '[Enter OpsCentre Code :]'

Group BY axJob.JobNumber,
axJob.ShortName,
axUnit.Code,
axJob.BudgetFees,
axJob.BudgetExpenses,
axbusinessunit.code,
axJob.Startdate,
axJob.Enddate,
axJobClassification.Code,
axClient.Code,
axClient.ShortName,
axRateSet.Code,
axJobStatus.Code,
axJob.ShortName,
Director.ShortName,
Manager.ShortName,
SubManager.ShortName

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-13 : 18:47:38

sum(axDocument.InvoiceTotal) -SUM(axDocument.GSTAmount) as invoice,

Poor planning on your part does not constitute an emergency on my part.

casparjiang
Starting Member

5 Posts

Posted - 2011-02-13 : 18:57:32

quote:
Originally posted by dataguru1971

sum(axDocument.InvoiceTotal) -SUM(axDocument.GSTAmount) as invoice,

Poor planning on your part does not constitute an emergency on my part.

I tried this way,still show up the same mistake...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-13 : 21:52:44

Sorry ..with all those joins, no sample data , actual result or expected result..kind of hard to say for sure. If you are getting 6 times the correct value, that means something is producing 6 too many records. There is no way for us to know based on the query, because we don't know anything about the underlying structures or data contained therein. Remove the aggregates and be sure a simple select returns the correct amount of records. My guess is..without the aggregates you will have 6 rows for every 1 intended.

Poor planning on your part does not constitute an emergency on my part.

casparjiang
Starting Member

5 Posts

Posted - 2011-02-18 : 00:49:52

anyone have ideas?

casparjiang
Starting Member

5 Posts

Posted - 2011-02-23 : 18:21:44

need help

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 00:40:23

Remove the aggregates and be sure a simple select returns the correct amount of records.

dataGuru has given you one as how to debug this issue

have you tried it?

Without knowing about the structure, relations and data of all Joining Tables its difficult for us to to help you in finding the reason of "expenses become 6 times as before". Therefore, I would suggest you to pick one axJob.JobID as an example .. and start debugging your query for every Join (with out aggregate calculation)

e.g.
1)
Select Col1,col2,col3,...,colN
FROM axJob
Where axJob.JobId=XYZ/123

2)
Select Col1,col2,col3,...,colN
FROM axJob
Inner Join axJobLink ON axJob.JobID = axJobLink.JobID
Where axJob.JobId=XYZ/123

3)

Select Col1,col2,col3,...,colN
FROM axJob
Inner Join axJobLink ON axJob.JobID = axJobLink.JobID
Inner Join axDocument ON axJob.JobID = axDocument.JobID
Where axJob.JobId=XYZ/123

And so on ...

Hopefully and Surely you will find the problem

.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources