Query Problem

# Query Problem

• 6. ledna 2009 11:28

Hi Everyone, i Just started using SQL Server, while doing a SP (StoreProcedure) for Reporting Services, i can't get the result i need.

Btw sry for my poor english, i'll try to explain my self the best i can.

Basically the DB envolves Documents, these docs, are actully Invoices (with diferent Exchange Type, amounts, corresponding to diferents BU (Businnes Units) from diferent Companys bla bla)

What i am trying to make, is a Total of Money to Pay to the providers in each Exchange Type Depending con Company and BU.

Example:
Company: Microsoft Angeles
BU: ITE: 458.15 Dolars (X Quantity of invoices - SUM)
015.25 Pesos (X Quantity of invoices - SUM)
154.98 Euros (X Quantity of invoices - SUM)

Ok, now, i want a total for the BU. I can't just SUM all, cause i'll be summing Bananas with Apples (LOL), so i just convert all those SUM's to an Exchange Type.

What i wasn't able to do, is later on, SUM all the results in a certain Exchange Type to be the final Total to pay.

HMM, god, my english is so rusty.... Anyway, i'll paste the Query. I've tried to do a Point a Temp. Table, but, for now... no results....

QUERY !

DECLARE @CompanyId AS INT
DECLARE @SectorId AS INT
DECLARE @BuId    AS INT

SELECT DISTINCT
Company.Id AS CompanyId,
Company.SapId + ' - ' + Company.Description AS CompanyDescription,
Sector.Id AS SecotrId,
Sector.SapId + ' - ' + Sector.Description AS Sector,
Bu.Id AS BuId,
Bu.Description AS BuDescription,
Moneda.Id AS MonedaId,
SUM (Comprobante.ImporteTotal) AS TotalMoneda,
SUM (Comprobante.ImporteTotal * Moneda.TipoCambio)
FROM
Comprobante
INNER JOIN Company ON Company.Id = Comprobante.EmpresaId
INNER JOIN SectorCompanyBU ON SectorCompanyBu.Companyid = Company.Id
INNER JOIN Sector ON Sector.Id = SectorCompanyBu.SectorId
INNER JOIN Moneda ON Moneda.Id = Comprobante.MonedaId
INNER JOIN BU ON Bu.Id = SectorCompanyBu.BuId
WHERE
Comprobante.EstadoId IN (2, 4, 11, 12) AND
Company.Id = ISNULL (@CompanyId, Company.Id) AND
Sector.Id = ISNULL (@SectorId, Sector.Id) AND
Bu.Id = ISNULL (@BuId, Bu.Id)
GROUP BY
Company.Id,
Company.SapId + ' - ' + Company.Description,
Sector.Id,
Sector.SapId + ' - ' + Sector.Description,
Bu.Id,
Bu.Description,
Moneda.Id,
Moneda.Descripcion

### Všechny reakce

• 30. dubna 2012 7:01

Try to get the result only for two columns. There may be some data issue . As for as the query concerned it looks fine.

So try to get the result only by selecting 2 columns. like

```SELECT Company.Id as CompanyId,
SUM(C.ImporteTotal) as TotalMoneda
FROM  Compprobante C
INNER JOIN Company ON Company.Id = C.EmpresaId
WHERE <Conditions>
Group By <Columns>```
If you are getting result by this then try adding columns and see results. In this way you will see which column is creating issue.

Dharmendra Kumar SE, CSC India. Noida,+91-9911308352