Query de cuentas contables vs presupuestos

MiguelHG · 28 Julio, 2021 16:19

Buen día a todos,
Soy nuevo en el foro y más nuevo en la creación de querys, por eso recurro a ustedes porque tengo problemas con uno,
Manejo Power BI en la empresa y me pidieron un dashboard para indicadores de finanzas, quieren información de las cuentas contables (OACT, OJDT, JDT1) vs su presupuesto (OBGT, BGT1) con el identificador del proyecto (OPRJ, OBGS), el problema está en que lo he armado, pero como no sé muy bien las sentencias para sql, los datos me los da en totales, no sé como poner la sentencia correcta, alguien me puede ayudar???, este es el query que tengo:

SELECT
‘BD_2019’ as ‘Base de datos’,
T0.[Acctcode], T0.[AcctName],
(T4.[Line_ID] + 1) as ‘Mes’,
(T4.[DebSTotal] + T4.[CredSTotal]) as ‘Presupuesto’,
(T2.[Credit] + T2.[Debit]) as ‘Monto’,
T7.[PrjName] as ‘Proyecto’
FROM [BD_2019].[dbo].[OACT] T0
INNER JOIN OBGT T3 ON T0.[AcctCode]=T3.[AcctCode]
INNER JOIN JDT1 T2 ON T0.[AcctCode]=T2.[Account]
INNER JOIN OJDT T1 ON T2.[TransId]=T1.[TransId]
LEFT JOIN OPRJ T7 ON T2.[Project]=T7.[PrjCode]
INNER JOIN BGT1 T4 ON T3.[AbsId]=T4.[BudgId]
INNER JOIN BGT2 T5 ON T3.[AbsId]=T5.[BudgId]
INNER JOIN BGT3 T6 ON T3.[AbsId]=T6.[BudgId]
LEFT JOIN OBGS T8 ON T4.[Instance]=T8.[BaseId]
WHERE T2.[RefDate] BETWEEN ‘20210101’ AND ‘20211231’ AND T3.[FinancYear] = ‘20210101’
GROUP BY T0.[Acctcode], T0.[AcctName], T4.[Line_ID], T7.[PrjName], T4.[DebSTotal], T4.[CredSTotal], T2.[Credit], T2.[Debit]
ORDER BY T0.[Acctcode]

SidV · 11 Agosto, 2021 23:28

Este tema estaba mal clasificado, capaz ahora alguien de B1 pueda verlo

Ares17000 · 11 Octubre, 2021 19:31

Espero no llegar muy tarde…

Yo tuve que realizar una consulta, pero tiene muchas personalizaciones para la empresa, la misma incluye no solo las cuentas que están en el presupuesto, sino las que también están fuera del presupuesto (porque no las incluyeron originalmente), y unas especiales…

Te paso parte de la consulta a ver si te sirve lo hago de un SP donde paso de parámetro la Instancia la fecha inicial y fecha final:

--Parámetros
@instancia as int = 4,
@fechaInicial as date = '01/01/2021',
@fechaFinal as date = '12/31/2021'

--Primero validamos las cuentas que tienen presupuesto
select 
AcctCode,
DebLTotal
into #tempcuentasconPresupuesto
from OBGT where Instance = @instancia

--Aquí tomamos las cuentas de Gastos que no están dentro de presupuesto
select 
acctcode,
Acctname,
OACT.FrgnName,
OACT.U_CTA_TIPO
into #tempcuentasgastossinpresupuesto
 from OACT where 
(FatherNum like '5%' or FatherNum like '6%' or FatherNum like '70%' or FatherNum like '71%') 
and FatherNum <> '500' and FatherNum <> '600000000000000' and FatherNum <> '500000000000000' and FatherNum <> '700000000000000'
and AcctCode not in (select Acctcode from #tempcuentasconPresupuesto)

--Sumamos mes a mes lo que se ha aplicado en las cuentas que no tienen presupuesto en asientos contables
Select 
Account,
sum(Debit - Credit) as 'Monto',
month(refdate) as mes

into #tempctgastos
From JDT1 
inner join OACT on JDT1.Account = OACT.AcctCode
where RefDate Between @fechaInicial and @fechaFinal
and Account in (select Acctcode from #tempcuentasgastossinpresupuesto)
group by Account, MONTH(refdate)

--Armamos de las cuentas que no tienen presupuesto el consumo mes a mes
Select
Distinct
t0.U_CTA_TIPO,
t0.FrgnName,
t0.AcctCode,
t0.AcctName,
0 as 'PRESUPUESTO ANUAL',
isnull((Select Sum(monto) From #tempctgastos where Account = T0.AcctCode),0) as 'Real Anual',
isnull((Select Sum(monto)*-1 From #tempctgastos where Account = T0.AcctCode),0) as 'Diferencia Anual',
100 as '% Avance del año',

0 as 'Presupuesto Enero',
isnull((Select Monto From #tempctgastos where Account = T0.AcctCode and Mes = 1),0) as 'Real Enero',
isnull((Select Monto*-1 From #tempctgastos where Account = T0.AcctCode and Mes = 1),0) as 'Diferencia Enero',
Case when isnull((Select Monto From #tempctgastos where Account = T0.AcctCode and Mes = 1),0) = 0 then 0
else 100 end as 'Avance % Enero',

0 as 'Presupuesto Febrero',
isnull((Select Monto From #tempctgastos where Account = T0.AcctCode and Mes = 2),0) as 'Real Febrero',
isnull((Select Monto*-1 From #tempctgastos where Account = T0.AcctCode and Mes = 2),0) as 'Diferencia Febrero',
Case when isnull((Select Monto From #tempctgastos where Account = T0.AcctCode and Mes = 2),0) = 0 then 0
else 100 end 'Avance % Febrero',
.
.
.
Into #avancesinPresupuesto
 from 
#tempcuentasgastossinpresupuesto t0 

---- CAMBIO PARA TOMAR LOS DATOS DE LA JDT1 YA QUE LA BGT1 DE PRESUPUESTO NO SE ESTÁ ACTUALIZANDO AL 100%
--primera parte
select 
OACT.acctcode,
OACT.Acctname,
OACT.FrgnName,
OACT.U_CTA_TIPO,
#tempcuentasconPresupuesto.DebLTotal AS 'Pres_anual'
into #tempDATOSCUENTASCONPRESUPUESTO
 from OACT inner join #tempcuentasconPresupuesto on OACT.AcctCode = #tempcuentasconPresupuesto.AcctCode
 


--Sumamos mes a mes lo que se ha aplicado en las cuentas que no tienen presupuesto en asientos contables
Select 
Account,
sum(Debit - Credit) as 'Monto',
month(refdate) as mes

into #tempctgastospresupuestado
From JDT1 
inner join OACT on JDT1.Account = OACT.AcctCode
where RefDate Between @fechaInicial and @fechaFinal
and Account in (select Acctcode from #tempDATOSCUENTASCONPRESUPUESTO)
group by Account, MONTH(refdate)

 

Select
Distinct
t0.U_CTA_TIPO as 'División Presupuesto',
t0.FrgnName as 'Área', 
t0.AcctCode as 'Cuenta',
t0.AcctName as 'Nombre Cuenta',
t0.Pres_anual as 'PRESUPUESTO ANUAL',
isnull((Select Sum(monto) From #tempctgastospresupuestado where Account = T0.AcctCode),0) as 'Real Anual',
isnull((Select T0.Pres_anual - SUM(monto) From #tempctgastospresupuestado where Account = T0.AcctCode),0) as 'Diferencia Anual',
(Select  Case When Sum(monto) = 0 then 0 else
	 ((Sum(monto) * 100) / T0.pres_anual) end as 'Porcentaje anual'

 from #tempctgastospresupuestado where Account = T0.AcctCode) as '% Avance del año',

 (Select  t1.DebLTotal from BGT1 T1 inner join OBGT T2 on T2.[AbsId] = T1.[BudgId] where (t1.Line_ID + 1) = 1 --selección de línea del mes
			and t2.AcctCode = t0.acctcode --selección de la cuenta
			and T2.[Instance]  = @instancia) as 'Presupuesto Enero', --validación de la instancia del presupuesto es la del año que corresponde

isnull((Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 1),0) as 'Real Enero',

isnull((Select  t1.DebLTotal from BGT1 T1 inner join OBGT T2 on T2.[AbsId] = T1.[BudgId] where (t1.Line_ID + 1) = 1 and t2.AcctCode = t0.acctcode and T2.[Instance]  = @instancia) - /*lo presupuestado*/
		- /*Menos lo consumido real*/
		(Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 1),0) as 'Diferencia Enero',

Case when isnull((Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 1),0) = 0 then 0
	else  (((Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 1) *100 ) / --el  monto del mes * 100 entre
	(Select  t1.DebLTotal from BGT1 T1 inner join OBGT T2 on T2.[AbsId] = T1.[BudgId] where (t1.Line_ID + 1) = 1 and t2.AcctCode = t0.acctcode and T2.[Instance]  = @instancia)) /*lo presupuestado*/
end as 'Avance % Enero',
-------------------------------------------------------------------------------
 (Select  t1.DebLTotal from BGT1 T1 inner join OBGT T2 on T2.[AbsId] = T1.[BudgId] where (t1.Line_ID + 1) = 2 --selección de línea del mes
			and t2.AcctCode = t0.acctcode --selección de la cuenta
			and T2.[Instance]  = @instancia) as 'Presupuesto Febrero', --validación de la instancia del presupuesto es la del año que corresponde

isnull((Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 2),0) as 'Real Febrero',

isnull((Select  t1.DebLTotal from BGT1 T1 inner join OBGT T2 on T2.[AbsId] = T1.[BudgId] where (t1.Line_ID + 1) = 2 and t2.AcctCode = t0.acctcode and T2.[Instance]  = @instancia) - /*lo presupuestado*/
		- /*Menos lo consumido real*/
		(Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 2),0) as 'Diferencia Febrero',

Case when isnull((Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 2),0) = 0 then 0
	else  (((Select Monto From #tempctgastospresupuestado where Account = T0.AcctCode and Mes = 2) *100 ) / --el  monto del mes * 100 entre
	(Select  t1.DebLTotal from BGT1 T1 inner join OBGT T2 on T2.[AbsId] = T1.[BudgId] where (t1.Line_ID + 1) = 2 and t2.AcctCode = t0.acctcode and T2.[Instance]  = @instancia)) /*lo presupuestado*/
end as 'Avance % Febrero',
.
.
.
.
Into #avanceconPresupuesto
 from 
#tempDATOSCUENTASCONPRESUPUESTO t0
--Quitamos las cuentas de Gastos Financieros para que coincida con el reporte de Cuentas de Perdida y Ganancia de Finanzas
--Orden de j. Torres.
where t0.AcctCode not in ('720001','720002','720008','720010','720011','730001','740003')

Select * from #avanceconPresupuesto  
---- FIN DE CAMBIOS 01/07/2021

UNION ALL
--Agregamos las cuentas que no tenían presupuesto
--validamos que si tuviera algún movimiento en el año
Select * from #avancesinPresupuesto where [Real Anual] > 0

Y ya luego borras las tablas temporales. Espero te pueda ayudar.

NOTA: Revisa bien el query, ya que usa algunos campos de usuario que seguramente no tendrás pero son temas de validaciones internas, y complementa los meses en los que solo puse una parte…

tenguman · 11 Octubre, 2021 19:58

Evite usar tablas temporales, pues pueden generar un consumo elevado de memoria, o al menos aplicar la sentencia NO LOCK

Si se van a utilizar consultas frecuentemente, mejor habilitar vistas o si se van a retornar la información de un solo registro, almacenarlas en variables

Ares17000 · 11 Octubre, 2021 21:03

Esta es una consulta que se dispara 1 vez al día, a las 8am para enviar la información del avance de los presupuestos a la dirección.

Las vistas no permiten crear tantos calculos y necesidades como se puede hacer en un SP, por eso lo realicé de esa manera.

Pero si tienes razón, me faltaron los NO LOCK.

SidV · 11 Noviembre, 2021 21:51

¿En qué quedó este tema @MiguelHG ? ¿Te sirvieron las respuestas que recibiste?

system · 12 Diciembre, 2021 08:51

Este tema se cerró automáticamente 30 días después de la última publicación. No se permiten nuevas respuestas.