on 08-08-2012 12:40 AM
Hola a todos, alguien tiene algun query para pagos efectuados que tenga el estilo siguiente,
Proveedor | Lunes 06/08/2012 | Martes 07/08/2012 | Miercoles 08/08/2012 | Jueves 09/08/2012 | Viernes 10/08/2012 | Sabado 11/08/12 |
---|---|---|---|---|---|---|
Proveedor 1 | 4000 | |||||
Proveedor 2 | 500 | 600 | 800 | |||
Proveedor 3 | 250 | 1000 | ||||
Proveedor 4 | 750 | 2500 |
Alguna idea?
Saludos.
Alessandro.
Modifique el query debido a que se me olvido agregar unos filtros básicos en los pagos.
Aquí va.
SET LANGUAGE Spanish
DECLARE @TOP INT, @F_INI DATETIME, @F_FIN DATETIME, @COND_1 CHAR(1), @COND_2 CHAR(1)
SET @TOP=(SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate>='[%0]' AND A.RefDate<='[%1]')
SET @F_INI='[%0]'
SET @F_FIN='[%1]'
SET @COND_1='N'
SET @COND_2='S'
SELECT DISTINCT B.DocDate AS DocDate, CONVERT(NVARCHAR, B.DocDate, 103) AS Fecha
INTO #FECHAS
FROM OVPM B
WHERE B.DocDate BETWEEN @F_INI AND @F_FIN
ORDER BY 1
DECLARE @pvt_table NVARCHAR(MAX)
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + C.Fecha + ']', '[' + C.Fecha + ']')
FROM #FECHAS C
ORDER BY C.DocDate
DECLARE @Pvt NVARCHAR(MAX)
SET @Pvt =
N'
SELECT *
FROM (
SELECT DISTINCT T0.CardCode AS CardCode, T0.CardName AS CardName, CONVERT(NVARCHAR, T0.DocDate, 103) AS Fecha, SUM(T0.DocTotal) AS Pagos
FROM OVPM T0
WHERE T0.DocDate BETWEEN (@INI) AND (@FIN) AND T0.Canceled=(@C_1) AND T0.DocType=(@C_2)
GROUP BY T0.CardCode, T0.CardName, T0.DocDate
) AS A
PIVOT (
SUM(Pagos)
FOR Fecha IN ('+ @pvt_table +')
) AS Pvt
ORDER BY 1,3
'
EXEC sp_executesql @pvt, N'@pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME, @C_1 CHAR(1), @C_2 CHAR(1)',@pvt_table, @F_INI, @F_FIN, @COND_1, @COND_2
DROP TABLE #FECHAS
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Buenos dias Alessandro, en base a tus comentarios modifique el query para que apuntara a la tabla de facturas de proveedores, con los docuemtos abiertos y en base a la fecha de vencimiento , pero me arroja la fecha de contabilizacion? que estara mal del query? tambien es posible que no aparezcan las fechas cuando no haya ningun dato? en este caso algun pago pendiente
gracias por el apoyo
SET LANGUAGE Spanish
DECLARE @TOP INT, @F_INI DATETIME, @F_FIN DATETIME, @COND_1 CHAR(1), @COND_2 CHAR(1)
SET @TOP=(SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate>='[%0]' AND A.RefDate<='[%1]')
SET @F_INI='[%0]'
SET @F_FIN='[%1]'
SET @COND_1='N'
SET @COND_2='S'
SELECT DISTINCT B.DocDueDate AS DocDueDate, CONVERT(NVARCHAR, B.DocDueDate, 103) AS Fecha
INTO #FECHAS
FROM OPCH B
WHERE B.DocDueDate BETWEEN @F_INI AND @F_FIN AND DocStatus = 'O'
ORDER BY 1
DECLARE @pvt_table NVARCHAR(MAX)
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + C.Fecha + ']', '[' + C.Fecha + ']')
FROM #FECHAS C
ORDER BY C.DocDueDate
DECLARE @Pvt NVARCHAR(MAX)
SET @Pvt =
N'
SELECT *
FROM (
SELECT DISTINCT T0.CardCode AS CardCode, T0.CardName AS CardName, CONVERT(NVARCHAR, T0.DocDueDate, 103) AS Fecha, SUM(T0.DocTotal) AS Pagos
FROM OVPM T0
WHERE T0.DocDueDate BETWEEN (@INI) AND (@FIN) AND T0.Canceled=(@C_1) AND T0.DocType=(@C_2)
GROUP BY T0.CardCode, T0.CardName, T0.DocDueDate
) AS A
PIVOT (
SUM(Pagos)
FOR Fecha IN ('+ @pvt_table +')
) AS Pvt
ORDER BY 1,3
'
EXEC sp_executesql @pvt, N'@pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME, @C_1 CHAR(1), @C_2 CHAR(1)',@pvt_table, @F_INI, @F_FIN, @COND_1, @COND_2
DROP TABLE #FECHAS
Para eso necesitas un PIVOT
-- Modificado
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
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.