on 11-24-2010 9:47 PM
Actualmente estoy comenzando a desarrollar un query para reflejar las facturas por proveedores con sus pagos, retenciones de iva, retenciones de islr, adelantos, nota de debito y nota de credito. Pero hasta ahora arme un query que me trae las facturas con sus pagos por proveedor pero no me lo muestra como quiero, agrupado por factura, es decir que por cada factura del proveedor me muestre en la linea siguente los pagos, las retenciones, las nota de credito, las nota de debito y los adelantos de la factura y asi sucesivamente por ahora tengo 2 query que mostrare para factura de compra con sus pagos
SELECT 'Compra' AS Documento, T0.[DocNum], T0.[TaxDate], T0.[DocDueDate], T0.[Comments], T0.[JrnlMemo], T0.[DocTotal], T0.[DocStatus], T3.[DocNum], T3.[DocType], T3.[TaxDate], T3.[DocDueDate], T3.[DocDate], T3.[Comments], T3.[JrnlMemo], T2.[SumApplied], T2.[BfDcntSum], T2.[BfNetDcnt] FROM OPCH T0 INNER JOIN VPM2 T2 ON T0.DocEntry = T2.DocEntry INNER JOIN OVPM T3 ON T2.DocNum = T3.DocEntry WHERE T0.[DocType]='S' and T0.[CardName] =[%0]
el otro es con union
SELECT 'Compra' AS Documento, T0.[DocNum] as 'N° Documento', T0.[TaxDate] as 'Fecha de Creacion',
T0.[DocDueDate] as 'Fecha de Vencimiento', T0.[DocDate] as 'Contabilizacion', T0.[Comments], T0.[JrnlMemo],
T0.[DocTotal] as 'Importe', T0.[DocStatus]as 'Estatus' FROM OPCH T0
WHERE T0.DocType='s' and T0.[CardName] ='ECO CHEMICAL 2000, C. A.'
UNION
select 'Pago Efectuado' as Documento, T1.[DocNum] as 'N° Documento', T1.[TaxDate]as 'Fecha de Creacion',
T1.[DocDueDate] as 'Fecha de Vencimiento', T1.[DocDate] as 'Contabilizacion', T1.[Comments], T1.[JrnlMemo],
T0.[SumApplied] as 'Importe', T1.Status as 'Estatus' FROM VPM2 T0 INNER JOIN OVPM T1 ON T0.DocNum = T1.DocEntry inner join OPCH T3 ON T3.DocEntry = T0.DocEntry
WHERE T1.DocType='s' and T1.[CardName] ='ECO CHEMICAL 2000, C. A.'
group by T0.DocEntry
pero no he podido traerme los pagos referente a cada factura del proveedor seleccionado, es decir todo agrupado por cada factura
Prueba esta query:
DECLARE @FechaInicio AS DateTime, @FechaFinal AS DateTime
SET @FechaInicio = '2014-01-01 00:00:00.000'
SET @FechaFinal = '2014-12-31 00:00:00.000'
--FACRTURAS DE COMPRAS
Select a.cardcode AS 'Codigo',a.cardname AS 'Nombre',Year(a.DocDate) AS 'Ano',datepart(month,a.DocDate) AS 'mes',
SUM(a.doctotal-a.vatsum+a.wtsum) as 'COMPRAS', 0 as 'PAGOS'
INTO #TEMP1
From OPCH a
WHERE a.CANCELED = 'N' and a.DocDate BETWEEN @FechaInicio AND @FechaFinal
group by a.CardCode,a.cardname,a.docdate
--NOTAS DE CREDITO A LAS COMPRAS
Select a.cardcode AS 'Codigo',a.cardname AS 'Nombre',Year(a.DocDate) AS 'Ano',datepart(month,a.DocDate) AS 'mes',
-SUM(a.doctotal-a.vatsum+a.wtsum) as 'COMPRAS' , 0 as 'PAGOS'
INTO #TEMP2
From ORPC a
WHERE a.CANCELED = 'N' and a.DocDate BETWEEN @FechaInicio AND @FechaFinal
group by a.CardCode,a.CardName,a.docdate
--PAGOS POR CUALQUIER ORIGEN
SELECT DISTINCT T0.CardCode AS 'Codigo', T0.CardName AS 'Nombre', Year(T0.DocDueDate) AS 'Ano',datepart(month,T0.DocDueDate) AS 'mes',
0 as 'COMPRAS', SUM(T0.DocTotal) as 'PAGOS'
INTO #TEMP3
FROM
OVPM T0
WHERE T0.DocDueDate BETWEEN @FechaInicio AND @FechaFinal AND T0.Canceled='N' AND T0.DocType='S'
GROUP BY T0.CardCode, T0.CardName, T0.DocDueDate
--UNION DE LAS TRES TABLAS
Select
T.Codigo as 'CODIGO',
T.Nombre as 'NOMBRE',
T.Ano 'ANO',
'C'+ cast(t.mes as varchar(2)) 'CMES',
'P'+ cast(t.mes as varchar(2)) 'PMES',
sum(t.COMPRAS) AS 'COMPRAS',
sum(t.PAGOS) AS 'PAGOS'
INTO #TEMP4
from
(
Select * from #TEMP1
union
Select * from #TEMP2
union
Select * from #TEMP3
) as T
inner join OCRD T0
on T0.CardCode = T.Codigo and T0.CardType = 'S'
GROUP BY
T.Codigo, T.Nombre ,T.Ano,T.MES
--DOBLE PIVOT A LAS TABLAS
Select *
INTO #TEMP5
from #TEMP4
pivot ( sum(COMPRAS)
for CMES in ( C1, C2, C3, C4, C5, C6, C7, C8, C9, C10,C11, C12 )
) AS Pivot1
pivot ( sum(PAGOS)
for PMES in ( P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12 )
) AS Pivot2
--SELECCION DE COMPRAS Y PAGOS MES A MES EN COLUMNAS ADYACENTES
Select t.CODIGO, t.NOMBRE, t.ANO,
t.C1, t.P1, t.C2, t.P2, t.C3, t.P3, t.C4, t.P4, t.C5, t.P5, t.C6, t.P6,
t.C7, t.P7, t.C8, t.P8, t.C9, t.P9, t.C10,t.P10, t.C11,t.P11, t.C12,t.P12
from #TEMP5 as t
--ELIMINACION DE TODAS LAS TABLAS TEMPORALES
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
DROP TABLE #TEMP4
DROP TABLE #TEMP5
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
92 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.