Cuenta de Perdidas y Ganancias Query

[Former Member]

Estoy tratando de hacer un query de Cuentas de Perdidas y Ganancias basado en las cuentas seleccionadas en el Modelo de Informe Financiero, en el que figure :

Codigo Cuentas, Nombre Cuenta, Debe, Haber, Saldo

Agradezco de antemano su ayuda.

[former_member227744]

Giovanni:

Prueba con esta query:

---BALANCE 8 COLUMNAS

DECLARE @DESDE DATETIME

DECLARE @HASTA DATETIME

SET @DESDE = (SELECT ISNULL(MAX(T0.RefDate),'[%0]') FROM OJDT T0 WHERE T0.RefDate = '[%0]') SET @HASTA = (SELECT ISNULL(MAX(T0.RefDate),'[%1]') FROM OJDT T0

WHERE T0.RefDate = '[%1]')

Select

T1.AcctCode 'Cuenta'

,T1.AcctName 'Nombre'

,SUM(T0.debitos)'Debito'

,SUM(T0.creditos)'Credito'

,CASE

WHEN Sum(T0.Acreedor)-SUM(T0.Deudor) < 0 THEN

-Sum(T0.Acreedor)+SUM(T0.Deudor)

WHEN Sum(T0.Acreedor)-SUM(T0.Deudor) > 0 THEN 0 ELSE 0 END 'Deudor'

,CASE

WHEN Sum(T0.Acreedor)-SUM(T0.Deudor) > 0 THEN

+Sum(T0.Acreedor)-SUM(T0.Deudor)

WHEN Sum(T0.Acreedor)-SUM(T0.Deudor) < 0 THEN 0 ELSE 0 END 'Acreedor'

,CASE

WHEN T0.Tipo < '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) < 0 THEN -Sum(T0.Acreedor)+SUM(T0.Deudor) WHEN T0.Tipo < '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) > 0

THEN 0 ELSE 0 END 'Activo'

,CASE

WHEN T0.Tipo < '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) > 0 THEN +Sum(T0.Acreedor)-SUM(T0.Deudor) WHEN T0.Tipo < '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) < 0

THEN 0 ELSE 0 END 'Pasivo'

,CASE

WHEN T0.Tipo >= '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) < 0 THEN -Sum(T0.Acreedor)+SUM(T0.Deudor) WHEN T0.Tipo >= '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) > 0

THEN 0 ELSE 0 END 'Perdida'

,CASE

WHEN T0.Tipo >= '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) > 0 THEN +Sum(T0.Acreedor)-SUM(T0.Deudor) WHEN T0.Tipo >= '4' AND Sum(T0.Acreedor)-SUM(T0.Deudor) < 0

THEN 0 ELSE 0 END 'Ganancia'

FROM Balance8 T0 INNER JOIN OACT T1 ON

T0.Cuenta=T1.AcctCode

WHERE (T0.ano Between YEAR(@DESDE) AND YEAR(@HASTA) ) AND (T0.mes Between MONTH(@DESDE) AND MONTH(@HASTA)) AND T1.Levels>3 GROUP BY

T0.Cuenta,T1.AcctCode,T0.Nombre,T1.AcctName,T0.Tipo

Espero te sea de utilidad, saludos.

Manuel

[former_member227744]

Sorry, acá va, tienes que crear las vistas necesarias para que funcione la query anterior.

USE [BASE DE DATOS]

GO

/****** Object:  View [dbo].[Balance8]    Script Date: 03/19/2014 12:40:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create view [dbo].[Balance8] as SELECT  YEAR(RefDate) AS ano, MONTH(RefDate) AS mes, tipo, FormatCode AS cuenta, AcctName AS nombre, SUM(Debit) AS debitos, SUM(Credit) AS creditos, CASE WHEN SUM(Debit) - SUM(Credit) > 0 THEN SUM(Debit) - SUM(Credit) ELSE 0 END AS Deudor, CASE WHEN SUM(Debit) - SUM(Credit) <= 0 THEN (SUM(Debit) - SUM(Credit)) * - 1 ELSE 0 END AS acreedor FROM Comprobantes_BALANCE GROUP BY FormatCode, AcctName, YEAR(RefDate), MONTH(RefDate), tipo

Aca la de comprobante, por si te la pide:

USE [BASE DE DATOS]

GO

/****** Object:  View [dbo].[Comprobantes_BALANCE]    Script Date: 03/19/2014 12:44:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE View [dbo].[Comprobantes_BALANCE] AS SELECT  TOP 100 PERCENT * FROM

(SELECT O.GroupMask AS tipo, O.AcctCode AS Account, O.FormatCode, O.AcctName, J.TransId, J.RefDate,

CASE WHEN isnull(J.credit,0) < 0 THEN isnull(J.credit,0) * - 1 ELSE CASE WHEN isnull(J.debit,0) < 0 THEN 0 ELSE j.debit END END AS debit,

CASE WHEN isnull(J.Debit,0) < 0 THEN isnull(J.debit,0) * - 1 ELSE CASE WHEN isnull(J.credit,0)  < 0 THEN 0 ELSE j.credit END END AS credit,

J.LineMemo, J.Transtype FROM  dbo.JDT1 J INNER JOIN dbo.OACT O ON J.Account = O.AcctCode

Where  ((refdate >= {d'2009-01-01'} and refdate <= {d'2009-05-30'}) or ( refdate = {d'2009-05-31'} and TransType <> -3)))  AS A  order By tipo, formatcode

Espero sea de utilidad.

Saludos,

Manuel

[former_member212657]

Hola.

Selecciona estos campos de las tablas JDT1, OJDT y OACT.

Alli esta lo que necesitas.

Saludos