CODEWITHSUNDEEP
sql-server
Matias
Matias

Reputation: 471

Microsoft SQL Server : SUM and LEFT JOIN double values

I am trying to get data from an contability software that is using Microsoft SQL Server 2008.

I am trying to get the total of money and to get a field called Numero de Obra (number of work) from an invoice in one line, the problem is a need to sum some items to get the total, and when i get the extra field number of work my data is getting duplicated.

This is the query:

SELECT 
    GVA12.FECHA_EMIS AS [Fecha de emisión] ,
    GVA53.T_Comp AS [Tipo comprobante] ,
    GVA53.N_Comp AS [Nro. comprobante] ,
    GVA12.COD_VENDED AS [Cód. vendedor] ,
    CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END AS [Nombre Vendedor] ,
    GVA12.COD_CLIENT AS [Cód. cliente] ,
    CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END AS [Razón social] ,
    GVA12.COTIZ AS [Cotización] ,
    GVA45.[DESC] as [Num_Obra]],
    SUM(CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END * GVA53.CANTIDAD) AS [Cantidad] ,
    SUM( CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END *      CASE GVA12.Cotiz WHEN 0 THEN 0 ELSE           CASE 'BIMONCTE'               WHEN 'BIMONCTE' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  ELSE GVA53. IMP_NETO_P  * GVA12.COTIZ END)                WHEN 'BIORIGEN' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / GVA12.COTIZ ELSE GVA53.IMP_NETO_P  END)                WHEN 'BICOTIZ'  THEN(CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / 1 ELSE GVA53.IMP_NETO_P  * GVA12.COTIZ  / 1 END)           END       END ) AS [Total] 
FROM 
    GVA12 (NOLOCK)  
INNER JOIN 
    GVA53 (NOLOCK) ON GVA53.T_COMP = GVA12.T_COMP AND GVA53.N_COMP = GVA12.N_COMP 
 INNER JOIN 
    GVA23 (NOLOCK) ON GVA12.COD_VENDED = GVA23.COD_VENDED
 LEFT JOIN 
    GVA14 (NOLOCK) ON GVA12.COD_CLIENT = GVA14.COD_CLIENT
LEFT JOIN 
    GVA15 ON GVA15.IDENT_COMP = GVA12.T_COMP
LEFT JOIN 
    GVA45 (NOLOCK) ON GVA53.COD_ARTICU = 'NºOBRA' AND
GVA45.N_COMP = GVA53.N_COMP AND GVA45.N_RENGLON = GVA53.N_RENGL_V 
WHERE 
    (GVA53.COD_ARTICU <> 'Art. Ajuste') AND (GVA53.COD_ARTICU <> '')
    AND (GVA53.RENGL_PADR = 0 OR GVA53.INSUMO_KIT_SEPARADO =1)
GROUP BY 
    GVA12.FECHA_EMIS , GVA53.T_Comp , GVA53.N_Comp , GVA12.COD_VENDED , CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END , GVA12.COD_CLIENT , CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END , GVA12.COTIZ,GVA45.[DESC]

My current output:

number_invoce     client          Number_Of_Work  Total         
FAC A000500000354 Hospital        NULL              90
FAC A000500000354 Hospital        346300            0.000000

My expected output:

number_invoce     client          Number_Of_Work    Total         
A000500000354     Hospital        346300            90

I have a lot of different tables, most of them are not related to the question, so i will only explain the ones that matter:

GVA53: It have all the lines from an invoice

For example will be

   number_invoce COD_ARTICU      AMOUNT N_RENGL_V
   A000500000354 Wall            $50    1
   A000500000354 Roof            $40    2
   A000500000354 number_of_work  $0     3

GVA45: It have the description of an article

   number_invoce DESC   N_RENGL
   A000500000354        1
   A000500000354        2
   A000500000354 346300 3

In resume: I need to get the total from an invoice, I need to sum the items, and I need to get the DESC (description) field from the item number of work, relating n_rengl(number of line) from GV45 to n_rengl_v of GV53 and where COD_ARTIC is equal to number_of_work. Everything is working but I don't get why my data is getting duplicated.

Thank you very much

Upvotes: 0

Views: 316

Answers (1)

xQbert
xQbert

Reputation: 35333

Ok one last try :P

As suggested in comments I'm not sure the value of noLock here but as they were not causing the problem you wanted to address I left them in presently. I don't condone the use however.

The issue is the group by on GVA45.[DESC] is resulting in two values null due to the left joins and an actual value. Since you only want the values, you need to have the system return the max and eliminate it from the group by.

This may do it... 

SELECT 
    GVA12.FECHA_EMIS AS [Fecha de emisión] ,
    GVA53.T_Comp AS [Tipo comprobante] ,
    GVA53.N_Comp AS [Nro. comprobante] ,
    GVA12.COD_VENDED AS [Cód. vendedor] ,
    CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END AS [Nombre Vendedor] ,
    GVA12.COD_CLIENT AS [Cód. cliente] ,
    CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END AS [Razón social] ,
    GVA12.COTIZ AS [Cotización] ,
    max(GVA45.[DESC]) as [Num_Obra]],
    SUM(CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END * GVA53.CANTIDAD) AS [Cantidad] ,
    SUM( CASE WHEN GVA12.T_Comp <> 'FAC' and GVA15.Tipo_Comp = 'C' then (-1) ELSE (1) END *      CASE GVA12.Cotiz WHEN 0 THEN 0 ELSE           CASE 'BIMONCTE'               WHEN 'BIMONCTE' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  ELSE GVA53. IMP_NETO_P  * GVA12.COTIZ END)                WHEN 'BIORIGEN' THEN (CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / GVA12.COTIZ ELSE GVA53.IMP_NETO_P  END)                WHEN 'BICOTIZ'  THEN(CASE GVA12.MON_CTE WHEN 1 THEN GVA53.IMP_NETO_P  / 1 ELSE GVA53.IMP_NETO_P  * GVA12.COTIZ  / 1 END)           END       END ) AS [Total] 
FROM 
    GVA12 (NOLOCK)  
INNER JOIN 
    GVA53 (NOLOCK) ON GVA53.T_COMP = GVA12.T_COMP AND GVA53.N_COMP = GVA12.N_COMP 
 INNER JOIN 
    GVA23 (NOLOCK) ON GVA12.COD_VENDED = GVA23.COD_VENDED
 LEFT JOIN 
    GVA14 (NOLOCK) ON GVA12.COD_CLIENT = GVA14.COD_CLIENT
LEFT JOIN 
    GVA15 ON GVA15.IDENT_COMP = GVA12.T_COMP
LEFT JOIN 
    GVA45 (NOLOCK) ON GVA53.COD_ARTICU = 'NºOBRA' AND
GVA45.N_COMP = GVA53.N_COMP AND GVA45.N_RENGLON = GVA53.N_RENGL_V 
WHERE 
    (GVA53.COD_ARTICU <> 'Art. Ajuste') AND (GVA53.COD_ARTICU <> '')
    AND (GVA53.RENGL_PADR = 0 OR GVA53.INSUMO_KIT_SEPARADO =1)
GROUP BY 
    GVA12.FECHA_EMIS , GVA53.T_Comp , GVA53.N_Comp , GVA12.COD_VENDED , CASE GVA12.COD_VENDED WHEN '**' THEN 'CONTADO' ELSE GVA23.NOMBRE_VEN END , GVA12.COD_CLIENT , CASE GVA12.COD_CLIENT WHEN '000000' THEN 'OCASIONAL' ELSE GVA14.RAZON_SOCI END , GVA12.COTIZ

Upvotes: 1

Related Questions