| View previous topic :: View next topic |
| Author |
Message |
caravas
General User
Joined: 05 Sep 2011
Posts: 31
|
 Posted: Wed May 23, 2012 8:12 am Post subject: [SOLVED] SQL: help on Summing Query from two tables |
 |
|
Hi folks,
I have a database for shift work hours control. It is running on standalone forms, and HSQLDB 2.2.8. I need to sum, by query, two time fields from diferent tables. I actually already did it for a period of a year, but my goal is do it for every month in the current year.
The two tables are:
Table name: OutrasHoras, columns: Horas (Double), Serial(Integer) primary key autoincrease field, Matricula (VarChar(15)), DataInicio(Date), DataFim(Date), Autoriza(BOOLEAN), OBS(VARCHAR_IGNORECASE(75));
Second Table Name: HorasTrabalhadas, columns: Identificacao (Integer) as primary key and auto-increase field, Data(Date), Matricula (VarChar(15)), Tipo(VarChar(30)), Horas(Time), SERIAL(VarChar(50)).
The following SQL code did the task for the current year:
| Code: |
SELECT "HorasTrabalhadas"."Matricula", SUM( EXTRACT( HOUR FROM "HorasTrabalhadas"."Horas" ) + ( EXTRACT( MINUTE FROM "HorasTrabalhadas"."Horas" ) / 60.00 )) AS HORASPLANTAO, (SELECT SUM("OutrasHoras"."Horas") FROM "OutrasHoras" WHERE( "OutrasHoras"."Matricula" = "HorasTrabalhadas"."Matricula" AND "OutrasHoras"."Autoriza" = true AND YEAR("OutrasHoras"."DataInicio") = YEAR(CURRENT_TIMESTAMP))) AS OUTRASAPROVADAS, (SUM( EXTRACT( HOUR FROM "HorasTrabalhadas"."Horas" ) + ( EXTRACT( MINUTE FROM "HorasTrabalhadas"."Horas" ) / 60.00 )) ISNULL((SELECT SUM("OutrasHoras"."Horas") FROM "OutrasHoras" WHERE( "OutrasHoras"."Matricula" = "HorasTrabalhadas"."Matricula" AND "OutrasHoras"."Autoriza" = true AND YEAR("OutrasHoras"."DataInicio") = YEAR(CURRENT_TIMESTAMP))),0)) AS TOTAL, YEAR("HorasTrabalhadas"."Data") AS ANO FROM "HorasTrabalhadas" WHERE YEAR("HorasTrabalhadas"."Data") = YEAR(CURRENT_TIMESTAMP) GROUP BY "HorasTrabalhadas"."Matricula", ANO ORDER BY "HorasTrabalhadas"."Matricula" ASC
|
Any help would be very appreciated.
Last edited by caravas on Thu May 24, 2012 2:36 pm; edited 1 time in total |
|
| Back to top |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed May 23, 2012 9:45 am Post subject: |
|
|
You said:
| caravas wrote: | | I actually already did it for a period of a year, but my goal is do it for every month in the current year. |
While I do not have your database to 'test' it, I think the following will provide you the results you described you wanted. 
| Code: | SELECT
"HorasTrabalhadas"."Matricula",
SUM( EXTRACT( HOUR FROM "HorasTrabalhadas"."Horas" ) + ( EXTRACT( MINUTE FROM "HorasTrabalhadas"."Horas" ) / 60.00 )) AS HORASPLANTAO,
(SELECT SUM("OutrasHoras"."Horas") FROM "OutrasHoras" WHERE( "OutrasHoras"."Matricula" = "HorasTrabalhadas"."Matricula" AND "OutrasHoras"."Autoriza" = true AND YEAR("OutrasHoras"."DataInicio") = YEAR(CURRENT_TIMESTAMP))) AS OUTRASAPROVADAS,
(SUM( EXTRACT( HOUR FROM "HorasTrabalhadas"."Horas" ) + ( EXTRACT( MINUTE FROM "HorasTrabalhadas"."Horas" ) / 60.00 )) ISNULL((SELECT SUM("OutrasHoras"."Horas") FROM "OutrasHoras" WHERE( "OutrasHoras"."Matricula" = "HorasTrabalhadas"."Matricula" AND "OutrasHoras"."Autoriza" = true AND YEAR("OutrasHoras"."DataInicio") = YEAR(CURRENT_TIMESTAMP))),0)) AS TOTAL,
YEAR("HorasTrabalhadas"."Data") AS "ANO",
MONTH("HorasTrabalhadas"."Data") AS "MES"
FROM "HorasTrabalhadas"
WHERE YEAR("HorasTrabalhadas"."Data") = YEAR(CURRENT_TIMESTAMP)
GROUP BY "HorasTrabalhadas"."Matricula",
"ANO",
"MES"
ORDER BY "HorasTrabalhadas"."Matricula" ASC,
"MES" ASC |
Explanation:- I add one additional column to be returned in the SELECT portion, MONTH("HorasTrabalhadas"."Data") AS "MES". This will return the month as a number, 1 to 12. Therefore, I also included it in the GROUP BY section. The same is true for the ORDER BY section.
- You can start a new query, by clicking on: Create Query in SQL View... and copy and paste the above, run it, and, save it.
- I did NOT look at the code you used, to determine if it coded well, since, you said it works. Just wanted to mention this..
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
caravas
General User
Joined: 05 Sep 2011
Posts: 31
|
| Posted: Wed May 23, 2012 1:02 pm Post subject: |
|
|
Thanks for your help my friend!
I tried the code as you suggested and had the following results:
The sum field with alias "HORASPLANTAO" worked as expected, calculating the total amount per month. But, the other sum field with alias "OUTRASAPROVADAS" just gave the total amount per year as before (it just repeats the year sum result for every row)
Well, concerning SQL acknowledgements, guess I gave my best so far 
I really need a light in this struggle  |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed May 23, 2012 3:14 pm Post subject: |
|
|
You said:
| caravas wrote: | | The sum field with alias "HORASPLANTAO" worked as expected, calculating the total amount per month. But, the other sum field with alias "OUTRASAPROVADAS" just gave the total amount per year as before (it just repeats the year sum result for every row) |
Therefore, per your comment, the only result field that is not correct, per your words, is, "OUTRASAPROVADAS" . . . so . . . please try the following Query.
| Code: | SELECT
"HorasTrabalhadas"."Matricula",
SUM( EXTRACT( HOUR FROM "HorasTrabalhadas"."Horas" ) + ( EXTRACT( MINUTE FROM "HorasTrabalhadas"."Horas" ) / 60.00 )) AS "HORASPLANTAO",
SUM("OutrasHoras"."Horas") AS "OUTRASAPROVADAS",
(SUM( EXTRACT( HOUR FROM "HorasTrabalhadas"."Horas" ) + ( EXTRACT( MINUTE FROM "HorasTrabalhadas"."Horas" ) / 60.00 )) ISNULL((SELECT SUM("OutrasHoras"."Horas") FROM "OutrasHoras" WHERE( "OutrasHoras"."Matricula" = "HorasTrabalhadas"."Matricula" AND "OutrasHoras"."Autoriza" = true AND YEAR("OutrasHoras"."DataInicio") = YEAR(CURRENT_TIMESTAMP))),0)) AS "TOTAL",
YEAR("HorasTrabalhadas"."Data") AS "ANO",
MONTH("HorasTrabalhadas"."Data") AS "MES"
FROM "HorasTrabalhadas"
LEFT OUTER JOIN "OutrasHoras" ON "HorasTrabalhadas"."Matricula" = "OutrasHoras"."Matricula"
WHERE YEAR("HorasTrabalhadas"."Data") = YEAR(CURRENT_TIMESTAMP)
AND YEAR("OutrasHoras"."DataInicio") = YEAR(CURRENT_TIMESTAMP)
AND "OutrasHoras"."Autoriza" = TRUE
GROUP BY "HorasTrabalhadas"."Matricula",
"ANO",
"MES"
ORDER BY "HorasTrabalhadas"."Matricula" ASC,
"MES" ASC |
Explanation: I am using a LEFT OUTER JOIN here . . . and . . . this means, it will return rows that have matching data in the left table "HorasTrabalhadas", even if there's no matching rows in the other table "OutrasHoras" .
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
caravas
General User
Joined: 05 Sep 2011
Posts: 31
|
| Posted: Thu May 24, 2012 6:07 am Post subject: |
|
|
Hi There,
I tried your code and the sum results were not correct. Maybe I should send my db files for you... if you don't mind to take a look.
I get the monthly results I want with separated queries. But, using only one query, the results are incorrect, except for a whole year period (first code I posted)...
The following code do the task, for every year and every month, on table "HorasTrabalhadas":
| Code: |
SELECT
"HorasTrabalhadas"."Matricula",
SUM( EXTRACT( HOUR FROM "HorasTrabalhadas"."Horas" ) + ( EXTRACT( MINUTE FROM "HorasTrabalhadas"."Horas" ) / 60.00 )) AS HORASPLANTAO,
MONTH("HorasTrabalhadas"."Data") AS MES,
YEAR("HorasTrabalhadas"."Data") AS ANO
FROM "HorasTrabalhadas"
GROUP BY "HorasTrabalhadas"."Matricula", MES, ANO
ORDER BY ANO, MES ASC
|
and this code do the same task on table "OutrasHoras":
| Code: |
SELECT
"OutrasHoras"."Matricula",
ISNULL(SUM("OutrasHoras"."Horas"),0)AS TOTAL,
MONTH("OutrasHoras"."DataInicio") AS MES,
YEAR("OutrasHoras"."DataInicio") AS ANO
FROM "OutrasHoras"
GROUP BY "OutrasHoras"."Matricula", MES, ANO
ORDER BY "OutrasHoras"."Matricula", ANO, MES ASC
|
Thanks again! |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Thu May 24, 2012 6:15 am Post subject: |
|
|
I am sending you a PM ( Private Message ).
Sliderule |
|
| Back to top |
|
|
caravas
General User
Joined: 05 Sep 2011
Posts: 31
|
| Posted: Thu May 24, 2012 2:35 pm Post subject: |
|
|
Well,
Mister Sliderule sent to me a SQL code that has answered all my problems. It really worked.
I will not post the code here, since a I did not created it, but, if the autor wants, no problem to me at all.
Thanks again Mister Sliderule! |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Thu May 24, 2012 2:44 pm Post subject: |
|
|
Yes, feel free to post the SQL code for the VIEWS and the QUERY. They are good examples of using the LEFT OUTER JOIN SQL capabilityu .
I am glad to help, and, appreciate that you have marked the forum post as Solved, so, ohters reading the post will know.
Sliderule |
|
| Back to top |
|
|
caravas
General User
Joined: 05 Sep 2011
Posts: 31
|
| Posted: Thu May 24, 2012 3:20 pm Post subject: |
|
|
I'm posting here the step by step procedure sent by Mr Sliderule:
1. Open your OpenOffice Base file ( *.odb )
2. From the menu: Tools -> SQL...
3, In the Command to Execute Box, copy and paste:
| Code: |
-- New View "HorasTrabalhadas_TOTAL_VIEW"
CREATE VIEW "HorasTrabalhadas_TOTAL_VIEW" AS
(SELECT
"HorasTrabalhadas"."Matricula",
SUM( HOUR( "HorasTrabalhadas"."Horas") + ( MINUTE( "HorasTrabalhadas" ."Horas") / 60.00 )) AS "HORASPLANTAO",
YEAR( "HorasTrabalhadas"."Data") AS "ANO",
MONTH( "HorasTrabalhadas"."Data") AS "MES"
FROM "HorasTrabalhadas"
WHERE YEAR( "HorasTrabalhadas"."Data") = YEAR(CURRENT_DATE)
GROUP BY "HorasTrabalhadas"."Matricula",
"ANO",
"MES");
-- - - - - - - - - - - - - - - - - - -
-- New View "OutrasHoras_TOTAL_VIEW"
CREATE VIEW "OutrasHoras_TOTAL_VIEW" AS
(SELECT
"OutrasHoras"."Matricula",
SUM("OutrasHoras"."Horas") AS "OUTRASHORAS_TOTAL",
MONTH("OutrasHoras"."DataInicio") AS "MES",
YEAR("OutrasHoras"."DataInicio") AS "ANO"
FROM "OutrasHoras"
WHERE YEAR("OutrasHoras"."DataInicio") = YEAR(CURRENT_TIMESTAMP)
AND "OutrasHoras"."Autoriza" = TRUE
GROUP BY "OutrasHoras"."Matricula", "MES", "ANO"
ORDER BY "OutrasHoras"."Matricula", ANO, MES ASC);
|
4. Press the Execute button
5. Press the Close button
6. IMPORTANT STEP: From the Menu: View -> Refresh Tables
7. Create a new Query from: Create Query in SQL View...
8. Copy and paste:
| Code: |
SELECT
"A"."Matricula",
"A"."HORASPLANTAO" AS "HORASPLANTAO",
"B"."OUTRASHORAS_TOTAL",
COALESCE("A"."HORASPLANTAO",0) + COALESCE("B"."OUTRASHORAS_TOTAL",0) AS "** TOTAL **",
"A"."ANO",
"A"."MES"
FROM "HorasTrabalhadas_TOTAL_VIEW" as "A"
LEFT OUTER JOIN "OutrasHoras_TOTAL_VIEW" as "B" ON "A"."Matricula" = "B"."Matricula"
AND "A"."MES" = "B"."MES"
ORDER BY "A"."Matricula",
"A"."ANO",
"A"."MES"
|
9. Run the Query ( F5 or Run QUery icon ) and review it gives you the values you expect
10. Save the Query to a name of your choice.
11. If you want, you can add a line, BEFORE the ORDER BY . . .
WHERE "A"."Matricula" = :Enter_Matricula_Number
By Sliderule =] |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
