List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 14 2006 4:00pm
Subject:Re: Complex Query
View as plain text  
"ElkinFernando Ortiz" <elkinfortiz@stripped> wrote on 03/10/2006 12:26:57 
AM:

> I will explain my problem in spanish and english.
> 
> Se deben Presentar los consumos por 24 horas de energia para su ingreso. 
Si
> el usuario ha digitado 10 registros, se deben presentar estos mas los 
otros
> 14 correspondientes desde la hora 11 hasta la 24 con los consumos en 
cero.
> Siempre un conjunto de 24 Registros.
> La primera parte de la union es clara, pero como calculo los restantes 
14
> registros en la misma consulta ?
> 
> 
> I need to present consumptions for 24 hours that correspond to energy
> consumptions. If user digit 10 registers, I need to adition others 14
> registers with zero consumptions that correspond from hour 11 to 24. 
Always
> 24 registers.
> the first part of union is rigth. How i calculate for union the other 14
> register in the same Query?
> 
> 
> First Part.
> SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where
> e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.Hour
> 
> UNION () ??????
> 
> Thanks,
> 
> 
> Elkin
> Medellin,Colombia
> 

Please forgive my Spanish, it's a little rusty and I don't know how to 
type the accented characters.

The server cannot give you data that it does not have. The easiest 
solution is to create a table with ALL of the hour values you want to see 
in a report and JOIN your original query into it to fill in the missing 
information.

El servidor no puede darte los datos que no tiene. La solucion mas facil 
es crear una tabla con todos los valores de la hora que te deseas ver en 
un informe y JOIN su pregunta original en ella a complete la informacion 
que falta.

CREATE TABLE report_hours (
        horas tinyint
);

INSERT report_hora VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), 
(9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), 
(21), (22), (23);

SELECT e.Plant,e.Date,e.Hour,e.Consuption 
From report_horas h
LEFT JOIN Energy e
        on h.horas = e.hour
Where e.Plant=Var_Plant 
        AND e.Date=Var_Date 
GROUP BY h.Hour,e.Plant,e.Date,


Or, you could JOIN the plant information to the report_hora table to fill 
in missing information as part of a UNION query.

O, tu puedes JOIN la información de la planta a la tabla del report_hora 
para complete la informacion que falta como parte de una pregunta UNION.

(SELECT e.Plant,e.Date,e.Hour,e.Consuption From Energy Where
e.Plant=Var_Plant AND e.Date=Var_Date GROUP BY e.Plant,e.Date,e.Hour)
UNION
(SELECT var_plant, var_date, h.horas,0
FROM report_horas h
LEFT JOIN Energy e
        ON h.horas = e.hour
        AND e.plant = Var_Plant
        AND e.date = Var_Date
WHERE e.plant is null)
ORDER BY Hour;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Again, HUGE apologies for my poor Spanish.
Thread
bytefx native provider for .NET and mysql5Dan Rossi9 Mar
  • Re: bytefx native provider for .NET and mysql5Dan Rossi9 Mar
  • Complex QueryElkinFernando Ortiz14 Mar
    • Re: Complex QuerySGreen14 Mar
    • Re: Complex QueryPeter Brawley14 Mar