"ElkinFernando Ortiz" <[EMAIL PROTECTED]> 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.
