Validar campo anterior y decidir si se inserta un valor

[gojusaul]

Hola, tengo una consulta que me trabaja bien, pero para fines de simplificar la vida de mis usuarios necesito que el campo INVOICE AMOUNT no muestre valor si la línea habla de un movimiento para el mismo valor del campo INVOICE FOLIO.

La consulta es la siguiente:

Código:

SELECT    TOP (100) PERCENT dbo.admAgentes.CNOMBREAGENTE AS [SALES REP], dbo.admClientes.CRAZONSOCIAL AS CUSTOMER, admDocumentos_1.CSERIEDOCUMENTO AS [INVOICE SERIE], 
                      admDocumentos_1.CFOLIO AS [INVOICE FOLIO], admDocumentos_1.CFECHA AS [INVOICE DATE], 
                      
                      admDocumentos_1.CTOTAL  AS [INVOICE AMOUNT], 
                      
                      admDocumentos_1.CTEXTOEXTRA1 AS [INVOICE PO], dbo.admDocumentos.CSERIEDOCUMENTO AS [TYPE OF PAYMENT], dbo.admDocumentos.CFOLIO AS [PAYMENT FOLIO], 
                      CASE dbo.admDocumentos.CSERIEDOCUMENTO WHEN 'PAY' THEN dbo.admAsocCargosAbonos.CIMPORTEABONO ELSE - dbo.admAsocCargosAbonos.CIMPORTEABONO END AS [PAYMENT AMOUNT],
                       dbo.admDocumentos.CFECHA AS [PAYMENT DATE], dbo.admDocumentos.CREFERENCIA AS [CHECK NUMBER], dbo.admClientes.CCOMVENTA / 100 AS '%', 
                      CASE dbo.admDocumentos.CSERIEDOCUMENTO WHEN 'PAY' THEN dbo.admAsocCargosAbonos.CIMPORTEABONO ELSE - dbo.admAsocCargosAbonos.CIMPORTEABONO END * (dbo.admClientes.CCOMVENTA
                       / 100) AS COMISSION, ROW_NUMBER() OVER (ORDER BY dbo.admAgentes.CNOMBREAGENTE, admDocumentos_1.CFOLIO, dbo.admDocumentos.CSERIEDOCUMENTO)as ROW
FROM         dbo.admDocumentos INNER JOIN
                      dbo.admClientes ON dbo.admDocumentos.CIDCLIENTEPROVEEDOR = dbo.admClientes.CIDCLIENTEPROVEEDOR INNER JOIN
                      dbo.admAgentes ON dbo.admClientes.CIDAGENTEVENTA = dbo.admAgentes.CIDAGENTE INNER JOIN
                      dbo.admAsocCargosAbonos ON dbo.admDocumentos.CIDDOCUMENTO = dbo.admAsocCargosAbonos.CIDDOCUMENTOABONO INNER JOIN
                      dbo.admDocumentos AS admDocumentos_1 ON dbo.admAsocCargosAbonos.CIDDOCUMENTOCARGO = admDocumentos_1.CIDDOCUMENTO INNER JOIN
                      dbo.admMovimientos ON dbo.admDocumentos.CIDDOCUMENTO = dbo.admMovimientos.CIDDOCUMENTO INNER JOIN
                      dbo.admProductos ON dbo.admMovimientos.CIDPRODUCTO = dbo.admProductos.CIDPRODUCTO
WHERE     (dbo.admDocumentos.CFECHA BETWEEN '01/12/2015' AND '31/12/2015') AND (dbo.admDocumentos.CIDDOCUMENTODE = 9) AND (dbo.admDocumentos.CCANCELADO = 0) AND 
                      (dbo.admAgentes.CNOMBREAGENTE <> '(Ninguno)') AND (dbo.admAgentes.CNOMBREAGENTE <> 'AMLOID CORPORATION') AND (dbo.admProductos.CCODIGOPRODUCTO <> 'F2')
ORDER BY [SALES REP], [INVOICE FOLIO], [TYPE OF PAYMENT]

Un ejemplo de resultado:

Código HTML:

BOE & ASSOCIATES	Bluestem Brands, Inc	FE	9599	2015-11-19 00:00:00.000	4365
BOE & ASSOCIATES	Bluestem Brands, Inc	FE	9599	2015-11-19 00:00:00.000	4365

Y lo que necesitaría como resultado:

Código HTML:

BOE & ASSOCIATES	Bluestem Brands, Inc	FE	9599	2015-11-19 00:00:00.000	4365
BOE & ASSOCIATES	Bluestem Brands, Inc	FE	9599	2015-11-19 00:00:00.000	0.00

Espero me puedan ayudar. Saludos!!

[Libras]

y un select distinct no te sirve??? y para que el select top(100) percent??

[gojusaul]

Iniciado por Libras

y un select distinct no te sirve??? y para que el select top(100) percent??

El SELECT DISTINCT no me sirve, explicaré un poco la lógica del reporte:

Muestra el numero de factura y los cargos o abonos que se le haya hecho, por ejemplo:

FACT1 PAGO1
FACT1 PAGO2
FACT1 PAGO3
FACT2 PAGO1
FACT2 PAGO2

Entonces, mi usuario sumariza todas las columnas en excel y obviamente la columna de facturas no muestra información correcta, por que hay valores duplicados. Entonces quiero que para cada factura, solo muestre el monto en la primer línea y en el resto cero o nada.

[Libras]

Para empezar no seria datos repetidos, sino que son el resultado del query que estas haciendo, si tienes una tabla de pagos por factura, al hacer el join con facturas te va a generar un producto cartesiano(un renglon por cada pago de cada factura) con los datos que das de tu query no podria ayudarte, necesitaria datos de ejemplo de cada una de las tablas implicadas

[gojusaul]

Iniciado por Libras

Para empezar no seria datos repetidos, sino que son el resultado del query que estas haciendo, si tienes una tabla de pagos por factura, al hacer el join con facturas te va a generar un producto cartesiano(un renglon por cada pago de cada factura) con los datos que das de tu query no podria ayudarte, necesitaria datos de ejemplo de cada una de las tablas implicadas

Entiendo... no son REGISTROS duplicados, pero para funciones de lo que necesito que los usuarios hagan cuando la consulta se exporta a Exel, el monto de la factura se repite en cada línea, entonces obtienen un total de facturas cobradas que no es real.

[gojusaul]

Si mi lógica no me falla, el valor que quiero que tome el campo INVOICE AMOUNT sería:

SI [INVOICE FOLIO] = [INVOICE FOLIO DE LA LINEA ANTERIOR]

ENTONCES [INVOICE AMOUNT] = 0

SI NO ENTONCES [INVOICE AMOUNT] = [INVOICE AMOUNT]

[Libras]

Se puede hacer eso pero seria pasar el resultado por un ciclo para revisar renglon por renglon cuales son los valores, ahora creo que se podria manejar de otra manera

tienes estos valores:

FACT1 PAGO1
FACT1 PAGO2
FACT1 PAGO3
FACT2 PAGO1
FACT2 PAGO2

Para factura 1 y factura 2 cual es el valor que quisieras? Pago1 y Pago 1 respectivamente verdad? y este pago es el que tiene la fecha mas "vieja" o como saber cual es el pago que te interesaria???

[gojusaul]

Iniciado por Libras

Se puede hacer eso pero seria pasar el resultado por un ciclo para revisar renglon por renglon cuales son los valores, ahora creo que se podria manejar de otra manera

tienes estos valores:

FACT1 PAGO1
FACT1 PAGO2
FACT1 PAGO3
FACT2 PAGO1
FACT2 PAGO2

Para factura 1 y factura 2 cual es el valor que quisieras? Pago1 y Pago 1 respectivamente verdad? y este pago es el que tiene la fecha mas "vieja" o como saber cual es el pago que te interesaria???

Te paso un resultado de la consulta como lo arroja ahora mismo:

Código HTML:

BOE & ASSOCIATES	Bluestem Brands, Inc	FE	9599	2015-11-19 00:00:00.000	4365	PODTNV00435539	ALL	140	-87.3	2015-12-04 00:00:00.000	30012438	0.05	-4.365
BOE & ASSOCIATES	Bluestem Brands, Inc	FE	9599	2015-11-19 00:00:00.000	4365	PODTNV00435539	PAY	321	4277.7	2015-12-04 00:00:00.000	30012438	0.05	213.885

En este caso la factura tiene dos movimientos, uno de tipo PAY y otro de tipo ALL.
El folio de la factura es 9599
El monto de la factura es 4365

Entonces lo que necesito es que solo en la primer linea de movimientos de ese folio de factura aparezca su monto y en las demás aparezca cero o nada.

Gracias por tu apoyo.

[Libras]

A lo que veo tu consulta la puedes resolver si en tu select solamente usas estos campos:

OE & ASSOCIATES Bluestem Brands, Inc FE 9599 2015-11-19 00:00:00.000 4365 PODTNV00435539 2015-12-04 00:00:00.000 30012438 0.05
BOE & ASSOCIATES Bluestem Brands, Inc FE 9599 2015-11-19 00:00:00.000 4365 PODTNV00435539 2015-12-04 00:00:00.000 30012438 0.05

y el select lo haces con un disctinct.

con el puro resultado no te puedo decir donde tienes el error porque no se exactamente en que tabla estas haciendo la duplicidad de registros, si no tengo un ejemplo de tus datos es dificil el poder ayudarte.....

Ya si quieres puedes usar una estructura while o un cursor(estas usando sql server??) y recorrer registro por registro tu resultado y poner los 0's que necesitas

[gojusaul]

Iniciado por Libras

A lo que veo tu consulta la puedes resolver si en tu select solamente usas estos campos:

OE & ASSOCIATES Bluestem Brands, Inc FE 9599 2015-11-19 00:00:00.000 4365 PODTNV00435539 2015-12-04 00:00:00.000 30012438 0.05
BOE & ASSOCIATES Bluestem Brands, Inc FE 9599 2015-11-19 00:00:00.000 4365 PODTNV00435539 2015-12-04 00:00:00.000 30012438 0.05

y el select lo haces con un disctinct.

con el puro resultado no te puedo decir donde tienes el error porque no se exactamente en que tabla estas haciendo la duplicidad de registros, si no tengo un ejemplo de tus datos es dificil el poder ayudarte.....

Ya si quieres puedes usar una estructura while o un cursor(estas usando sql server??) y recorrer registro por registro tu resultado y poner los 0's que necesitas

Si... estoy usando SQL Server.

La opción de usar menos campos para usar el DISTINCT no me funciona... toda la información que se muestra es la que necesito en mi reporte, sino que lo que requiero es que aunque una factura tenga N movimientos, el campo INVOICE AMOUNT solo muestra el valor en el primer registro que obtenga, y en el resto nada o cero.

Serías tan amable de apoyarme por Skype para darte más detalle? estoy como [email protected]

Gracias por tu apoyo.

[Libras]

Código SQL:

CREATE TABLE #temp
(
id INT,
fecha datetime
)
 
INSERT INTO #temp VALUES (900681,'01/04/2013')
INSERT INTO #temp VALUES (900681,'01/05/2013')
INSERT INTO #temp VALUES (900681,'01/06/2013')
INSERT INTO #temp VALUES (900525,'04/03/2010')
INSERT INTO #temp VALUES (900525,'01/02/2010')
INSERT INTO #temp VALUES (900526,'01/03/2010')
INSERT INTO #temp VALUES (900526,'01/04/2010')
INSERT INTO #temp VALUES (900526,'01/05/2010')
INSERT INTO #temp VALUES (900526,'01/06/2010')
 
 
SELECT t1.rn, CASE WHEN t1.id=t2.id THEN 0 ELSE t1.id END AS test, t1.fecha FROM(
SELECT ROW_NUMBER() OVER(partition BY id ORDER BY fecha) AS rn,* FROM #temp
) AS t1 LEFT JOIN
(SELECT ROW_NUMBER() OVER(partition BY id ORDER BY fecha) AS rn,* FROM #temp) AS t2 ON (t1.id=t2.id AND (t1.rn)=t2.rn+1)
 

revisa este query, regresa un resultado parecido al que buscas :)


rn test fecha
1 900525 2010-01-02 00:00:00.000
2 0 2010-04-03 00:00:00.000
1 900526 2010-01-03 00:00:00.000
2 0 2010-01-04 00:00:00.000
3 0 2010-01-05 00:00:00.000
4 0 2010-01-06 00:00:00.000
1 900681 2013-01-04 00:00:00.000
2 0 2013-01-05 00:00:00.000
3 0 2013-01-06 00:00:00.000

Si te fijas el campo id esta repetido, y lo que hace el query es poner en los repetidos el valor de 0 y solo dejar 1(el de la fecha menor)