April 3, 2009 at 1:04 pm
I know it can be significantly improved, but im still learning XD, i add the code here, and the sql file in atachments for just in case.
the tables that i use r coded inside the store procedure. TEMP_DET_ALM_POR_TIENDA and TEMP_TOT_DET_ALM_POR_TIENDA.
😀
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IND_IV00101')
create index [IND_IV00101] ON [dbo].[IV00101] ([USCATVLS_2,USCATVLS_3,USCATVLS_4])
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'VW_DET_ALM_POR_TIENDA') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW VW_DET_ALM_POR_TIENDA
GO
create view VW_DET_ALM_POR_TIENDA WITH SCHEMABINDING
as
select art.ITEMNMBR,art.USCATVLS_2,art.USCATVLS_3,art.USCATVLS_4,
trf.DOCNUMBR,trf.DOCDATE,trf.HSTMODUL,trf.EXTDCOST,trf.TRXLOCTN,
cat.USCATVAL,cat.USCATNUM, cat.Image_Url,
flt.POPRCTNM,flt.Landed_Cost_Id,flt.Orig_TotalLandedCostAmt
from dbo.iv00101 art right join dbo.iv40600 cat on art.USCATVLS_3 = cat.USCATVAL
right join dbo.iv30300 trf on trf.ITEMNMBR = art.ITEMNMBR
left join dbo.POP30700 flt on flt.POPRCTNM = trf.DOCNUMBR
GO
--%%%%%%%%%%%%%%%%PROCEDURE%%%%%%%%%%%%%
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PROC_DET_ALMACEN_POR_TIENDA') AND type in (N'P'))
DROP PROCEDURE PROC_DET_ALMACEN_POR_TIENDA
GO
CREATE PROCEDURE PROC_DET_ALMACEN_POR_TIENDA (@MES nvarchar(2), @ANIO nchar(4),@TIENDA INT)
AS
SET NOCOUNT ON -- SI SE RETIRA ESTA LINEA, EN EXCEL MARCARA ERROR "OPERATION IS NOT ALLOWED WHEN OBJECT IS CLOSED"
BEGIN
declare @invInicialMES as nvarchar(2),
@invInicialANIO as nchar(4),
@diasMes as int,
@diasANIO as int,
@SENTENCIASQL VARCHAR(5000),
@DIVISION NVARCHAR(11),
@DEPARTAMENTO NVARCHAR(255),
@DESC_DEPARTAMENTO VARCHAR(255),
@INV_INICIAL NUMERIC(14,2),
@COMPRAS NUMERIC(14,2),
@GASTOS_DE_COMPRA NUMERIC(14,2),
@DEVOL_COMPRAS NUMERIC(14,2),
@DIF_COMPRAS NUMERIC(14,2),
@COMPRAS_NETAS NUMERIC(14,2),
@TRANSF_ENTRADA NUMERIC(14,0),
@TRANSF_SALIDA NUMERIC(14,0),
@INV_DISPONIBLE NUMERIC(14,2),
@INV_FINAL NUMERIC(14,2),
@COSTO_VENTAS NUMERIC(14,2),
@ROTACION NUMERIC(14,2),
@VECES NUMERIC(8,2),
-----
@TOT_INV_INICIAL NUMERIC(14,2),
@TOT_COMPRAS NUMERIC(14,2),
@TOT_GASTOS_DE_COMPRA NUMERIC(14,2),
@TOT_DEVOL_COMPRAS NUMERIC(14,2),
@TOT_DIF_COMPRAS NUMERIC(14,2),
@TOT_COMPRAS_NETAS NUMERIC(14,2),
@TOT_TRANSF_ENTRADA NUMERIC(14,0),
@TOT_TRANSF_SALIDA NUMERIC(14,0),
@TOT_INV_DISPONIBLE NUMERIC(14,2),
@TOT_INV_FINAL NUMERIC(14,2),
@TOT_COSTO_VENTAS NUMERIC(14,2),
@TOT_ROTACION NUMERIC(8,2),
@TOT_VECES NUMERIC(8,2)
--INICIALIZADOS PARAEVITAR SUMAS CON NULL
SET @TOT_INV_INICIAL = 0
SET @TOT_COMPRAS = 0
SET @TOT_GASTOS_DE_COMPRA= 0
SET @TOT_DEVOL_COMPRAS= 0
SET @TOT_DIF_COMPRAS = 0
SET @TOT_COMPRAS_NETAS = 0
SET @TOT_TRANSF_ENTRADA = 0
SET @TOT_TRANSF_SALIDA = 0
SET @TOT_INV_DISPONIBLE = 0
SET @TOT_INV_FINAL = 0
SET @TOT_COSTO_VENTAS = 0
IF @MES = '1' -- si el mes de reporte es enero el inventario inicial debe ser hasta diciembre del año anterior
BEGIN
SET @invInicialMES = '12'
SET @invInicialANIO = Cast((Cast(@ANIO as int)-1) as varchar)
END
ELSE
BEGIN
SET @invInicialMES = Cast((Cast(@MES as int)-1) as varchar)
SET @invInicialANIO = @ANIO
END
--AÑO BISIESTO
IF ((@ANIO % 4 = 0) and ((@ANIO % 100 <> 0) or (@ANIO % 400 = 0)))
BEGIN
IF (@MES = 2)
SET @diasMES = 29
SET @diasANIO = 366
END
ELSE
BEGIN
IF (@MES = 2)
SET @diasMES = 28
SET @diasANIO = 365
END
IF (@MES=1 or @MES=3 or @MES=5 or @MES=7 or @MES=8 or @MES=10 or @MES=12)
SET @diasMES = 31
IF (@MES=4 or @MES=6 or @MES=9 or @MES=11)
SET @diasMES = 30
-- FIN AÑO BISIESTO
SET @SENTENCIASQL = ''
SET @SENTENCIASQL = 'IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'+CHAR(39)+'TEMP_DET_ALM_POR_TIENDA'+CHAR(39)+') AND type in (N'+CHAR(39)+'U'+CHAR(39)+'))'
SET @SENTENCIASQL = @SENTENCIASQL+CHAR(13)+'DROP TABLE TEMP_DET_ALM_POR_TIENDA'
EXEC(@SENTENCIASQL)
set @SENTENCIASQL = ''
set @SENTENCIASQL = 'CREATE TABLE TEMP_DET_ALM_POR_TIENDA(
DIVISION NVARCHAR(11),
DEPARTAMENTO NVARCHAR(255),
INV_INICIAL NUMERIC(14,2),
COMPRAS NUMERIC(14,2),
GASTOS_DE_COMPRA NUMERIC(14,2),
DEVOL_COMPRAS NUMERIC(14,2),
DIF_COMPRAS NUMERIC(14,2),
COMPRAS_NETAS NUMERIC(14,2),
TRANSF_ENTRADA NUMERIC(14,0),
TRANSF_SALIDA NUMERIC(14,0),
INV_DISPONIBLE NUMERIC(14,2),
INV_FINAL NUMERIC(14,2),
COSTO_VENTAS NUMERIC(14,2),
ROTACION NUMERIC(14,2),
VECES NUMERIC(8,2))'
exec (@SENTENCIASQL)
IF (@TIENDA = 1)--EN EL CODIGO DE VISUAL bASIC EL PROCEDURE DEBE SER LLAMADO PRIMERO CON TIENDA = 4 PARA CREAR LA HOJA DE TOTALES, Y SE CREE LA TABLA DE TOTALES
BEGIN
SET @SENTENCIASQL = ''
SET @SENTENCIASQL = 'IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'+CHAR(39)+'TEMP_TOT_DET_ALM_POR_TIENDA'+CHAR(39)+') AND type in (N'+CHAR(39)+'U'+CHAR(39)+'))'
SET @SENTENCIASQL = @SENTENCIASQL+CHAR(13)+'DROP TABLE TEMP_TOT_DET_ALM_POR_TIENDA'
EXEC(@SENTENCIASQL)
set @SENTENCIASQL = ''
set @SENTENCIASQL = 'CREATE TABLE TEMP_TOT_DET_ALM_POR_TIENDA(
DIVISION NVARCHAR(11),
DEPARTAMENTO NVARCHAR(255),
INV_INICIAL NUMERIC(14,2),
COMPRAS NUMERIC(14,2),
GASTOS_DE_COMPRA NUMERIC(14,2),
DEVOL_COMPRAS NUMERIC(14,2),
DIF_COMPRAS NUMERIC(14,2),
COMPRAS_NETAS NUMERIC(14,2),
TRANSF_ENTRADA NUMERIC(14,0),
TRANSF_SALIDA NUMERIC(14,0),
INV_DISPONIBLE NUMERIC(14,2),
INV_FINAL NUMERIC(14,2),
COSTO_VENTAS NUMERIC(14,2),
ROTACION NUMERIC(14,2),
VECES NUMERIC(8,2))'
EXEC(@SENTENCIASQL)--65
END
Declare CurDepartamento cursor FAST_FORWARD READ_ONLY for
SELECT distinct USCATVLS_3, USCATVLS_2 FROM VW_DET_ALM_POR_TIENDA where uscatnum = 3
--Select USCATVAL from iv40600 where USCATNUM = 3
OPEN CurDepartamento
------------INICIO DE CICLO
FETCH NEXT FROM CurDepartamento
INTO @DEPARTAMENTO,@DIVISION
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @DIVISION = (SELECT USCATVLS_2 FROM VW_DET_ALM_POR_TIENDA WHERE USCATVLS_3 = @DEPARTAMENTO)
SET @DESC_DEPARTAMENTO =(select RTRIM(SUBSTRING(Image_URL,CHARINDEX('-',Image_URL)+1,255)) FROM iv40600
WHERE USCATVAL = @DEPARTAMENTO AND USCATNUM = 3)
SET @INV_INICIAL = (select ISNULL(sum(EXTDCOST),0) FROM VW_DET_ALM_POR_TIENDA
WHERE month(docdate)= @invInicialMES
AND year(docdate)=@invInicialANIO
AND USCATVLS_3 = @DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)
SET @COMPRAS = (SELECT ISNULL(sum(EXTDCOST),0) FROM VW_DET_ALM_POR_TIENDA
WHERE DOCNUMBR like '%RB%'
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
SET @GASTOS_DE_COMPRA = (SELECT ISNULL(sum(Orig_TotalLandedCostAmt),0) FROM VW_DET_ALM_POR_TIENDA
WHERE Landed_Cost_Id like '%FLET%'
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
SET @DEVOL_COMPRAS = 0 --PENDIENTE PENDIENTE PENDIENTE PENDIENTE
SET @DIF_COMPRAS = 0 -- PENDIENTE PENDIENTE PENDIENTE PENDIENTE
SET @COMPRAS_NETAS = @COMPRAS - @GASTOS_DE_COMPRA - @DEVOL_COMPRAS
SET @TRANSF_ENTRADA = (SELECT ISNULL (SUM (EXTDCOST),0) FROM VW_DET_ALM_POR_TIENDA
WHERE EXTDCOST > 0 --TRANFERENCIAS POSITIVAS
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
SET @TRANSF_SALIDA = (SELECT ISNULL (SUM (EXTDCOST),0) FROM VW_DET_ALM_POR_TIENDA
WHERE EXTDCOST < 0 --TRANFERENCIAS POSITIVAS
AND month(docdate)= @MES --DEL MES DE REPORTE
AND year(docdate)=@ANIO --DEL AÑO DEL REPORTE
AND USCATVLS_3 = @DEPARTAMENTO -- QUE SEAN DE ESE DEPARTAMENTO
AND USCATVLS_2 = @DIVISION
--AND USCATNUM = 3 -- cOMPLEMENTO DE INDENTIFICADOR DE DEPARTAMENTO
AND @TIENDA = Case ISNUMERIC(substring(TRXLOCTN,1,1)) when 0 then 0 else substring(TRXLOCTN,1,1) end)-- DE LA TIENDA REQUERIDA
SET @INV_DISPONIBLE = @INV_INICIAL + @COMPRAS_NETAS
SET @INV_FINAL = ISNULL(@INV_INICIAL + @TRANSF_ENTRADA - @TRANSF_SALIDA,0)
SET @COSTO_VENTAS = ISNULL(@INV_DISPONIBLE - @INV_FINAL,0)
IF(@INV_FINAL <> 0)
IF(@COSTO_VENTAS/@INV_FINAL <> 0)
SET @ROTACION = ISNULL(@diasMES/(@COSTO_VENTAS/@INV_FINAL),0)
ELSE
SET @ROTACION = 0
IF(@ROTACION <> 0)
SET @VECES = ISNULL(@diasANIO / @ROTACION,0)
ELSE
SET @VECES = 0
--ACUMULADO DE TOTALES
SET @TOT_INV_INICIAL = @TOT_INV_INICIAL + @INV_INICIAL
SET @TOT_COMPRAS = @TOT_COMPRAS + @COMPRAS
SET @TOT_GASTOS_DE_COMPRA= @TOT_GASTOS_DE_COMPRA + @GASTOS_DE_COMPRA
SET @TOT_DEVOL_COMPRAS= @TOT_DEVOL_COMPRAS + @DEVOL_COMPRAS
SET @TOT_DIF_COMPRAS = @TOT_DIF_COMPRAS + @DIF_COMPRAS
SET @TOT_COMPRAS_NETAS = @TOT_COMPRAS_NETAS + @COMPRAS_NETAS
SET @TOT_TRANSF_ENTRADA = @TOT_TRANSF_ENTRADA + @TRANSF_ENTRADA
SET @TOT_TRANSF_SALIDA = @TOT_TRANSF_SALIDA + @TRANSF_SALIDA
SET @TOT_INV_DISPONIBLE = @TOT_INV_DISPONIBLE + @INV_DISPONIBLE
SET @TOT_INV_FINAL = @TOT_INV_FINAL + @INV_FINAL
SET @TOT_COSTO_VENTAS = @TOT_COSTO_VENTAS + @COSTO_VENTAS
INSERT INTO TEMP_DET_ALM_POR_TIENDA
(DIVISION,DEPARTAMENTO,INV_INICIAL,COMPRAS ,GASTOS_DE_COMPRA ,DEVOL_COMPRAS ,DIF_COMPRAS ,COMPRAS_NETAS ,TRANSF_ENTRADA ,TRANSF_SALIDA ,INV_DISPONIBLE ,INV_FINAL ,COSTO_VENTAS,ROTACION ,VECES)
VALUES
(@DIVISION,@DESC_DEPARTAMENTO,@INV_INICIAL,@COMPRAS,@GASTOS_DE_COMPRA,@DEVOL_COMPRAS,@DIF_COMPRAS,@COMPRAS_NETAS,@TRANSF_ENTRADA,@TRANSF_SALIDA,@INV_DISPONIBLE,@INV_FINAL ,@COSTO_VENTAS,@ROTACION,@VECES)
IF (@TIENDA = 1)
BEGIN
INSERT INTO TEMP_TOT_DET_ALM_POR_TIENDA
(DIVISION,DEPARTAMENTO,INV_INICIAL,COMPRAS ,GASTOS_DE_COMPRA ,DEVOL_COMPRAS ,DIF_COMPRAS ,COMPRAS_NETAS ,TRANSF_ENTRADA ,TRANSF_SALIDA ,INV_DISPONIBLE ,INV_FINAL ,COSTO_VENTAS,ROTACION ,VECES)
VALUES
(@DIVISION,@DESC_DEPARTAMENTO,@INV_INICIAL,@COMPRAS,@GASTOS_DE_COMPRA,@DEVOL_COMPRAS,@DIF_COMPRAS,@COMPRAS_NETAS,@TRANSF_ENTRADA,@TRANSF_SALIDA,@INV_DISPONIBLE,@INV_FINAL ,@COSTO_VENTAS,@ROTACION,@VECES)
END
IF (@TIENDA = 2 OR @TIENDA = 3)
BEGIN
UPDATE TEMP_TOT_DET_ALM_POR_TIENDA
SET INV_INICIAL = INV_INICIAL + @INV_INICIAL, COMPRAS = COMPRAS + @COMPRAS ,GASTOS_DE_COMPRA = GASTOS_DE_COMPRA + @GASTOS_DE_COMPRA,DEVOL_COMPRAS = DEVOL_COMPRAS + @DEVOL_COMPRAS,DIF_COMPRAS = DIF_COMPRAS + @DIF_COMPRAS ,
COMPRAS_NETAS = COMPRAS_NETAS + @COMPRAS_NETAS,TRANSF_ENTRADA = TRANSF_ENTRADA+ @TRANSF_ENTRADA,TRANSF_SALIDA = TRANSF_SALIDA + @TRANSF_SALIDA,INV_DISPONIBLE = INV_DISPONIBLE+@INV_DISPONIBLE,INV_FINAL = INV_FINAL + @INV_FINAL ,COSTO_VENTAS = COSTO_VENTAS + @COSTO_VENTAS
WHERE DIVISION = @DIVISION AND DEPARTAMENTO = @DESC_DEPARTAMENTO
--SE OBTIENEN DATOS DESPUES DE ACTUALIZADOS PARA CALCULAR LOS ULTIMOS 2 VALORES
SET @INV_FINAL = (SELECT INV_FINAL FROM TEMP_TOT_DET_ALM_POR_TIENDA WHERE DIVISION = @DIVISION AND DEPARTAMENTO = @DESC_DEPARTAMENTO)
SET @COSTO_VENTAS = (SELECT COSTO_VENTAS FROM TEMP_TOT_DET_ALM_POR_TIENDA WHERE DIVISION = @DIVISION AND DEPARTAMENTO = @DESC_DEPARTAMENTO)
IF(@INV_FINAL <> 0)
BEGIN
IF(@COSTO_VENTAS/@INV_FINAL <> 0)
SET @ROTACION = ISNULL(@diasMES/(@COSTO_VENTAS/@INV_FINAL),0)
ELSE
SET @ROTACION = 0
END
ELSE
SET @ROTACION = 0
IF(@ROTACION <> 0)
SET @VECES = ISNULL(@diasANIO / @ROTACION,0)
ELSE
SET @VECES = 0
UPDATE TEMP_TOT_DET_ALM_POR_TIENDA
SET ROTACION = @ROTACION, VECES = @VECES
WHERE DIVISION = @DIVISION AND DEPARTAMENTO = @DESC_DEPARTAMENTO
END
FETCH NEXT FROM CurDepartamento
INTO @DEPARTAMENTO, @DIVISION
END --END WHILE
--TOTALES CALCULADOS EN BASE A TOTALES ACUMULADOS
IF(@TOT_INV_FINAL <> 0)
BEGIN
IF(@TOT_COSTO_VENTAS/@TOT_INV_FINAL <> 0)
SET @TOT_ROTACION = ISNULL(@diasMES/(@TOT_COSTO_VENTAS/@TOT_INV_FINAL),0)
ELSE
SET @TOT_ROTACION = 0
END
ELSE
SET @TOT_ROTACION = 0
IF(@TOT_ROTACION <> 0)
SET @TOT_VECES = ISNULL(@diasANIO / @TOT_ROTACION,0)
ELSE
SET @TOT_VECES = 0
INSERT INTO TEMP_DET_ALM_POR_TIENDA
(DIVISION,DEPARTAMENTO,INV_INICIAL,COMPRAS ,GASTOS_DE_COMPRA ,DEVOL_COMPRAS ,DIF_COMPRAS ,COMPRAS_NETAS ,TRANSF_ENTRADA ,TRANSF_SALIDA ,INV_DISPONIBLE ,INV_FINAL ,COSTO_VENTAS ,ROTACION ,VECES)
VALUES
(' ','TOTALES',@TOT_INV_INICIAL,@TOT_COMPRAS,@TOT_GASTOS_DE_COMPRA,@TOT_DEVOL_COMPRAS,@TOT_DIF_COMPRAS,@TOT_COMPRAS_NETAS,@TOT_TRANSF_ENTRADA,@TOT_TRANSF_SALIDA,@TOT_INV_DISPONIBLE,@TOT_INV_FINAL ,@TOT_COSTO_VENTAS,@TOT_ROTACION,@TOT_VECES)
----------------------------TOTALES DE TABLA TOTALES
IF (@TIENDA = 4)
BEGIN
SET @TOT_INV_INICIAL = ISNULL((SELECT SUM(INV_INICIAL) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_COMPRAS = ISNULL((SELECT SUM(COMPRAS) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_GASTOS_DE_COMPRA= ISNULL((SELECT SUM(GASTOS_DE_COMPRA) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_DEVOL_COMPRAS= ISNULL((SELECT SUM(DEVOL_COMPRAS) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_DIF_COMPRAS = ISNULL((SELECT SUM(DIF_COMPRAS) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_COMPRAS_NETAS = ISNULL((SELECT SUM(COMPRAS_NETAS) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_TRANSF_ENTRADA = ISNULL((SELECT SUM(TRANSF_ENTRADA) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_TRANSF_SALIDA = ISNULL((SELECT SUM(TRANSF_SALIDA) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_INV_DISPONIBLE = ISNULL((SELECT SUM(INV_DISPONIBLE) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_INV_FINAL = ISNULL((SELECT SUM(INV_FINAL) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
SET @TOT_COSTO_VENTAS = ISNULL((SELECT SUM(COSTO_VENTAS) FROM TEMP_TOT_DET_ALM_POR_TIENDA),0)
IF(@TOT_INV_FINAL <> 0)
BEGIN
IF(@TOT_COSTO_VENTAS/@TOT_INV_FINAL <> 0)
SET @TOT_ROTACION = ISNULL(@diasMES/(@TOT_COSTO_VENTAS/@TOT_INV_FINAL),0)
ELSE
SET @TOT_ROTACION = 0
END
ELSE
SET @TOT_ROTACION = 0
IF(@TOT_ROTACION <> 0)
SET @TOT_VECES = ISNULL(@diasANIO / @TOT_ROTACION,0)
ELSE
SET @TOT_VECES = 0
INSERT INTO TEMP_TOT_DET_ALM_POR_TIENDA
(DIVISION,DEPARTAMENTO,INV_INICIAL,COMPRAS ,GASTOS_DE_COMPRA ,DEVOL_COMPRAS ,DIF_COMPRAS ,COMPRAS_NETAS ,TRANSF_ENTRADA ,TRANSF_SALIDA ,INV_DISPONIBLE ,INV_FINAL ,COSTO_VENTAS ,ROTACION ,VECES)
VALUES
(' ','TOTALES',@TOT_INV_INICIAL,@TOT_COMPRAS,@TOT_GASTOS_DE_COMPRA,@TOT_DEVOL_COMPRAS,@TOT_DIF_COMPRAS,@TOT_COMPRAS_NETAS,@TOT_TRANSF_ENTRADA,@TOT_TRANSF_SALIDA,@TOT_INV_DISPONIBLE,@TOT_INV_FINAL ,@TOT_COSTO_VENTAS,@TOT_ROTACION,@TOT_VECES)
END
--ESTE QUERY RETORNA EL RECORDSET A EXCEL--
IF (@TIENDA = 4)
SELECT * FROM TEMP_TOT_DET_ALM_POR_TIENDA
ELSE
SELECT * FROM TEMP_DET_ALM_POR_TIENDA
deallocate CurDepartamento
END
April 3, 2009 at 1:14 pm
Hi
Just for information. How many rows will this return:
Declare CurDepartamento cursor FAST_FORWARD READ_ONLY for
SELECT distinct USCATVLS_3, USCATVLS_2 FROM VW_DET_ALM_POR_TIENDA where uscatnum = 3
The count of rows you handle in your cursor?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 3, 2009 at 1:21 pm
36 rows, why?
April 3, 2009 at 1:27 pm
Hi
This may indicate the factor to be optimized. 😉
Is it possible that you provide the CREATE statements of the tables iv40600, iv00101, iv30300 and POP30700?
Best would be some sample data (sure non real data if they are financial or personal!)?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 3, 2009 at 2:35 pm
Seems that no sample data are available.
For everybody who wants join the topic
I attempted to create some test data with first investigated requirements... Feel free to correct it 😉
I mapped the view to a table:
-- DROP TABLE VW_DET_ALM_POR_TIENDA
IF (OBJECT_ID('VW_DET_ALM_POR_TIENDA') IS NULL)
BEGIN
CREATE TABLE VW_DET_ALM_POR_TIENDA
(
ITEMNMBR INT, -- not used
USCATVLS_2 NVARCHAR(20), -- Division
USCATVLS_3 NVARCHAR(255), -- Departamento
USCATVLS_4 NVARCHAR(20), -- not used
DOCNUMBR NVARCHAR(30), -- sometimes %RB%
DOCDATE DATETIME, -- Any date
HSTMODUL NVARCHAR(20), -- not used
EXTDCOST MONEY, -- Cost
TRXLOCTN VARCHAR(50), -- first character may be numeric - matches to TIENDA (1-4)
USCATVAL NVARCHAR(255), -- equal to USCATVLS_3 (Departamento)
USCATNUM INT, -- Either always 3 or not used
Image_Url NVARCHAR(100), -- http://www.anywhere.com/bla-@DESC_DEPARTAMENTO
POPRCTNM VARCHAR(50), -- not used
Landed_Cost_Id NVARCHAR(100), -- Sometimes %FLET%
Orig_TotalLandedCostAmt NUMERIC(14,2) -- Cost
)
INSERT INTO VW_DET_ALM_POR_TIENDA (
ITEMNMBR,
USCATVLS_2,
USCATVLS_3,
USCATVLS_4,
DOCNUMBR,
DOCDATE,
HSTMODUL,
EXTDCOST,
TRXLOCTN,
USCATVAL,
USCATNUM,
Image_Url,
POPRCTNM,
Landed_Cost_Id,
Orig_TotalLandedCostAmt
)
SELECT 1 AS ITEMNBR,
'Division' + CONVERT(VARCHAR(2), N % 10) AS USCATVLS_2,
'Departamento' + CONVERT(VARCHAR(3), N % 100) AS USCATVLS_3,
'Uscatvls4_' + CONVERT(VARCHAR(10), N) AS USCATVLS_4,
'Doc_' + CASE N % 4 WHEN 0 THEN 'RB' ELSE 'NOT' END + '_Numbr' AS DOCNUMBR,
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) + N AS DOCDATE,
'HstModul' + CONVERT(VARCHAR(10), N) AS HSTMODUL,
N AS EXTDCOST,
CASE WHEN N % 5 = 0 THEN '' ELSE CONVERT(CHAR(1), N % 5) END + 'TRXLOCTN' AS TRXLOCTN,
'Departamento' + CONVERT(VARCHAR(3), N % 100) AS USCATVAL,
3 AS USCATNUM,
'www.anywhere.com/blah-DescDep' + CONVERT(VARCHAR(10), N) AS Image_Url,
'PoPrcntM' + CONVERT(VARCHAR(10), N) AS POPRCTNM,
'LandedCost_' + CASE N % 7 WHEN 0 THEN 'FLET' ELSE '' END + '_Id' AS Landed_Cost_Id,
N * 2 AS Orig_TotalLandedCostAmt
FROM Tally
END
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 3, 2009 at 3:24 pm
Awww sorry for the delay, very nice deductions of the data Florian, really, ill add some observations to it.
IF (OBJECT_ID('VW_DET_ALM_POR_TIENDA') IS NULL)
BEGIN
CREATE TABLE VW_DET_ALM_POR_TIENDA
(
ITEMNMBR INT, --- not used
USCATVLS_2 NVARCHAR(20), --- Division
USCATVLS_3 NVARCHAR(255), --- Departamento
USCATVLS_4 NVARCHAR(20), --- SubDepartment
DOCNUMBR NVARCHAR(30), --- sometimes %RB% used to filter RECIBE transactions RB
DOCDATE DATETIME, --- Any date
HSTMODUL NVARCHAR(20), --- not used
EXTDCOST MONEY, --- Cost
TRXLOCTN VARCHAR(50), --- first character may be numeric - matches to TIENDA (1-4)
USCATVAL NVARCHAR(255), --- equal to USCATVLS_3 (Departamento)
USCATNUM INT, --- Either always 3 or not used, Diferences the type of category in the table iv40600, if 3 then is Department, if 2 then division, etc.
Image_Url NVARCHAR(100), --- http://www.anywhere.com/bla-@DESC_DEPARTAMENTO , we use this field as the description of the category. i.e. Vegetables Area, Tools, Electronics , etcs
POPRCTNM VARCHAR(50), --- not used Price of Purchase Reciept number
Landed_Cost_Id NVARCHAR(100), --- Sometimes %FLET% -if is FLET then is SHIPMENT charge
Orig_TotalLandedCostAmt NUMERIC(14,2) --- Cost - the cost of the shipment
)
i provide the create code of the tables u requested.
Departments, Subdepartments, Divisions, etc, categorys in this table.
/****** Objeto: Table [dbo].[IV40600] Fecha de la secuencia de comandos: 04/03/2009 15:05:05 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[IV40600](
[USCATVAL] [char](11) NOT NULL,
[USCATNUM] [smallint] NOT NULL,
[Image_URL] [char](255) NOT NULL,
[UserCatLongDescr] [char](255) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKIV40600] PRIMARY KEY NONCLUSTERED
(
[USCATNUM] ASC,
[USCATVAL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Item Master table
/****** Objeto: Table [dbo].[IV00101] Fecha de la secuencia de comandos: 04/03/2009 15:05:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[IV00101](
[ITEMNMBR] [char](31) NOT NULL,
[ITEMDESC] [char](101) NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[ITMSHNAM] [char](15) NOT NULL,
[ITEMTYPE] [smallint] NOT NULL,
[ITMGEDSC] [char](11) NOT NULL,
[STNDCOST] [numeric](19, 5) NOT NULL,
[CURRCOST] [numeric](19, 5) NOT NULL,
[ITEMSHWT] [int] NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[ITMTSHID] [char](15) NOT NULL,
[TAXOPTNS] [smallint] NOT NULL,
[IVIVINDX] [int] NOT NULL,
[IVIVOFIX] [int] NOT NULL,
[IVCOGSIX] [int] NOT NULL,
[IVSLSIDX] [int] NOT NULL,
[IVSLDSIX] [int] NOT NULL,
[IVSLRNIX] [int] NOT NULL,
[IVINUSIX] [int] NOT NULL,
[IVINSVIX] [int] NOT NULL,
[IVDMGIDX] [int] NOT NULL,
[IVVARIDX] [int] NOT NULL,
[DPSHPIDX] [int] NOT NULL,
[PURPVIDX] [int] NOT NULL,
[UPPVIDX] [int] NOT NULL,
[IVRETIDX] [int] NOT NULL,
[ASMVRIDX] [int] NOT NULL,
[ITMCLSCD] [char](11) NOT NULL,
[ITMTRKOP] [smallint] NOT NULL,
[LOTTYPE] [char](11) NOT NULL,
[KPERHIST] [tinyint] NOT NULL,
[KPTRXHST] [tinyint] NOT NULL,
[KPCALHST] [tinyint] NOT NULL,
[KPDSTHST] [tinyint] NOT NULL,
[ALWBKORD] [tinyint] NOT NULL,
[VCTNMTHD] [smallint] NOT NULL,
[UOMSCHDL] [char](11) NOT NULL,
[ALTITEM1] [char](31) NOT NULL,
[ALTITEM2] [char](31) NOT NULL,
[USCATVLS_1] [char](11) NOT NULL,
[USCATVLS_2] [char](11) NOT NULL,
[USCATVLS_3] [char](11) NOT NULL,
[USCATVLS_4] [char](11) NOT NULL,
[USCATVLS_5] [char](11) NOT NULL,
[USCATVLS_6] [char](11) NOT NULL,
[MSTRCDTY] [smallint] NOT NULL,
[MODIFDT] [datetime] NOT NULL,
[CREATDDT] [datetime] NOT NULL,
[WRNTYDYS] [smallint] NOT NULL,
[PRCLEVEL] [char](11) NOT NULL,
[LOCNCODE] [char](11) NOT NULL,
[PINFLIDX] [int] NOT NULL,
[PURMCIDX] [int] NOT NULL,
[IVINFIDX] [int] NOT NULL,
[INVMCIDX] [int] NOT NULL,
[CGSINFLX] [int] NOT NULL,
[CGSMCIDX] [int] NOT NULL,
[ITEMCODE] [char](15) NOT NULL,
[TCC] [char](31) NOT NULL,
[PriceGroup] [char](11) NOT NULL,
[PRICMTHD] [smallint] NOT NULL,
[PRCHSUOM] [char](9) NOT NULL,
[SELNGUOM] [char](9) NOT NULL,
[KTACCTSR] [smallint] NOT NULL,
[LASTGENSN] [char](21) NOT NULL,
[ABCCODE] [smallint] NOT NULL,
[Revalue_Inventory] [tinyint] NOT NULL,
[Tolerance_Percentage] [int] NOT NULL,
[Purchase_Item_Tax_Schedu] [char](15) NOT NULL,
[Purchase_Tax_Options] [smallint] NOT NULL,
[ITMPLNNNGTYP] [smallint] NOT NULL,
[STTSTCLVLPRCNTG] [smallint] NOT NULL,
[CNTRYORGN] [char](7) NOT NULL,
[INACTIVE] [tinyint] NOT NULL,
[MINSHELF1] [smallint] NOT NULL,
[MINSHELF2] [smallint] NOT NULL,
[INCLUDEINDP] [tinyint] NOT NULL,
[LOTEXPWARN] [tinyint] NOT NULL,
[LOTEXPWARNDAYS] [smallint] NOT NULL,
[DEX_ROW_TS] [datetime] NOT NULL CONSTRAINT [DF__IV00101__DEX_ROW__6B05EC12] DEFAULT (getutcdate()),
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKIV00101] PRIMARY KEY NONCLUSTERED
(
[ITEMNMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[IV00101] WITH CHECK ADD CHECK ((datepart(hour,[CREATDDT])=(0) AND datepart(minute,[CREATDDT])=(0) AND datepart(second,[CREATDDT])=(0) AND datepart(millisecond,[CREATDDT])=(0)))
GO
ALTER TABLE [dbo].[IV00101] WITH CHECK ADD CHECK ((datepart(hour,[MODIFDT])=(0) AND datepart(minute,[MODIFDT])=(0) AND datepart(second,[MODIFDT])=(0) AND datepart(millisecond,[MODIFDT])=(0)))
Transaction history table
/****** Objeto: Table [dbo].[IV30300] Fecha de la secuencia de comandos: 04/03/2009 15:07:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[IV30300](
[TRXSORCE] [char](13) NOT NULL,
[DOCTYPE] [smallint] NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[HSTMODUL] [char](3) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[LNSEQNBR] [numeric](19, 5) NOT NULL,
[UOFM] [char](9) NOT NULL,
[TRXQTY] [numeric](19, 5) NOT NULL,
[UNITCOST] [numeric](19, 5) NOT NULL,
[EXTDCOST] [numeric](19, 5) NOT NULL,
[TRXLOCTN] [char](11) NOT NULL,
[TRNSTLOC] [char](11) NOT NULL,
[TRFQTYTY] [smallint] NOT NULL,
[TRTQTYTY] [smallint] NOT NULL,
[IVIVINDX] [int] NOT NULL,
[IVIVOFIX] [int] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[DECPLQTY] [smallint] NOT NULL,
[QTYBSUOM] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKIV30300] PRIMARY KEY NONCLUSTERED
(
[DOCTYPE] ASC,
[DOCNUMBR] ASC,
[LNSEQNBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[IV30300] WITH CHECK ADD CHECK ((datepart(hour,[DOCDATE])=(0) AND datepart(minute,[DOCDATE])=(0) AND datepart(second,[DOCDATE])=(0) AND datepart(millisecond,[DOCDATE])=(0)))
Price Of Purchase
/****** Objeto: Table [dbo].[POP30700] Fecha de la secuencia de comandos: 04/03/2009 15:07:43 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[POP30700](
[POPRCTNM] [char](17) NOT NULL,
[RCPTLNNM] [int] NOT NULL,
[LCLINENUMBER] [int] NOT NULL,
[LCHDRNUMBER] [int] NOT NULL,
[Landed_Cost_ID] [char](15) NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[Long_Description] [char](51) NOT NULL,
[Landed_Cost_Type] [smallint] NOT NULL,
[VENDORID] [char](15) NOT NULL,
[Vendor_Note_Index] [numeric](19, 5) NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[Currency_Note_Index] [numeric](19, 5) NOT NULL,
[CURRNIDX] [smallint] NOT NULL,
[RATETPID] [char](15) NOT NULL,
[EXGTBLID] [char](15) NOT NULL,
[XCHGRATE] [numeric](19, 7) NOT NULL,
[EXCHDATE] [datetime] NOT NULL,
[TIME1] [datetime] NOT NULL,
[RATECALC] [smallint] NOT NULL,
[DENXRATE] [numeric](19, 7) NOT NULL,
[MCTRXSTT] [smallint] NOT NULL,
[DECPLCUR] [smallint] NOT NULL,
[ODECPLCU] [smallint] NOT NULL,
[ACPURIDX] [int] NOT NULL,
[DistRef] [char](31) NOT NULL,
[PURPVIDX] [int] NOT NULL,
[Invoice_Match] [tinyint] NOT NULL,
[CALCMTHD] [smallint] NOT NULL,
[Orig_Landed_Cost_Amount] [numeric](19, 5) NOT NULL,
[Calculation_Percentage] [int] NOT NULL,
[Total_Landed_Cost_Amount] [numeric](19, 5) NOT NULL,
[Orig_TotalLandedCostAmt] [numeric](19, 5) NOT NULL,
[Landed_Cost_Warnings] [smallint] NOT NULL,
[Apportion_By] [smallint] NOT NULL,
[Orig_UnapportionedAmount] [numeric](19, 5) NOT NULL,
[INVINDX] [int] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKPOP30700] PRIMARY KEY CLUSTERED
(
[POPRCTNM] ASC,
[RCPTLNNM] ASC,
[LCLINENUMBER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[POP30700] WITH CHECK ADD CHECK ((datepart(hour,[EXCHDATE])=(0) AND datepart(minute,[EXCHDATE])=(0) AND datepart(second,[EXCHDATE])=(0) AND datepart(millisecond,[EXCHDATE])=(0)))
GO
ALTER TABLE [dbo].[POP30700] WITH CHECK ADD CHECK ((datepart(day,[TIME1])=(1) AND datepart(month,[TIME1])=(1) AND datepart(year,[TIME1])=(1900)))
lemme get some test data
April 3, 2009 at 3:27 pm
Thanks! Some test data would be perfect!
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 3, 2009 at 4:46 pm
Im attaching an excel file with the 4 main tables of the process, and some data.
is that useful?, maybe was a wrong choice excel D:
--
Damn, i had some trouble uploading this last file on SSC.
April 4, 2009 at 4:33 am
Hi!
Thank you very much; other people can learn a lesson from you! It is nice to see that somebody really wants help and make his own effort!
It’s almost done! I don’t speak Spanish so, there are only some questions:
The View
Should the combination of DEPARTAMENTO and DIVISION be unique? Are they unique within one month?
If the combination of DEPARTAMENTO and DIVISION isn’t unique, where can I find any uniqueness within the view? If I have a look to your view the only assured uniqueness seems to be the primary key of IV30300 (combination of DOCTYPE, DOCNUMBR and LNSEQNBR) or its DEX_ROW_ID.
Uniqueness is necessary for every table or view ;-).
The Procedure
It seems that there are two different business cases within the procedure:
* Once the detailed information about all DEPARTEMENTO and DIVISION
* Once the aggregation of this information
Usually you should split this into two procedures. If you use one procedure for two different handlings you may get performance problems because there are two different execution plans for SQL Server. If you call the procedure and it uses the wrong execution plan the performance might be really bad.
Your cursor selects all DEPARTAMENTO and DIVISION records of your view but in your loop; without any criteria. If there are duplicates you do the same calculation several times. Should this be done grouped?
What should be the difference between the detailed result and the totals? You also use aggregations (SUM) to determine the data of the details table.
* Shall the detail table return one record for each DEPARTAMENTO/DIVISION the specified month?
* Shall the detail table return one record for each DEPARTAMENTO and DIVISION independent of available billing data for the current month?
* The uniqueness... You use the DEPARTAMENTO and the DIVISION to get the billing INFORMATION from your view. If this combination is not unique for the current month you will calculate the same several times and get duplicate results.
* Shall the totals table show the DEPARTAMENTO/DIVISION totals for the current month or for all records within the view?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 4, 2009 at 7:44 pm
Thanks a lot for ur time too Flo, guess the last thing i can do when someone is helping, is give some information =D
Answering your question.
The main purpose of the whole procedurs is, i need the billing information per store in our ERP, in this case @TIENDA, i need to show the billing information in certain Shop(@Tienda), Year, month, and for each DEPARTMENT, every department is a part of a division, thats why i use Department and division to get the billling info.
"
The Procedure
It seems that there are two different business cases within the procedure:
* Once the detailed information about all DEPARTEMENTO and DIVISION
* Once the aggregation of this information
Since i call this procedure to get a recordset with the information calculated already. i call it 3 times, every time with a different @TIENDA parameter (1, 2, 3) and when is 4 it means that i need the recordset of the TOTALS table thats why all the aggregation. i Format all the info directly to the tables in SQL, so i just use a COPYfromRECORDSET in excel and paste all the info.
i show 4 reports, 3 (1 for each store), and another for the DETAILED totals, that is the fourth report
Guess youre right i can do this by calling a different procedure,guess i started coding before tinking a good plan as u say. CLOSED MIND XD
Shall the detail table return one record for each DEPARTAMENTO/DIVISION the specified month?
YES
Shall the detail table return one record for each DEPARTAMENTO and DIVISION independent of available billing data for the current month?
Yes i need to show all divisions departments even if no billing data involve em
* The uniqueness... You use the DEPARTAMENTO and the DIVISION to get the billing INFORMATION from your view. If this combination is not unique for the current month you will calculate the same several times and get duplicate results.
There can be a same department for a different division thats why i use them to get the info.
Shall the totals table show the DEPARTAMENTO/DIVISION totals for the current month or for all records within the view?
The totals should be just for the selected month and year on all the Stores(@Tienda) showed by department/division
Hope i answered all your questions 😉 , and i have been implementing some tips that i have learned from this topic in another procedurees, all of em old ones, like ommiting the functions on the where clauses, and they have been getting better =)
Txs for the time!
April 5, 2009 at 10:22 am
Hi Zen
Here we go... 😎
First, my final interpretation of your procedure:
If @TIENDA is 1, 2 or 3 the returned data are always the same.
If @TIENDA is 1 you also fill your totals table with the initial data
If @TIENDA is 2 or 3 the procedure will do exectly the same except the criterion for TRXLOCTN
If @TIENDA is 4 the cursor does not manipulate any data in TEMP_DET_ALM_POR_TIENDA. You only calculate the final totals for TEMP_TOT_DET_ALM_POR_TIENDA
If not yet exists, there should critical be an index on table iv30300 column DOCDATE!
I usually comment everything in English (no I'm not from an English speaking country) it's more simple to share/discuss information with any people.
I added an example describing header as I use it. Feel free to remove/translate/change it.
I also added some comments. Also feel free to remove/translate/change it. If you have any question about any part of the procedure feel free to ask!
Since I changed the handling of the totals and details handling within the procedure the execution plan should also be the same. So you don't need a second procedure any more.
Important to understand :w00t:
I changed the procedure from row based acting (cursor) to a set based solution. We don't change values within one field in one row; we change one (or more) column(s) in all objects.
This means that we don't use IF-clauses for any checks but CASE-clauses. CASE is equal to IF but it works within a statement. Looks a bit different but is the same. 🙂
You should search for RBAR articles on this side. There are some very good from Jeff Moden and some other people. In some days there will also come a new article by RBarryYoung about cursors. Should be really interesting!
I tested with 11,000 test data and 1,000 results for one month. Each execution (@TALIDA 1, 2, 3 or 4) took about 350 milliseconds.
So here is the procedure:
Edited: Seems that the forum formatting does not work correctly for some parts of the procedure. Copy to Management Studio for a better formatting..
-- DROP PROCEDURE PROC_DET_ALMACEN_POR_TIENDA
CREATE PROCEDURE PROC_DET_ALMACEN_POR_TIENDA
@MES nvarchar(2),
@ANIO nchar(4),
@TIENDA INT
AS
/**********************************************************************************
Author
======
Zen Rigar
Summary
=======
Financial calculation for departments and divisions depending on specified month and year.
Parameters
==========
@MES
The moth for calculation
@ANIO
The year for calculation
@TIENDA
The calculation mode.
If 1, 2 or 3 the criterion will be used as criterion for the first character or TRXLOCTN column.
If 4 all data with first character 1, 2 or 3 in TRXLOCTN will be used.
Remarks
=======
TO BE DEFINED :)
Version
=======
* V01.00.00.00 (2009-01-01)
Initial version based on cursor
* V01.01.00.00 (2009-03-05)
Redesigned to set based work
**********************************************************************************/
-- Comment the CREATE part and un-comment the lines within test configuration to test
-- the procedure without always recreating and calling of the procedure
-- ////////////////////////////////////////
-- --> Test configuration
--DECLARE @MES NVARCHAR(2)
--DECLARE @ANIO NVARCHAR(4)
--DECLARE @TIENDA INT
--SELECT @MES = '2', @ANIO = '2009', @TIENDA = 2
-- <-- Test configuration
-- ////////////////////////////////////////
-- Avoid row count information
SET NOCOUNT ON
--------------------
-- Get date ranges
-- We use a StartDate and an EndDate instead of YEAR() and MONTH() to ensure index usage
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @DateString VARCHAR(30)
-- Get first day of month (American formatting mm/dd/yyyy)
SELECT @DateString = @MES + '/01/' + @ANIO
-- Get StartDate
SELECT @StartDate = CONVERT(DATETIME, @DateString, 101)
-- Get EndDate just by adding one month
SELECT @EndDate = DATEADD(MONTH, 1, @StartDate)
--------------------
-- Tienda to char
-- Get Tienda as char to optimize query execution and avoid conversion of
-- first character of TRXLOCTN to INT
DECLARE @TiendaChar NCHAR(1)
SELECT @TiendaChar = CONVERT(NCHAR(1), @TIENDA)
--------------------
-- Variable table for row based results instead of a real table.
-- I think the TEMP_DET_ALM_POR_TIENDA was never used outside of the
-- procedure so I removed it from here and replaced by a variable table
-- This keeps the database clean and avoids transaction logging of
-- temporary data
DECLARE @TEMP_DET_ALM_POR_TIENDA TABLE
(
DEPARTAMENTO_ORIGINAL NVARCHAR(255),
DIVISION NVARCHAR(11),
DEPARTAMENTO NVARCHAR(255),
INV_INICIAL NUMERIC(14,2),
COMPRAS NUMERIC(14,2),
GASTOS_DE_COMPRA NUMERIC(14,2),
DEVOL_COMPRAS NUMERIC(14,2),
DIF_COMPRAS NUMERIC(14,2),
COMPRAS_NETAS NUMERIC(14,2),
TRANSF_ENTRADA NUMERIC(14,0),
TRANSF_SALIDA NUMERIC(14,0),
INV_DISPONIBLE NUMERIC(14,2),
INV_FINAL NUMERIC(14,2),
COSTO_VENTAS NUMERIC(14,2),
ROTACION NUMERIC(14,2),
VECES NUMERIC(8,2),
PRIMARY KEY CLUSTERED (DEPARTAMENTO_ORIGINAL, DIVISION)
)
-- INSERT into temporary table all DEPARTEMENTO/DIVISION information in one step.
-- Avoid the cursor usage if possible ( it is almost always possible ;) ).
--
-- The only criterion for this is USCATNUM = 3 to get all departments and divisions independent
-- on any data for current month.
--
-- ATTENTION: I came from fact that Image_Url is always same for DEPARTAMENTO;
-- if not you may get a PRIMARY KEY violation. Please tell me if.
INSERT INTO @TEMP_DET_ALM_POR_TIENDA (
DEPARTAMENTO_ORIGINAL, -- Hold original DEPARTAMENTO (just needed inside the procedure)
DIVISION, -- DIVISION
DEPARTAMENTO, -- DEPARTAMENTO from Image_Url
DEVOL_COMPRAS, -- Currently always zero
DIF_COMPRAS -- Currently always zero
)
SELECT
USCATVLS_3,
USCATVLS_2,
RTRIM(SUBSTRING(Image_URL, CHARINDEX('-', Image_URL) + 1, 255)) DEPARTAMENTO_URL,
0,
0
FROM VW_DET_ALM_POR_TIENDA
WHERE USCATNUM = 3
-- Group to avoid duplicate entries
GROUP BY
USCATVLS_3,
USCATVLS_2,
RTRIM(SUBSTRING(Image_URL, CHARINDEX('-', Image_URL) + 1, 255))
-- Get ALL cost information from view within one CTE (no select for each row and each type of financial data
-- A CTE is a inline table function. For more information have a look to BOL if you don't know them.
; WITH
AllCost (DEPARTAMENTO, DIVISION, INV_INICIAL, COMPRAS, GASTOS_DE_COMPRA, TRANSF_ENTRADA, TRANSF_SALIDA) AS
(
SELECT
USCATVLS_3, -- Departemento
USCATVLS_2, -- Division
EXTDCOST INV_INICIAL, -- Inv_Inicial
-- Get Compras depending on RB within DocNumbr. If RB is always at start of DOCNUMBR
-- you should replace "LIKE '%RB%'" with "CHARINDEX('RB', DOCNUMBR) = 1" for better performance
-- In your procedure Comprase is equal to COMPRAS_NETAS
CASE WHEN DOCNUMBR LIKE '%RB%' THEN EXTDCOST ELSE 0 END COMPRAS,
-- Get Castos_De_Compra depending on FLET within Landed_Cost_Id. If the value is always exactly
-- "FLETE" you should replace this either with an equal check. If the FLET is always at start
-- of Landed_Cost_Id you should use CHARINDEX.
CASE WHEN Landed_Cost_Id LIKE '%FLET%' THEN EXTDCOST ELSE 0 END GASTOS_DE_COMPRA,
-- Get Transf_Entrada depending on positive ExtDCost
CASE WHEN EXTDCOST > 0 THEN EXTDCOST ELSE 0 END TRANSF_ENTRADA,
-- Get Transf_Salida depending on negative ExtDCost
CASE WHEN EXTDCOST < 0 THEN EXTDCOST ELSE 0 END TRANSF_SALIDA
FROM VW_DET_ALM_POR_TIENDA
-- Use the month criteria
WHERE DOCDATE >= @StartDate AND DOCDATE < @EndDate
AND ( -- AND ->
(
-- If @TIENDA is 1, 2 or 3 use it as criterion for the TRXLOCTN
@TiendaChar IN ('1', '2', '3') AND LEFT(TRXLOCTN, 1) IN (@TiendaChar)
)
OR
(
-- If @TIENDA is 4; TRXLOCTN has to be 1, 2 or 3 to get the totals
@TiendaChar IN ('4') AND LEFT(TRXLOCTN, 1) IN ('1', '2', '3')
)
) -- <- AND
)
UPDATE TOP(100) dest SET
INV_INICIAL = ac.INV_INICIAL,
COMPRAS = ac.COMPRAS,
COMPRAS_NETAS = ac.COMPRAS,
GASTOS_DE_COMPRA = ac.GASTOS_DE_COMPRA,
TRANSF_ENTRADA = ac.TRANSF_ENTRADA,
TRANSF_SALIDA = ac.TRANSF_SALIDA,
INV_DISPONIBLE = ac.INV_INICIAL + ac.COMPRAS,
INV_FINAL = ac.INV_INICIAL + ac.TRANSF_ENTRADA - ac.TRANSF_SALIDA,
COSTO_VENTAS = ac.INV_INICIAL + ac.COMPRAS - ac.INV_INICIAL + ac.TRANSF_ENTRADA - ac.TRANSF_SALIDA
-- We want to update all data within our temp table
FROM @TEMP_DET_ALM_POR_TIENDA dest
-- Cross apply can be used to call inline functions (like a CTE) with criteria from out side.
-- The resulting data will directly be JOINed with the current row
CROSS APPLY (SELECT SUM(ISNULL(INV_INICIAL, 0)) INV_INICIAL,
SUM(ISNULL(COMPRAS, 0)) COMPRAS,
SUM(ISNULL(GASTOS_DE_COMPRA, 0)) GASTOS_DE_COMPRA,
SUM(ISNULL(TRANSF_ENTRADA, 0)) TRANSF_ENTRADA,
SUM(ISNULL(TRANSF_SALIDA, 0)) TRANSF_SALIDA
FROM AllCost
-- Here we have the JOIN criteria from outside temp table to the CTE
WHERE DEPARTAMENTO = dest.DEPARTAMENTO_ORIGINAL AND DIVISION = dest.DIVISION) ac
-- Calculate ROTACION and VECES. This might also be possible within the first update
-- but I think for better readability the performance loss is legitimated
UPDATE @TEMP_DET_ALM_POR_TIENDA SET
ROTACION = CASE
-- Avoid division by zero
WHEN INV_FINAL <> 0
AND COSTO_VENTAS / INV_FINAL <> 0
-- (Days of month) / (Costo_Ventas / Inv_Final)
THEN DATEDIFF(DAY, @StartDate, @EndDate) / (COSTO_VENTAS / INV_FINAL)
ELSE 0
END,
VECES = CASE
-- Avoid division by zero
WHEN INV_FINAL <> 0
AND COSTO_VENTAS / INV_FINAL <> 0
AND DATEDIFF(DAY, @StartDate, @EndDate) / (COSTO_VENTAS / INV_FINAL) <> 0
-- (Days of year) / (Days of month) / (Costo_Ventas / Inv_Final)
--
-- Explanation for DATEADD(YEAR, DATEDIFF(YEAR, 0, @StartDate), 0):
-- Use the StartDate and get the difference from 0 (1900-01-01) in years
-- Use this value and add it to 0 (1900-01-01) so we have the first day of current year
THEN DATEDIFF(DAY,
DATEADD(YEAR, DATEDIFF(YEAR, 0, @StartDate), 0),
DATEADD(YEAR, DATEDIFF(YEAR, 0, @StartDate) + 1, 0))
/
DATEDIFF(DAY, @StartDate, @EndDate) / (COSTO_VENTAS / INV_FINAL)
ELSE 0
END
IF (@TIENDA = 4)
BEGIN
-- Get the totals row by getting the sum information from our temp table
INSERT INTO @TEMP_DET_ALM_POR_TIENDA (
DEPARTAMENTO_ORIGINAL,
DIVISION,
DEPARTAMENTO,
INV_INICIAL,
COMPRAS,
GASTOS_DE_COMPRA,
DEVOL_COMPRAS,
DIF_COMPRAS,
COMPRAS_NETAS,
TRANSF_ENTRADA,
TRANSF_SALIDA,
INV_DISPONIBLE,
INV_FINAL,
COSTO_VENTAS,
ROTACION,
VECES
)
SELECT
'',
'',
'TOTALES',
SUM(ISNULL(INV_INICIAL, 0)),
SUM(ISNULL(COMPRAS, 0)),
SUM(ISNULL(GASTOS_DE_COMPRA, 0)),
SUM(ISNULL(DEVOL_COMPRAS, 0)),
SUM(ISNULL(DIF_COMPRAS, 0)),
SUM(ISNULL(COMPRAS_NETAS, 0)),
SUM(ISNULL(TRANSF_ENTRADA, 0)),
SUM(ISNULL(TRANSF_SALIDA, 0)),
SUM(ISNULL(INV_DISPONIBLE, 0)),
SUM(ISNULL(INV_FINAL, 0)),
SUM(ISNULL(COSTO_VENTAS, 0)),
SUM(ISNULL(ROTACION, 0)),
SUM(ISNULL(VECES, 0))
FROM @TEMP_DET_ALM_POR_TIENDA
END
-- Return the result data from our temp table
SELECT
DIVISION,
DEPARTAMENTO,
INV_INICIAL,
COMPRAS,
GASTOS_DE_COMPRA,
DEVOL_COMPRAS,
DIF_COMPRAS,
COMPRAS_NETAS,
TRANSF_ENTRADA,
TRANSF_SALIDA,
INV_DISPONIBLE,
INV_FINAL,
COSTO_VENTAS,
ROTACION,
VECES
FROM @TEMP_DET_ALM_POR_TIENDA
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 6, 2009 at 9:47 am
:w00t:
Dude now that im in the work PC, im testing the procedure, and i dont know if i should feel bad or good D:
I still got a lot to learn XD, i have been checking a lot of definitions of functions of sql that i had never tried even readed before XD.
i still need to dominate basic stuff like the group by before jumping to stuff like that XD.
but im amazed about the reduction of code and execution time u made on ur procedure D: ,
your procedure works, but guess i have a lot of homework in understand it at 100% 😀
i get confused by stuff like the cross apply, im chekcing that at the moment.
txs a lot for your time and help :-):-):-)
April 6, 2009 at 10:32 am
Hi Zen
Glad to help you!
Just because I'm pry, is the performance better than before?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 6, 2009 at 10:47 am
ahahahah
Hell yeah, for a simple execution for a single Store my old proc takes 14 seconds,
Your Proc takes 2 seconds ¬¬, plus less code XD.
excellent improvment 😀
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply