Crear una consulta en costos - gastos - ingresos

jtriana6591 · 4 Mayo, 2020 14:31

buenos días, por favor alguien que me ayude con esta consulta me solicitaron realizar un informe de costos- gasto e ingresos en SAP por proyectos y por mes es decir que por mes me muestre los proyectos el costo e gasto y el ingreso

gracias por su ayuda

tengo esta consulta pero me trae es el total por proyecto y no por mes

SELECT        PrjCode AS proyecto, PrjName, ISNULL
                             ((SELECT        SUM(tt0.Debit) - SUM(tt0.Credit) AS Expr1
                                 FROM            dbo.JDT1 AS tt0 LEFT OUTER JOIN
                                                          dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
                                 WHERE        (tt0.Project = t11.PrjCode) AND (tt0.Account LIKE '5%')
                                 GROUP BY tt0.Project), 0) AS Gasto, ISNULL
                             ((SELECT        SUM(tt0.Debit) - SUM(tt0.Credit) AS Expr1
                                 FROM            dbo.JDT1 AS tt0 LEFT OUTER JOIN
                                                          dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
                                 WHERE        (tt0.Project = t11.PrjCode) AND (tt0.Account LIKE '7%')
                                 GROUP BY tt0.Project), 0) + ISNULL
                             ((SELECT        SUM(tt0.Debit) - SUM(tt0.Credit) AS Expr1
                                 FROM            dbo.JDT1 AS tt0 LEFT OUTER JOIN
                                                          dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
                                 WHERE        (tt0.Project = t11.PrjCode) AND (tt0.Account LIKE '6%')
                                 GROUP BY tt0.Project), 0) AS Costo,
                             (SELECT        (SUM(tt0.Debit) - SUM(tt0.Credit)) * - 1 AS Expr1
                               FROM            dbo.JDT1 AS tt0 LEFT OUTER JOIN
                                                         dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
                               WHERE        (tt0.Project = t11.PrjCode) AND (tt0.Account LIKE '4%')
                               GROUP BY tt0.Project) AS Ingreso
FROM            dbo.OPRJ AS t11
GROUP BY PrjCode, PrjName

Willy_Caldero · 5 Mayo, 2020 15:55

Echale una mirada a este post: Consultas usando la función PIVOT del SQL
con eso puedes desglosar por mes, tus datos.

jtriana6591 · 6 Mayo, 2020 20:05

hola a todos he intentado realizar la consulta por separado pero he podedio encontrar la forma de separlos por mes y por año los valores por ejemple en gastos en el total me sale es el total repetido no por mes… alguien que sabe como podría hacerlo.


SELECT  T4.Project,T5.RefDate, ((select isnull(SUM(tt0.Debit) - SUM(tt0.Credit),0) 
from dbo.JDT1 AS tt0 LEFT OUTER JOIN dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
where tt1.RefDate between  '2019-01-01' and '2019-03-30' and tt0.Account LIKE '7%' and tt0.Project = 'ITSR-2018-133')
+
(select isnull (SUM(tt0.Debit) - SUM(tt0.Credit),0)  from dbo.JDT1 AS tt0 LEFT OUTER JOIN dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
where tt1.RefDate between '2019-01-01' and '2019-03-30' and tt0.Account LIKE '6%' and tt0.Project = 'ITSR-2018-133')) As totalGasto
from dbo.JDT1 AS T4 LEFT OUTER JOIN dbo.OJDT AS T5 ON T4.TransId = T5.TransId
group by T4.Project,T5.RefDate

Willy_Caldero · 6 Mayo, 2020 20:20

En este tema explico como usar el PIVOT, revisalo, con eso puede tomar el mes de la fecha, y separarlo en columnas.

jtriana6591 · 6 Mayo, 2020 21:38

hola Willy_Caldero gracias por responder no soy muy bueno realizando consultas y estoy tomando información de las tablas OJTD y JDT1

rubenantonio · 7 Mayo, 2020 18:48

Saludos cordiales Jtrian6591, creo que tengo un query que hice el cual te da todos las cuentas del balance de comprobación Resumida por mes y año, la exportas a excel y ahí manejas todo como quieras, le puedes agregar el proyecto, a la cuenta contable o quitas la cuenta y dejas el proyecto, puedes filtrar por las cuentas que msnejan los proyectos, dejame ver como te lo envío, ya que no estoy en la compañía por lo de la pandemia,
…///ve si te sirve, en el substr de la cuenta puedes empezar por el código de cuentas donde se encuentra el proyecto. El <>-3 indica qye no te liste el asiento de cierre anual, si lo quitas te va a dar monto cero, si es una cuenta de gasto. Account, última instrucción 1,1=‘6’ es que liste todas las cuentas que empiecen por 6…, comoyte dije este reporte este por el código de cuentas, no contempla el proyecto, entonces, prueba agregándoselo, me avisas por si o por no

Select consulta.*, convert(varchar(4),ano)+'-'+convert(varchar(4),mes)  +'-'+convert(varchar(2),dia)  as fecha, 'TuCompañia' as Compañia  from 
(select SUBSTRING(jdt1.account, 1, 1) as Nivel_1, SUBSTRING(jdt1.account, 1,1)+SUBSTRING(jdt1.account, 3,1) as Nivel_2, SUBSTRING(jdt1.account, 1,7) as Nivel_3,  SUBSTRING(jdt1.account, 1,10) as Nivel_4, Account, oact.acctname, 
year(OJDT.Refdate) as ano, month(OJDT.Refdate) as mes, '01' as dia,
sum(debit) as debe, sum(credit) as haber, sum(debit)-sum(credit) as saldo  from jdt1
INNER JOIN OACT ON JDT1.account= OACT.acctcode
LEFT JOIN OJDT ON OJDT.TransId=JDT1.TransId
where jdt1.transtype <>'-3' and SUBSTRING(jdt1.account, 1,1) = '6' 
group by Account, acctname, year(OJDT.Refdate),month(OJDT.Refdate)
) consulta

jtriana6591 · 8 Mayo, 2020 20:57

hola rubenantonio gracias por tu ayuda ya realiza el uso de tu consulta pero ahora debo realizar la sum(debito)- sum(credito) de la cuenta 7 - sum(debito)- sum(credito) de la cuenta 6 detallado por mes

rubenantonio · 8 Mayo, 2020 22:22

Saludos hermano, no sé como tienes parámetrizado tu maestro de cuentas, en el query solo te llama a la cuenta 6 qué para mi son gastos , si le colocas > ‘6’ debe de darte el total de la suma de las cuentas 6 y 7, asumo que esa cuenta 7 son otros ingresos, si es así esa cta debería tener saldo negativo si colocas > 6 te dará el neto de ambas cuentas o colocas =‘7’, como te dije ahí no esta incluido el proyecto, y puedes agregarle o donde esta la cuenta la quitas y colocas el proyecto, si solo quieres totalizar los montos por proyecto. No tengo interne en la cía ahora para buscar ese query, Yo hice ese reporte por proyecto /cuenta tambien, pero con ese puedes modificarlo y adaptarlo a lo que te piden. Avisame poe si o por no, saludos.

pibemad · 12 Mayo, 2020 11:07

Hola, ¿porqué no usas los reportes estándares de PS?

S_ALR_87013545 - Comparación períodos real , por ejemplo

YesidG · 12 Mayo, 2020 13:30

Hola intenta con esta consulta

SELECT
  Project ,MES ,AÑO ,SUM(totalGasto) OVER (PARTITION BY MES ) AS totalGasto
FROM
(
SELECT 
 T4.Project,MONTH(T5.RefDate) AS MES, YEAR(T5.RefDate) AS AÑO, T5.RefDate,
 ((select isnull(SUM(tt0.Debit) - SUM(tt0.Credit),0) 
from dbo.JDT1 AS tt0
 LEFT OUTER JOIN dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
where tt1.RefDate between  '2019-01-01' and '2019-03-30' 
and tt0.Account LIKE '7%' and tt0.Project = 'ITSR-2018-133')
+
(select isnull (SUM(tt0.Debit) - SUM(tt0.Credit),0)  from dbo.JDT1 AS tt0 
LEFT OUTER JOIN dbo.OJDT AS tt1 ON tt0.TransId = tt1.TransId
where tt1.RefDate between '2019-01-01' and '2019-03-30' 
and tt0.Account LIKE '6%' and tt0.Project = 'ITSR-2018-133')) As totalGasto
from dbo.JDT1 AS T4 LEFT OUTER JOIN dbo.OJDT AS T5 ON T4.TransId = T5.TransId
group by T4.Project,T5.RefDate ) AS T1

YesidG · 12 Mayo, 2020 13:36

Hola, este es un ejemplo de consulta con pivot dinámico

/*------------Consumos General - UP 20-02-2020---------*/
----COLMUNAS DE CONSUMOS
DECLARE @C VARCHAR(MAX)
DECLARE @Q VARCHAR(MAX)
DECLARE @F_HOY DATETIME
DECLARE @M1 DATETIME 
       ,@M2 DATETIME 
	   ,@M3 DATETIME 
	   ,@M4 DATETIME
DECLARE @MES1 VARCHAR(MAX) 
       ,@MES2 VARCHAR(MAX)
	   ,@MES3 VARCHAR(MAX) 
	   ,@MES4 VARCHAR(MAX)

SET @F_HOY = GETDATE()
SET @M1 = @F_HOY 
SET @M2 = DATEADD(MONTH,-1,@F_HOY)
SET @M3 = DATEADD(MONTH,-2,@F_HOY)
SET @M4 = DATEADD(MONTH,-3,@F_HOY)

SET @MES1 = DATENAME(YEAR, @M1)+'-'+CONVERT(CHAR(2), MONTH(@M1), 105)
SET @MES2 = DATENAME(YEAR, @M2)+'-'+CONVERT(CHAR(2), MONTH(@M2), 105)
SET @MES3 = DATENAME(YEAR, @M3)+'-'+CONVERT(CHAR(2), MONTH(@M3), 105)
SET @MES4 = DATENAME(YEAR, @M4)+'-'+CONVERT(CHAR(2), MONTH(@M4), 105)

SET @C = ''
SELECT @C = @C  +'['+@MES4+'],['+@MES3+'],['+@MES2+'],['+@MES1+'],' 

SET @C = LEFT(@C,LEN(@C)-1)
----------FIN COLMUNAS DE CONSUMOS	
SET @Q = '
  SELECT T3.ItemCode AS CODIGO, T3.ItemName AS DESCRIPCION, '+@C+'

  FROM (SELECT T0.ItemCode AS CODIGO, T0.ItemName AS DESCRIPCION, SUM(CANTIDAD) AS CANTIDAD, MES_AÑO
  FROM 
  OITM AS T0 JOIN 
  ( 
	/*-----Factura de deudores-----*/
	SELECT 
	T0.ItemCode, T1.Quantity AS CANTIDAD, 
	DATENAME(YEAR, T2.DocDate)+''-''+CONVERT(CHAR(2), MONTH(T2.DocDate), 105) AS MES_AÑO,
	T2.DocDate AS FECHA, YEAR(T2.DOCDATE) AS AÑO, MONTH(T2.DOCDATE) AS MES, T3.SeriesName, 
	T0.ItmsGrpCod, T1.WhsCode
	FROM OITM AS T0 
	JOIN INV1 AS T1 ON T0.ItemCode = T1.ItemCode
	JOIN OINV AS T2 ON T1.DocEntry = T2.DocEntry
	JOIN NNM1 AS T3 ON T2.Series = T3.Series
	WHERE T2.DocType = ''I'' 
	UNION ALL
	/*------Nota Credito------*/
	SELECT 
	T0.ItemCode, -T1.Quantity AS CANTIDAD, 
	DATENAME(YEAR, T2.DocDate)+''-''+CONVERT(CHAR(2), MONTH(T2.DocDate), 105) AS MES_AÑO,
	T2.DocDate AS FECHA, YEAR(T2.DOCDATE) AS AÑO, MONTH(T2.DOCDATE) AS MES, T3.SeriesName, 
	T0.ItmsGrpCod, T1.WhsCode 
	FROM OITM AS T0 
	JOIN RIN1 AS T1 ON T0.ItemCode = T1.ItemCode
	JOIN ORIN AS T2 ON T1.DocEntry = T2.DocEntry
	JOIN NNM1 AS T3 ON T2.Series = T3.Series
	WHERE T2.DocType = ''I''
	      AND T2.CANCELED = ''N''
   )AS T1  ON T0.ItemCode = T1.ItemCode

   WHERE T1.FECHA BETWEEN DATEADD(MONTH,-5,GETDATE()) AND GETDATE()
   GROUP BY T0.ItemCode, T0.ItemName, MES_AÑO, AÑO, MES
   
   ) AS T1
PIVOT 
(
  SUM(CANTIDAD)
  FOR MES_AÑO IN ('+@C+')
) AS T2 FULL JOIN OITM AS T3 ON T2.CODIGO = T3.ItemCode

  WHERE T3.validFor = ''Y''
  ORDER BY T3.ItemName'
EXEC (@Q)

jtriana6591 · 15 Mayo, 2020 21:55

gracias por tu ayuda