Bases de Datos > SQL Server
Sugerencia Procedure Cantidad de Stock por Mes
(1/1)
jose#:
Hola amigo les comento que realizo un sitema para farmacia el caso es que realize un procedure que me permite ver la cantidad de item por mes pero para cada mes tengo un select, lo que necesito es que el procedimiento genere un solo select por ejemplo select Cod_Articulo, Cantidad_Enero, Cantidad_Febrero... si pudieran darme una sugerencia o una clausula que me pueda ayudar, de antemano muchisimas gracias.
este es el procedure y perdonen por la saturacion.
--- Citar ---
ALTER PROCEDURE [dbo].[PR_MOSTRAREGRESOSXMESDELAÑO](@FECHA DATETIME)
AS
BEGIN
-- PARA ENERO
SELECT dbo.ARTICULO.COD_ARTICULO as 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) AS 'Egreso Enero'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '1' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- PARA FEBRERO
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) AS 'Egreso Febrero'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '2' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- PARA MARZO
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Marzo'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '3' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- ABRIL
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Abril'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '4' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- MAYO
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Mayo'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '5' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- JUNIO
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Junio'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '6' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- JULIO
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Julio'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '7' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- AGOSTO
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Agosto'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '8' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- SEPTIEMBRE
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Septiembre'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '9' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- OCTUBRE
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Octubre'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '10' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- NOVIEMBRE
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Noviembre'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '11' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
-- DICIEMBRE
SELECT dbo.ARTICULO.COD_ARTICULO AS 'CODIGO ITEM', dbo.ARTICULO.DESC_ARTICULO AS 'NOMBRE ITEM', dbo.ARTICULO.FORM_FARMACEUTICA AS 'FORM FARMACEUTICA', SUM(dbo.DETALLE_BOLETA.CANTIDAD) as 'Egreso Diciembre'
FROM dbo.ARTICULO INNER JOIN
dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO
WHERE MONTH(DETALLE_BOLETA.FECHA_EGRESO) = '12' AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = YEAR(@FECHA)
GROUP BY ARTICULO.COD_aRTICULO, ARTICULO.DESC_ARTICULO, ARTICULO.FORM_FARMACEUTICA ORDER BY ARTICULO.COD_aRTICULO ASC
END
--- Fin de la cita ---
Shiquilla:
Si tienes SQL Server 2005 o superior podrías mirar la sentencia PIVOT que hace lo que quieres.
Si tienes SQL Server 2000 podrías crearte una tabla temporal que contenga lo que quieres devolver y hacer una UPDATE sobre la tabla, por ejemplo:
--- Código: SQL ---DECLARE @nAño INTEGER; SET @nAño = 2005DECLARE @lnI INTEGERDECLARE @nTotal INTEGERDECLARE @sSQL VARCHAR(8000)DECLARE @dFechaINI DATETIME; SET @dFechaINI = CONVERT(DATETIME, '01/01/' + LTRIM(@nAño), 103)DECLARE @dFechaFIN DATETIME; SET @dFechaFIN = CONVERT(DATETIME, '31/12/' + LTRIM(@nAño), 103) -- ----------------------------------------------------------------------------------------------- Tabla de meses y años y su orden-- Es necesaria para poder realizar las updates masivas en el orden específico-- ---------------------------------------------------------------------------------------------CREATE TABLE #Dias_Meses( nOrden NUMERIC(18,0) IDENTITY(1,1) NOT NULL, dFecha DATETIME NOT NULL DEFAULT 0, nMes INTEGER NOT NULL DEFAULT 0, nAño INTEGER NOT NULL DEFAULT 0 PRIMARY KEY (nOrden)) -- En este caso, crea un TOP 12SET @sSQL = 'INSERT INTO #Dias_Meses (nAño) SELECT TOP ' + LTRIM(DATEDIFF(MONTH, @dFechaINI, @dFechaFIN) + 1) + ' 0 FROM SYSOBJECTS A CROSS JOIN SYSCOLUMNS B'EXEC(@sSQL) UPDATE #Dias_Meses SET dFecha = DATEADD(MONTH, nOrden -1, '01/' + RIGHT('0' + LTRIM(MONTH(@dFechaINI)),2) + '/' + LTRIM(YEAR(@dFechaINI)))UPDATE #Dias_Meses SET nMes = MONTH(dFecha), nAño = YEAR(dFecha) UPDATE #Dias_Meses SET dFecha = @dFechaINI WHERE nOrden = 1-- --------------------------------------------------------------------------------------------- -- Creas la tabla con los datos fijos que vas a devolverCREATE TABLE #Datos ( sCodArticulo VARCHAR(250) NOT NULL PRIMARY KEY) -- Añadimos las columnas necesarias a la tabla #Datos-- ---------------------------------------------------------------------------------------------SET @sSQL = ''SELECT @sSQL = @sSQL + CASE WHEN LEN(@sSQL) > 0 THEN ', ' ELSE '' END + 'nCant_' + LTRIM(nOrden) + ' NUMERIC(18,2) NOT NULL DEFAULT 0' FROM #Dias_Meses ORDER BY nOrdenSET @sSQL = 'ALTER TABLE #Datos ADD ' + @sSQLPRINT @sSQLEXEC(@sSQL)-- --------------------------------------------------------------------------------------------- -- Hace la INSERT en la tabla #Datos poniendo todos los artículos que entran en el filtro-- ---------------------------------------------------------------------------------------------INSERT INTO #Datos (sCodArticulo) SELECT dbo.ARTICULO.COD_ARTICULOFROM dbo.ARTICULO INNER JOIN dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULOWHERE DETALLE_BOLETA.FECHA_EGRESO BETWEEN @dFechaINI AND @dFechaFINGROUP BY dbo.ARTICULO.COD_ARTICULO-- --------------------------------------------------------------------------------------------- -- Ahora, hacemos la UPDATE apoyandonos sobre la tabla #Dias_Meses-- ---------------------------------------------------------------------------------------------SET @nTotal = DATEDIFF(MONTH, @dFechaINI, @dFechaFIN) + 1SET @lnI = 1WHILE @lnI <= @nTotalBEGIN SET @sSQL = 'UPDATE A SET A.nCant_' + LTRIM(@lnI) + ' = ISNULL(B.nTotal,0)' SET @sSQL = @sSQL + ' FROM #Datos A' SET @sSQL = @sSQL + ' INNER JOIN ( SELECT dbo.ARTICULO.COD_ARTICULO, SUM(dbo.DETALLE_BOLETA.CANTIDAD) AS nTotal FROM dbo.ARTICULO INNER JOIN dbo.DETALLE_BOLETA ON dbo.ARTICULO.COD_ARTICULO = dbo.DETALLE_BOLETA.COD_ARTICULO' SET @sSQL = @sSQL + ' INNER JOIN #Dias_Meses DM ON MONTH(DETALLE_BOLETA.FECHA_EGRESO) = DM.nMes AND YEAR(DETALLE_BOLETA.FECHA_EGRESO) = DM.nAño' SET @sSQL = @sSQL + ' WHERE DETALLE_BOLETA.FECHA_EGRESO BETWEEN ''' + LTRIM(@dFechaINI) + ''' AND ''' + LTRIM(@dFechaFIN) + '''' SET @sSQL = @sSQL + ' AND DM.nOrden = ' + LTRIM(@lnI) SET @sSQL = @sSQL + ' GROUP BY dbo.ARTICULO.COD_ARTICULO) B ON A.sCodArticulo = B.COD_ARTICULO' EXEC(@sSQL) SET @lnI = @lnI + 1END-- --------------------------------------------------------------------------------------------- SELECT * FROM #Datos ORDER BY sCodArticulo DROP TABLE #DatosDROP TABLE #Dias_Meses
Sería hacer algo parecido, en este caso es dinámico y da igual el filtro que se haga.
Espero que te sirva, saludos.
Nota: No lo he probado
jose#:
Muchas gracias Shiquilla utilizo sql server 2005, investigue algo sobre tablas temporale y segun algunos articulos relentiza y ocupa mucha memoria, me parece mas adecuado la sentencia PIVOT francamente nunca escuche de esta sentencia pero investigarè y lo probarè te cuento luego.
Gracias...
jose#:
Lo logre esto es con pivot
--- Citar ---
SELECT ITEM,[1] ENE,[2] FEB, [3] MAR, [4] ABR,[5] MAY,[6] JUN,[7] JUL,[8] AGO,[9] SEP,[10] OCT, [11] NOV,[12] DIC
FROM (
SELECT D.COD_ARTICULO AS ITEM,A.DESC_ARTICULO AS NOMBRE, MONTH(D.FECHA_EGRESO) AS MES, D.CANTIDAD AS CANTIDAD
FROM DETALLE_BOLETA AS D INNER JOIN ARTICULO A ON D.COD_ARTICULO = A.COD_ARTICULO
WHERE YEAR(D.FECHA_EGRESO) = '2009'
) V PIVOT (SUM(CANTIDAD) FOR MES IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PT
--- Fin de la cita ---
y este con Case
--- Citar ---ç
CREATE PROCEDURE PR_REPORTE_SUMA_TOTALES (@FECHA DATETIME)
AS
BEGIN
SELECT
[CODIGO] = CASE WHEN ROW IS NULL THEN 'SUMA'
ELSE CONVERT(VARCHAR(80),[ROW]) END,
--[NOMBRE] = ROW2,
[ENERO] = SUM(CASE COL WHEN '1' THEN DATA ELSE 0 END ),
[FEBRERO] = SUM(CASE COL WHEN '2' THEN DATA ELSE 0 END ),
[MARZO] = SUM(CASE COL WHEN '3' THEN DATA ELSE 0 END ),
[ABRIL] = SUM(CASE COL WHEN '4' THEN DATA ELSE 0 END ),
[MAYO] = SUM(CASE COL WHEN '5' THEN DATA ELSE 0 END ),
[JUNIO] = SUM(CASE COL WHEN '6' THEN DATA ELSE 0 END ),
[JULIO] = SUM(CASE COL WHEN '7' THEN DATA ELSE 0 END ),
[AGOSTO] = SUM (CASE COL WHEN '8' THEN DATA ELSE 0 END ),
[JULIO] = SUM(CASE COL WHEN '9' THEN DATA ELSE 0 END),
[SEPTIEMBRE] = SUM(CASE COL WHEN '10' THEN DATA ELSE 0 END),
[NOVIEMBRE] = SUM(CASE COL WHEN '11' THEN DATA ELSE 0 END),
[DICIEMBRE] = SUM(CASE COL WHEN '12' THEN DATA ELSE 0 END),
[TOTAL] = SUM(DATA)
FROM
(SELECT [ROW] = (DETALLE_BOLETA.COD_ARTICULO),
-- [ROW2] = ARTICULO.DESC_ARTICULO,
[COL]=MONTH(FECHA_EGRESO),
[DATA] = SUM(DETALLE_BOLETA.CANTIDAD)
FROM DETALLE_BOLETA INNER JOIN ARTICULO
ON (DETALLE_BOLETA.COD_ARTICULO = ARTICULO.COD_ARTICULO)
WHERE YEAR(DETALLE_BOLETA.FECHA_EGRESO) =YEAR(@FECHA)
GROUP BY DETALLE_BOLETA.COD_ARTICULO,MONTH(FECHA_EGRESO)
) F
GROUP BY ROW WITH ROLLUP
ORDER BY GROUPING(ROW) ASC
END
--- Fin de la cita ---
Me da totales y la suma de cada columna
Muchas gracias hasta la proxima.
Navegación
Ir a la versión completa