Query Problem

Question

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,
    Moneda.Descripcion AS MonedaDescription,
    SUM (Comprobante.ImporteTotal) AS TotalMoneda,
    SUM (Comprobante.ImporteTotal * Moneda.TipoCambio)
FROM
    Comprobante
INNER JOIN Estado ON Estado.Id = Comprobante.EstadoId
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
   
   
   

Answer 1

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,[phone]