listado facturas y nota de credito reporte

[Former Member]

Hola:

estoy haciendo un reporte de facturas, pero necesito especificar si estan canceladas o no mediante una nota de credito, si alquien puede ayudarme con el query se lo agradecerìa, tengo este que me saca las facturas desde un intervalo de fechas y por sucursales pero me faltaria especificar cuales estan canceladas, como se haria esto? gracias

Select inv.FolioNum as , inv.DocNum as , CONVERT(VARCHAR(10), inv.DocDate, 103) as , inv.CardCode as , inv.CardName as , CONVERT(decimal(18, 2), inv.GrosProfit) as Subtotal, CONVERT(decimal(18, 2), inv.VatSum) as Iva, CONVERT(decimal(18, 2), inv.DocTotal) as Total, inv.Canceled as Status, ser.GroupCode, ser.SeriesName as Sucursal, os.SlpName as Vendedor from OINV inv inner join OSLP os on os.SlpCode = inv.SlpCode inner join NNM1 ser on inv.Series = ser.Series where DocDate >= '20080202' and DocDate <= '20090126' and ser.GroupCode IN (2,3)

[Former Member · Accepted solution]

Intente este:

Select Distinct inv.FolioNum as 'Folio Fiscal', inv.DocNum as 'No. Factura', CONVERT(VARCHAR(10), inv.DocDate, 103) as Fecha, inv.CardCode as 'No.Cliente', inv.CardName as Cliente, CONVERT(decimal(18, 2), inv.GrosProfit) as Subtotal, CONVERT(decimal(18, 2), inv.VatSum) as Iva, CONVERT(decimal(18, 2), inv.DocTotal) as Total, Case WHEN inv.Canceled = 'Y' Then 'Cancelado' WHEN t0.TargetType = 14 THEN 'Invertido' ELSE 'Normal' END as Status, ser.GroupCode, ser.SeriesName as Sucursal, os.SlpName as Vendedor

from dbo.OINV inv

inner join dbo.INV1 t0 on t0.DocEntry = inv.DocEntry

inner join dbo.OSLP os on os.SlpCode = inv.SlpCode

inner join dbo.NNM1 ser on inv.Series = ser.Series

where inv.DocDate >= '[%0\]' and inv.DocDate <= '[%1\]' and ser.GroupCode IN (2,3)

Gracias,

Gordon

Edited by: Paulo Calado on Jun 11, 2009 2:07 PM

[angeles804]

-- Reporte de Facturas

Select 'Status de Documento' =

CASE WHEN T0.docstatus='C' THEN 'Cerrado'

ELSE 'Abierto'

END,

Cancelada =

case when ((t0.InvntSttus = 'C') and (t0.isins<>'Y')) then 'Cancelado'

when (t0.doctotal>t0.paidtodate) and (t0.groupnum=8) then ' FONACOT'

when (t0.isins='y') and (t0.doctotal>t0.paidtodate) then 'Fact de Reserva no pagada'

When (T0.doctotal=T0.[paidtodate]) and (t0.groupnum=2) THEN 'pagado a contado'

when (t0.isins='y') and (t0.doctotal=t0.paidtodate) then 'Fact de reserva pagada'

when (t0.doctotal>t0.paidtodate) and (t0.groupnum=-1) then ' COD sin pagar'

when (t0.doctotal>t0.paidtodate) and (t0.groupnum<>2) and (t0.groupnum<>-1) and (t0.groupnum<>9) and (t0.groupnum<>8) and ((t0.groupnum>-2) and (t0.groupnum<25))

THEN 'credito'

when (t0.doctotal=t0.paidtodate) and (t0.groupnum=9) then 'tarjeta'

end,

T0.DocNum,T0.FolioPref, T0.FolioNum, T0.DocDate, T0.CardCode, T0.CardName, T0.DocTotal, T0.PaidToDate

FROM OINV T0

INNER JOIN NNM1 T1 ON T0.series=T1.series

INNER JOIN OCTG T2 ON T2.GroupNum= T0.GroupNum

WHERE T0.doctype = 'I' and T1.seriesname='[%0]' and T0.DocDate >= '[%1]' and T0.DocDate <= '[%2]'

[Former Member]

me marca este error

Msg 4145, Level 15, State 1, Line 12

Se especificó una expresión no booleana en un contexto donde se esperaba una condición, cerca de 'and'.

es esta linea :

when (t0.doctotal>t0.paidtodate) and (t0.groupnum2) and (t0.groupnum-1) and (t0.groupnum9) and (t0.groupnum8) and ((t0.groupnum>-2) and (t0.groupnum<25))

Edited by: Rui Pereira on Apr 15, 2009 10:38 AM