USING Windows XP SP1; SQL 8
I need to create a stored procedure which displays the results of a temp table which is populated by that stored procedure. It's complex form me to write becuase it's my first SP.
to get my final results I need to run a couple of querys, lets start with Q1 and Q2 which need a parameter called @User. Q2 uses Q1 as an inner table and it (Q2) will be used in Q3. Q3 pulls the transactional data using Q2 and the parameters @AnoDesde and @AnoHasta to filter for a specific year range, the results of Q3 need to be stored in a temp table, #tp_Datos, becuase I then need to run a couple of insert querys base on it to compute subtotales, which are to be stored in that same temp table. After all the insert querys are ran I would like display all the records of #tp_Datos.
Here is the code I have written so far:
I need to create a stored procedure which displays the results of a temp table which is populated by that stored procedure. It's complex form me to write becuase it's my first SP.
to get my final results I need to run a couple of querys, lets start with Q1 and Q2 which need a parameter called @User. Q2 uses Q1 as an inner table and it (Q2) will be used in Q3. Q3 pulls the transactional data using Q2 and the parameters @AnoDesde and @AnoHasta to filter for a specific year range, the results of Q3 need to be stored in a temp table, #tp_Datos, becuase I then need to run a couple of insert querys base on it to compute subtotales, which are to be stored in that same temp table. After all the insert querys are ran I would like display all the records of #tp_Datos.
Here is the code I have written so far:
Code:
CREATE PROCEDURE sp_DatosParaSistemaAPH
(
@User int,
@AnoDesde int, --Start Year in number format CCYY
@AnoHasta int --End Year in number format CCYY
) as
--TABLA VARIABLE PARA DEPARTAMENTOS VISIBLES
DECLARE @tv_Dptos TABLE (
UnidadNegocio tinyint not null,
Dpto_ID int not null,
Departamento nvarchar(50) not null,
DepnumHACHESYS tinyint not null)
Insert @tv_Dptos
--Q1 query which determines the stores a user can see
SELECT UnidadNegocio,Dpto_ID, Departamento, DepnumHACHESYS
FROM tblDepartamentos
WHERE UnidadNegocio=
(select vp_unidad from tblUsers where @User =
(select @User from tblcurrentUser))
UNION
SELECT UnidadNegocio,Dpto_ID,Departamento,DepnumHACHESYS
FROM tblDepartamentos
WHERE Gerente Like
'*' + convert(varchar,(select @User from tblcurrentUser))
UNION
SELECT UnidadNegocio,Dpto_ID,Departamento,DepnumHACHESYS
FROM tblDepartamentos
WHERE convert(int,Reporta_A)=
convert(int,(SELECT @User FROM tblUsers
WHERE MulitipleCenter = -1 AND @User =
(SELECT @User FROM tblCurrentUser)))
AND Reporta_A IS NOT NULL
UNION
SELECT UnidadNegocio,Dpto_ID,Departamento,DepnumHACHESYS
FROM tblDepartamentos
WHERE Dpto_ID Between
CASE WHEN
(SELECT SecurityLevel FROM tblCurrentUser)=5 THEN
1 ELSE 0 END
And
CASE WHEN (SELECT SecurityLevel FROM tb
lCurrentUser)=5 THEN 999 ELSE 0 END
or
Dpto_ID <>(SELECT costcenter FROM tblUsers
WHERE @User =
(select @User from tblCurrentUser))
--Q2 query that selects the Stores and Accounts User Can See
Declare @tv_CtasVisibles Table(
UnidadNegocio tinyint not null,
Dpto_ID int not null,
Departamento nvarchar(50) not null,
GrupoCtas_ID int not null,
GrupoDeCuentas nvarchar(50) not null,
Centro nvarchar(8) not null,
Depnum nvarchar(8) not null)
Insert @tv_CtasVisibles
SELECT
@tv_Dptos.UnidadNegocio,
@tv_Dptos.Dpto_ID,
@tv_Dptos.Departamento,
T1.GrupoCtas_ID,
T1.GrupoDeCuentas,
tblDepartamentos.CentroCompleto as Centro,
tblDepartamentos.DepnumCompleto as Depnum
FROM
tblGrupoDeCuentas as T1, @tv_Dptos
INNER JOIN tblDepartamentos ON
@tv_Dptos.Dpto_ID = tblDepartamentos.Dpto_ID
UNION
SELECT
tblDepartamentos.UnidadNegocio,
T2.Dpto_ID,
tblDepartamentos.Departamento,
T2.GrupoCtasID,
T1.GrupoDeCuentas,
tblDepartamentos.CentroCompleto as Centro,
tblDepartamentos.DepnumCompleto as Depnum
FROM
tblGrupoDeCuentas AS T1 INNER JOIN tblUserAdditionalAccess AS T2
ON T1.GrupoCtas_ID = T2.GrupoCtasID
INNER JOIN tblDepartamentos
ON tblDepartamentos.Dpto_ID = T2.Dpto_ID
WHERE
(((convert(int,@User))=(Select @User From tblCurrentUser)))
--Q3 query that selects the transactional data
select
T4.UnidadNegocio,
T4.GrupoCtas_ID AS 'GrupoCuentas_ID',
T2.Cuenta_ID,
T4.Dpto_ID,
T1.Cuenta,
T1.Centro,
T1.Departamento,
T1.Ano,
T1.Mes,
Sum((round((T1.Monto/1000),2)*-1)) AS Monto,
T1.Origen
into #tp_Datos
from
@tv_CtasVisibles AS T4 LEFT OUTER JOIN v_uniDatosMACOLA AS T1 ON
T4.Depnum = t1.Departamento AND T4.Centro = T1.Centro
LEFT OUTER JOIN tblCuentas AS T2 ON T2.Cuenta_Numero = T1.Cuenta
WHERE
t4.GrupoCtas_ID = t2.GrupoCuentas_ID AND
T1.Ano Between @AnoDesde And @AnoHasta
GROUP BY
T4.UnidadNegocio,
T4.GrupoCtas_ID,
T2.Cuenta_ID,
T4.Dpto_ID,
T1.Cuenta,
T1.Centro,
T1.Departamento,
T1.Ano,
T1.Mes,
T1.Origen
HAVING
Sum((round((T1.Monto/1000),2)*-1)) <>0
--Q4 query that calculates subtotals based on the data
--of #tp_Datos and inserts the result
--in the same table.
insert #tp_Datos (Centro, Departamento, Ano, Mes, Monto, Origen)
SELECT
T1.Centro, T1.Departamento, T1.Ano, T1.Mes,
Sum(T1.Monto)+
(SELECT Sum(sq1.Monto) FROM v_DatosParaSistemaAPH AS sq1
WHERE sq1.GrupoCuentas_ID = 7 and
sq1.Centro = T1.Centro and
sq1.Departamento = T1.Departamento and
sq1.Ano = T1.Ano and
sq1.Mes = T1.Mes and
sq1.Origen = T1.Origen
GROUP BY
sq1.GrupoCuentas_ID,
sq1.Centro,
sq1.Departamento,
sq1.Ano,
sq1.Mes,
sq1.Origen) AS Monto,
T1.Origen
FROM v_DatosParaSistemaAPH AS T1
WHERE T1.GrupoCuentas_ID=6
GROUP BY
T1.Centro, T1.Departamento, T1.Ano, T1.Mes, T1.Origen
RETURN