consulta para resumen mensual

[PacoPepe]

Tengo una tabla de Recepcion que recoge el material que entra a diario.
Necesito hacer un informe resumen que contenga las entradas desde el dia 1 hasta el dia 31 de cada mes, de tal forma que ponga la suma de las entradas de material de cada dia y 0 los dias que no entre nada (por ejemplo, los domingos)

Me he creado una tabla auxiliar llamada DIAS en la que incluyo 31 registros, desde el 1 hasta el 31

Hago la siguiente consulta (la he simplificado un poco para que aparezcan sólo los datos para el mes 12)

Código SQL [-]
select d.dia, rm.suma from dias d
left join
(select extract(day from fecha) as dia, extract(month from fecha) as mes, sum(kgs) as suma
           from RECEPCION
where ano = 2010
group by dia, mes ) as rm  on d.iddia = rm.dia
where rm.mes = 12

Y el resultado que obtengo es sólo para los días en que he tenido Recepción de mercancia

[[oscarac]]

mm que tal si pruebas haciendo la consulta de otra manera

captutra los datos de la tabla auxiliar DIAS y relacionala mediante el letf join con la tabla Recepcion

Código SQL [-]
 
Select Dia.Dia, R.Sum
from Dias
Left Join Recepcion R on Dia.DIA = Recepcion.DIA
Where mont(DIA.DIA) = 12

OJO... he colocado campos... que no tienen relacion directa con tu ejemplo...
prueba y nos comentas

[PacoPepe]

No acabo de entender lo que me dices.
Si entiendo bien lo que me dices es justo lo que he hecho pero no me funciona.

Ten en cuenta, que cada dia tengo muchas entradas, por tanto lo primero que tengo que hacer es sumar las entradas diarias. Por eso, del campo fecha extraigo el dia y el mes al que pertenece la entrada y lo sumo

Un saludo

[PacoPepe]

Además, lo que no entiendo es que si pongo left join, el resultado me debería incluir todos los regirstros de la tabla situada a la izquierda ¿o no?

Un saludo

[PacoPepe]

He probado lo siguiente

Código SQL [-]
select d.dia, sum(r.kgs) from dias d
left join recepcion r on d.iddia = extract(day from r.fecha)
where extract(month from r.fecha) = 12 and ano = 2010
group by d.dia

y el resultado es exactamente el mismo.

Por cierto la bbdd es Firebird 2.1

[[oscarac]]

A ver
acabo de hacer una prueba
he creado 2 tablas
1 llamada DIA que contiene la fechas del 1 al 31 y la otra llamada consumo
que contiene los kilos consumidos por dia

Código SQL [-]
Select D.Dia, Sum(C.Kilos) from Dias D
left join Consumo C on D.Dia = C.Dia
group by D.Dia

en la tabla consumo existe esta informacion
[quote]
consumo..........
Dia......................kilos
01/01/2011..........10
01/01/2011.........20
05/01/2011........6
cuando ejecuto el query lo que hace es...
de la tabla DIA mostrara todos los dias del 1 al 31 y tambien agrupara todas las sumas de la tabla consumo que sean del mismo dia

eso quiere decir que en el dia :
01/01/2011 acumulara 30
02/01/2011 acumulara 0
03/01/2011 acumulara 0
04/01/2011 acumulara 0
05/01/2011 acumulara 6
......
......
......

no es eso lo que quieres? que muestre o (Cero) cuando no haya consumo?

[[oscarac]]

select d.dia, sum(r.kgs) from dias dleft join recepcion r on d.iddia = extract(day from r.fecha)where extract(month from r.fecha) = 12 and ano = 2010group by d.dia

lo que haces aqui es sacar los dias de la tabla de consumo R.Fecha deberias sacarlo de la tabla de Dias D

[PacoPepe]

Lo que quiero hacer es exactamente lo que indicas en tu ejemplo.
¿Lo has conseguido?
¿Con que consulta?

[santi33a]

si lo que quieres es saber el total por día de un campo de una tabla creo que bastaría así y al final te da el total del mes, sin tener que crear ninguna tabla adicional, esto funciona con SQL Server, pero espero te de una idea si no funciona con FireBird
Suerte

Código SQL [-]

 select 
       sum(case day(fecha) when 1 then Kgs end) '1',
       sum(case day(fecha) when 2 then Kgs end) '2',  
       sum(case day(fecha) when 3 then Kgs end) '3',
       sum(case day(fecha) when 4 then Kgs end) '4',
       sum(case day(fecha) when 5 then Kgs end) '5',
       sum(case day(fecha) when 6 then Kgs end) '6',
       sum(case day(fecha) when 7 then Kgs end) '7',
       sum(case day(fecha) when 8 then Kgs end) '8',
       sum(case day(fecha) when 9 then Kgs end) '9',
       sum(case day(fecha) when 10 then Kgs end) '10',
       sum(case day(fecha) when 11 then Kgs end) '11',
       sum(case day(fecha) when 12 then Kgs end) '12',
       sum(case day(fecha) when 13 then Kgs end) '13',
       sum(case day(fecha) when 14 then Kgs end) '14',
       sum(case day(fecha) when 15 then Kgs end) '15',
       sum(case day(fecha) when 16 then Kgs end) '16',
       sum(case day(fecha) when 17 then Kgs end) '17',
       sum(case day(fecha) when 18 then Kgs end) '18',
       sum(case day(fecha) when 19 then Kgs end) '19',
       sum(case day(fecha) when 20 then Kgs end) '20',
       sum(case day(fecha) when 21 then Kgs end) '21',
       sum(case day(fecha) when 22 then Kgs end) '22',
       sum(case day(fecha) when 23 then Kgs end) '23',
       sum(case day(fecha) when 24 then Kgs end) '24',
       sum(case day(fecha) when 25 then Kgs end) '25',
       sum(case day(fecha) when 26 then Kgs end) '26',
       sum(case day(fecha) when 27 then Kgs end) '27',
       sum(case day(fecha) when 28 then Kgs end) '28',
       sum(case day(fecha) when 29 then Kgs end) '29',
       sum(case day(fecha) when 30 then Kgs end) '30',
       sum(case day(fecha) when 31 then Kgs end) '31',
       sum(Kgs) Total
 from Recepcion
 where fecha between '20110101' and '20110131'

[[oscarac]]

asi es...
la diferencia es que tu consulta le muestra la informacion en columnas... y la de mas arriba se la muestra en filas....

[PacoPepe]

He probado la solución de santi33a y el resultado es el mismo que el mio

Quedaría así

Código SQL [-]
select
       case extract(day from fecha)
            when 1 then sum(kgsaceituna)
            when 2 then sum(kgsaceituna)
            when 3 then sum(kgsaceituna)
            when 4 then sum(kgsaceituna)
            when 5 then sum(kgsaceituna)
            when 6 then sum(kgsaceituna)
            when 7 then sum(kgsaceituna)
            when 8 then sum(kgsaceituna)
            when 9 then sum(kgsaceituna)
            when 10 then sum(kgsaceituna)
            when 11 then sum(kgsaceituna)
            when 12 then sum(kgsaceituna)
            when 13 then sum(kgsaceituna)
            when 14 then sum(kgsaceituna)
            when 15 then sum(kgsaceituna)
            when 16 then sum(kgsaceituna)
            when 17 then sum(kgsaceituna)
            when 18 then sum(kgsaceituna)
            when 19 then sum(kgsaceituna)
            when 20 then sum(kgsaceituna)
            when 21 then sum(kgsaceituna)
            when 22 then sum(kgsaceituna)
            when 23 then sum(kgsaceituna)
            when 24 then sum(kgsaceituna)
            when 25 then sum(kgsaceituna)
            when 26 then sum(kgsaceituna)
            when 27 then sum(kgsaceituna)
            when 28 then sum(kgsaceituna)
            when 29 then sum(kgsaceituna)
            when 30 then sum(kgsaceituna)
            when 31 then sum(kgsaceituna)
        end
from recepcion
where extract(month from fecha) = 12 and ano = 2010
group by (extract(day from fecha))

Y al igual que en mi consulta, los días en que no se ha recibido mercancia, no aparece.

Por lo que el problema no es que la consulta esté mal formulada, sino que cuando el resultado es NULL, no se evalua y no da ningún resultado

El problema es que no se como resolverlo

Un saludo

[[oscarac]]

con que base de datos estas trabajando?

[santi33a]

Exacto, no me di cuenta de eso, pense que lo quería para un informe y por lo general hace falta de esa forma, si lo quiere por fila entonces con la tabla Dias, pudiera quedar así

Código SQL [-]
select d.dia, IsNull(kgs,0) kgs 
 from dia d left join 
  (select Day(fecha) Dia, sum(kgs) kgs from 
    where fecha between '20110101' and '20110131'
   group by fecha) x on d.dia = x.dia

claro en sql Server

[santi33a]

si ese es el problema entonces así

Código SQL [-]
select 
       sum(case day(fecha) when 1 then Kgs else 0 end) '1',
       sum(case day(fecha) when 2 then Kgs else 0 end) '2',  
       sum(case day(fecha) when 3 then Kgs else 0 end) '3',
       sum(case day(fecha) when 4 then Kgs else 0 end) '4',
       sum(case day(fecha) when 5 then Kgs else 0 end) '5',
       sum(case day(fecha) when 6 then Kgs else 0 end) '6',
       sum(case day(fecha) when 7 then Kgs else 0 end) '7',
       sum(case day(fecha) when 8 then Kgs else 0 end) '8',
       sum(case day(fecha) when 9 then Kgs else 0 end) '9',
       sum(case day(fecha) when 10 then Kgs else 0 end) '10',
       sum(case day(fecha) when 11 then Kgs else 0 end) '11',
       sum(case day(fecha) when 12 then Kgs else 0 end) '12',
       sum(case day(fecha) when 13 then Kgs else 0 end) '13',
       sum(case day(fecha) when 14 then Kgs else 0 end) '14',
       sum(case day(fecha) when 15 then Kgs else 0 end) '15',
       sum(case day(fecha) when 16 then Kgs else 0 end) '16',
       sum(case day(fecha) when 17 then Kgs else 0 end) '17',
       sum(case day(fecha) when 18 then Kgs else 0 end) '18',
       sum(case day(fecha) when 19 then Kgs else 0 end) '19',
       sum(case day(fecha) when 20 then Kgs else 0 end) '20',
       sum(case day(fecha) when 21 then Kgs else 0 end) '21',
       sum(case day(fecha) when 22 then Kgs else 0 end) '22',
       sum(case day(fecha) when 23 then Kgs else 0 end) '23',
       sum(case day(fecha) when 24 then Kgs else 0 end) '24',
       sum(case day(fecha) when 25 then Kgs else 0 end) '25',
       sum(case day(fecha) when 26 then Kgs else 0 end) '26',
       sum(case day(fecha) when 27 then Kgs else 0 end) '27',
       sum(case day(fecha) when 28 then Kgs else 0 end) '28',
       sum(case day(fecha) when 29 then Kgs else 0 end) '29',
       sum(case day(fecha) when 30 then Kgs else 0 end) '30',
       sum(case day(fecha) when 31 then Kgs else 0 end) '31',
       sum(Kgs) Total
 from Recepcion me 
 where fecha between '20110101' and '20110131'

Creo que funcione

[[oscarac]]

creo que lo estamos mareando un poco con todas las sintaxis que estamos colocando..
seria bueno que paco se pronuncie y nos indique si ya dio con la solucion

[PacoPepe]

Aun no lo he resuelto.

Con todas las variantes que me dais obtengo el mismo resultado que con mi primera consulta, es decir, cuando no hay recepcion, por ejemplo los domingos, ese dia no me aparece en el resultado.

La bbdd es Firebird 2.1

Un saludo

[[gluglu]]

Hace poco hice una consulta en el foro :
http://www.clubdelphi.com/foros/showthread.php?t=72512

Finalmente el Procedimiento Almacenado que dejé es :

Código SQL [-]
CREATE OR ALTER PROCEDURE AUTO_INC_DATE (
    in_first_date date,
    in_count_days integer)
returns (
    out_date date)
as
begin
  While (IN_COUNT_DAYS > 0) do begin
    OUT_DATE = IN_FIRST_DATE;
    IN_FIRST_DATE = IN_FIRST_DATE + 1;
    IN_COUNT_DAYS = IN_COUNT_DAYS - 1;
    SUSPEND;
  end
end

Esto te permite utilizar AUTO_INC_DATE en la consulta principal, o incluso en el Join, y te facilita enormemente cualquier consulta, no sólo esta, sino cualquier otra que necesites.

Tu consulta podría quedar entonces :

Código SQL [-]
Select R.dia, rm.suma from AUTO_INC_DAY(Fecha, 31) AID
left join RECEPCION R
  on R.DIA = AID.OUT_DATE

... el resto te lo dejo a tí ...

[PacoPepe]

Quedaría así, probando a partir de un dia concreto

Código SQL [-]
 select sum(r.kgs), aid.out_date from AUTO_INC_DAY('10/10/2010', 100) AID
 left join recepcion r on r.fecha > AID.OUT_DATE and r.fecha <= AID.OUT_DATE +1
 where r.ano = 2010
 group by aid.out_date

Pero da el mismo resultado que con mi consulta original

Código SQL [-]
select d.dia, rm.suma from dias d
left join
(select extract(day from fecha) as dia, extract(month from fecha) as mes, sum(kgs) as suma
           from RECEPCION
where ano = 2010
group by dia, mes ) as rm  on d.iddia = rm.dia
where rm.mes = 12

El problema es que cuando encuentra un dia en que no hay Recepción, por ejemplo un domingo, no aparece el valor 0 para ese día, sino que no aparece nada, es decir, no evalua SUM cuando encuentra NULL.

Aunque lo que no entiendo es porque no funciona el LEFT JOIN, si en la tabla de la izquierda aparece un valor, por ejemplo el dia 1, debería aparecer el día 1 en el resultado, aunque en la tabla de la derecha no exista el día 1

Un saludo

[[gluglu]]

Código SQL [-]
Select AID.Out_Date as Fecha,
(Select Coalesce(Sum(Kgs), 0) from RECEPCION 
 where Fecha = AID.Out_Date) as Cantidad_Recibida
from AUTO_INC_DAY('10/10/2010', 100) AID

[[gluglu]]

.... respecto de los Join y por qué no aparece un valor u otro dependiendo de si un valor existe en la tabla de la consulta principal o del Join, deberías consultar manuales para comprender correctamente los diferentes tipos de Join que existen.