OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] SQL: help on Summing Query from two tables

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Wed May 23, 2012 8:12 am    Post subject: [SOLVED] SQL: help on Summing Query from two tables Reply with quote

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
View user's profile Send private message MSN Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed May 23, 2012 9:45 am    Post subject: Reply with quote

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. Smile

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:
  1. 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. Smile

  2. 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.

  3. 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
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Wed May 23, 2012 1:02 pm    Post subject: Reply with quote

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 Very Happy
I really need a light in this struggle Shocked
Back to top
View user's profile Send private message MSN Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed May 23, 2012 3:14 pm    Post subject: Reply with quote

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" Smile .

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
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Thu May 24, 2012 6:07 am    Post subject: Reply with quote

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
View user's profile Send private message MSN Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu May 24, 2012 6:15 am    Post subject: Reply with quote

I am sending you a PM ( Private Message ).

Sliderule
Back to top
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Thu May 24, 2012 2:35 pm    Post subject: Reply with quote

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
View user's profile Send private message MSN Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu May 24, 2012 2:44 pm    Post subject: Reply with quote

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 . Smile

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
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Thu May 24, 2012 3:20 pm    Post subject: Reply with quote

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
View user's profile Send private message MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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