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]checkif value in one table is part of another table
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dhanoff
General User
General User


Joined: 29 May 2011
Posts: 27

PostPosted: Sat Jun 04, 2011 8:32 am    Post subject: [Solved]checkif value in one table is part of another table Reply with quote

Hi

I am facing another issue in calculating over time. I have start_date_time & end_date_time in worker_table. If start_date is a part of Holiday list which is stored as Holiday_date then I am supposed to execuet a expression.

I am trying to use "CASEWHEN" along with 'IN' but not able to get the right results


Last edited by dhanoff on Sun Jun 05, 2011 12:20 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Sat Jun 04, 2011 9:12 am    Post subject: Reply with quote

It would be helpful, if, you could show me the EXACT SQL you are using, that works.

Put another way, forgetting for the moment, trying to include the HOLIDAY part . . . show the SQL that you are using EXCLUDING ANY HOLIDAY CALCULATION.

In order to 'see' the EXACT SQL . . . if you are have a Query
  1. Open your OpenOffice Base file ( *.odb )
  2. On the left, click on the Queries icon
  3. Under the list of Queries, right click on your Query, and, from the list choose:

    Edit in SQL View...

  4. Copy to the Clipboard your SQL
  5. Paste your SQL in this forum

The reason is, I suspect you will need a UNION ALL clause, but, that will only run in DIRECT MODE ( passing the SQL directly to the database engine ). That is fine, I just, want to get the syntax correct, with your exact table and field names.

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


Joined: 29 May 2011
Posts: 27

PostPosted: Sat Jun 04, 2011 9:28 am    Post subject: Reply with quote

My exact code is like this,


CASEWHEN("VISIT DETAILS"."START DATE" IN ( SELECT "SPECIALHOLIDAY"."SH" FROM "SPECIALHOLIDAY"), DATEDIFF('MI',"VISIT DETAILS"."END DATE", "VISIT DETAILS"."START DATE")/60.00,NULL) AS "OT1"

Following are the tables

1.Visit details holds data on work. ( There is space between visit and details)
2. specialholiday holds holiday data.

One issue was that I was missing a cama in statement, when it was corrected , above code got executed but i am facing another issue now.

When I calulate OT for Holiday and normal day using the casewhen statement it is writing data in two different columns. I want to have it in same column.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Sat Jun 04, 2011 9:34 am    Post subject: Reply with quote

That is NOT what I wanted you to provide to me.

I want to see a version of the SQL that works CORRECTLY ( from a WORKING QUERY ), in your case, including the calculation of OVERTIME . . . BUT . . . forgetting for a moment . . . the HOLIDAY issue.

I want to see a 'complete' SQL with all of the fields in the SELECT clause, and, also, the TABLE(s), WHERE clause, and, ORDER BY clause if used.

Reason, I want to start with WORKING SQL, and, then be able to ADD the components to include the HOLIDAY scenario.

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


Joined: 29 May 2011
Posts: 27

PostPosted: Sat Jun 04, 2011 9:38 am    Post subject: Reply with quote

I am still working on it , presently I am up to this code,

SELECT "EMPLOYEE LIST"."FIRST NAME", "VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE", "FACTORY LIST"."FACTORYNAME", "VISIT DETAILS"."PURPOSE",

CASEWHEN("VISIT DETAILS"."START DATE" IN ( SELECT "SPECIALHOLIDAY"."SH" FROM "SPECIALHOLIDAY"), DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00,NULL) AS "OT",

CASEWHEN("VISIT DETAILS"."START DATE" IN ( SELECT "NORMALHOLIDAY"."NH" FROM "NORMALHOLIDAY"), DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00,NULL) AS "OT"



FROM "VISIT DETAILS", "FACTORY LIST", "TOUR DETAILS", "EMPLOYEE LIST" WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID" AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID" AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Sat Jun 04, 2011 10:11 am    Post subject: Reply with quote

If I understand you correctly, from the SQL you provided above . . . please follow these directions:

  1. Open your OpenOffice Base file
  2. Press the Queries icon on the left
  3. Under Tasks, press . . . Create Query in SQL View...
  4. Copy and paste the following SQL . . . NOTE the COMMENTS ( lines starting with two dashes ) for DOCUMENTATION

    Code:
    -- Query that MUST be RUN DIRECT . . . BECAUSE OF USE OF UNION ALL clause
    -- First Select gets calculation for "SPECIALHOLIDAY"
    SELECT
       "EMPLOYEE LIST"."FIRST NAME",
       "VISIT DETAILS"."START DATE",
       "VISIT DETAILS"."END DATE",
       "FACTORY LIST"."FACTORYNAME",
       "VISIT DETAILS"."PURPOSE",
       DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00 as "OT"

    FROM "VISIT DETAILS",
         "FACTORY LIST",
         "TOUR DETAILS",
         "EMPLOYEE LIST"

    WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
      AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
      AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
      AND EXISTS ( SELECT * FROM "SPECIALHOLIDAY" WHERE "SPECIALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )
     
          UNION ALL

    -- This Select gets calculation for "NORMALHOLIDAY"
    SELECT
       "EMPLOYEE LIST"."FIRST NAME",
       "VISIT DETAILS"."START DATE",
       "VISIT DETAILS"."END DATE",
       "FACTORY LIST"."FACTORYNAME",
       "VISIT DETAILS"."PURPOSE",
       DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00 as "OT"


    FROM "VISIT DETAILS",
         "FACTORY LIST",
         "TOUR DETAILS",
         "EMPLOYEE LIST"

    WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
      AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
      AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
      AND EXISTS ( SELECT * FROM "NORMALHOLIDAY" WHERE "NORMALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )

  5. IMPORTANT - DO NOT FORGET: On the toolbar, press the Icon .. . Run SQL command directly ( This is the icon with the word SQL and a green check mark )
  6. If the result is what you want, you can save the Query, be sure to also save the OpenOffice Base file ( *.odb )

I hope this helps, please be sure to let me/ us know.

Sliderule

Explanation: You did NOT provide me the SQL for the condition when it is NOT a "SPECIALHOLIDAY nor a "NORMALHOLIDAY" as asked . . . therefore, it is NOT included in the results above. Smile
Back to top
View user's profile Send private message
dhanoff
General User
General User


Joined: 29 May 2011
Posts: 27

PostPosted: Sat Jun 04, 2011 10:23 am    Post subject: Reply with quote

Thanks a lot Sliderule.

It works like charm, I could not provide the SQL for non holiday OT as I was still trying to figure out on how to make it happen.

On other normal days I have to calculate number of hours before 8:30 AM and number of hours after 17:00 on Start date.

On saturdays I have to calculate number of hours before 8:30 AM and number of hours after 13:30 on start date.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Sat Jun 04, 2011 10:38 am    Post subject: Reply with quote

OK.

A few important things about a Query with a UNION or UNION ALL

  1. If you want an ORDER BY ( to sort the fields ) clause, it MUST be at the END ( bottom ) of the SQL

  2. If you use Alias to define the column name for output . . . the FIRST SQL Select statement Alias clauses will be what is used ( like the "OT" in the first Select )

  3. The number of fields in EACH Select clause must match, both by the NUMBER of fields output, AND, the FIELD TYPES ( for example, DATE, TIME, TIMESTAMP, VARCHA, VARCHAR_IGNORECASE, DECIMAL, FLOAT, INTEGER, BOOLEAN etc )

    I was imagining you wanted a column for "regular pay", and, a column for "OT", and, this can be done, perhaps by adding another SQL SELECT that calculates the "regular pay", and, adding the MATCHING appropriate columns in the other SELECT statements Smile

  4. If you think you have this 'complete' ( whenever that is ) you might consider making it a VIEW . . . that way . . . you could perform a Query on the VIEW ( just like a table ) and be Prompted to bring back certain dates and / or individuals Smile
I hope this helps, please, mark this as [Solved] in title of your FIRST post. Smile

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
dhanoff
General User
General User


Joined: 29 May 2011
Posts: 27

PostPosted: Sat Jun 04, 2011 10:59 am    Post subject: Reply with quote

Hey Thanks..

Continuing in the same lines can I use following code for getting OT on normal days

SELECT
"EMPLOYEE LIST"."FIRST NAME",
"VISIT DETAILS"."START DATE",
"VISIT DETAILS"."END DATE",
"FACTORY LIST"."FACTORYNAME",
"VISIT DETAILS"."PURPOSE",
---please check airthmetic operation to add OT in morning and evening.

(DATEDIFF('MI',"VISIT DETAILS"."START DATE",cast("VISIT DETAILS"."START DATE" as date) ||' '||'8:30')/60.00)+
(DATEDIFF('MI',cast("VISIT DETAILS"."START DATE" as date ||' '||'17:00'), "VISIT DETAILS"."END DATE")/60.00) AS "OT"

FROM "VISIT DETAILS",
"FACTORY LIST",
"TOUR DETAILS",
"EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
---condition to get rid of negative OT hours if start date is after 8:30AM
AND DATEDIFF('MI',"VISIT DETAILS"."START DATE",cast("VISIT DETAILS"."START DATE" as date) ||' '||'8:30')/60.00<=8.5


Thanks in advance
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Sat Jun 04, 2011 11:54 am    Post subject: Reply with quote

I do NOT understand you question in the above post at all. It is not clear to me at all what you want or what you are asking.

Looking at your SQL code . . . just a few comments . . . TIME is stated as HH:MM:SS surrounded by single quotes. The Time . . . HH is with a 24 hour clock, AND, should include a leading zero for hours of 00 ( midnight - '00:00:00' ) to 9:00 AM ( '09:00:00' ).

Therefore, in your code, you have, '8:30' and it should be '08:30:00' .

If what you wanted is to COMBINE the above working SQL ( I assume what you wrote above works, ¿ ¿ ¿ right ? ? ? ) with the UNION ALL Query I gave above . . . it might look like:

Code:
-- Query that MUST be RUN DIRECT . . . BECAUSE OF USE OF UNION ALL clauses
-- This Select gets the calculation for OverTime before '08:30:00' and after '17:00:00'
SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   -- please check airthmetic operation to add OT in morning and evening.
   (DATEDIFF('MI',"VISIT DETAILS"."START DATE",cast("VISIT DETAILS"."START DATE" as date) ||' '||'08:30:00')/60.00)+
   (DATEDIFF('MI',cast("VISIT DETAILS"."START DATE" as date ||' '||'17:00:00'), "VISIT DETAILS"."END DATE")/60.00) AS "OT"

FROM "VISIT DETAILS",
     "FACTORY LIST",
     "TOUR DETAILS",
     "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  -- condition to get rid of negative OT hours if start date is after 08:30:00AM
  AND DATEDIFF('MI',"VISIT DETAILS"."START DATE",cast("VISIT DETAILS"."START DATE" as date) ||' '||'08:30:00')/60.00 <= 8.5

  UNION ALL
 

-- This Select gets calculation for "SPECIALHOLIDAY"
SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00 as "OT"

FROM "VISIT DETAILS",
     "FACTORY LIST",
     "TOUR DETAILS",
     "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND EXISTS ( SELECT * FROM "SPECIALHOLIDAY" WHERE "SPECIALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )
 
      UNION ALL

-- This Select gets calculation for "NORMALHOLIDAY"
SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00 as "OT"


FROM "VISIT DETAILS",
     "FACTORY LIST",
     "TOUR DETAILS",
     "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND EXISTS ( SELECT * FROM "NORMALHOLIDAY" WHERE "NORMALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )

And, I still think you would want an ORDER BY ( SORT ) clause at the end, perhaps at the BOTTOM, adding:

Code:
ORDER BY "EMPLOYEE LIST"."FIRST NAME",
         "VISIT DETAILS"."START DATE"

NOTE: I do NOT have your database with real data, so, I can NOT test it, but, I think it will work. Smile

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
Sliderule
Super User
Super User


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

PostPosted: Sat Jun 04, 2011 12:17 pm    Post subject: Reply with quote

I am not sure about your intentions, that is, if the addtional NOT EXISTS is needed for the FIRST Select. That is, how are hours prior to '08:00:30' and after '17:00:00' handled if a "NORMALHOLIDAY" or "SPECIALHOLIDAY" ? ? ?

Code:
-- Query that MUST be RUN DIRECT . . . BECAUSE OF USE OF UNION ALL clauses
-- This Select gets the calculation for OverTime before '08:30:00' and after '17:00:00'
SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   -- please check airthmetic operation to add OT in morning and evening.
   (DATEDIFF('MI',"VISIT DETAILS"."START DATE",cast("VISIT DETAILS"."START DATE" as date) ||' '||'08:30:00')/60.00)+
   (DATEDIFF('MI',cast("VISIT DETAILS"."START DATE" as date ||' '||'17:00:00'), "VISIT DETAILS"."END DATE")/60.00) AS "OT"

FROM "VISIT DETAILS",
     "FACTORY LIST",
     "TOUR DETAILS",
     "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  -- condition to get rid of negative OT hours if start date is after 08:30:00AM
  AND DATEDIFF('MI',"VISIT DETAILS"."START DATE",cast("VISIT DETAILS"."START DATE" as date) ||' '||'08:30:00')/60.00 <= 8.5
  -- Do NOT perform this calculation for "SPECIALHOLIDAY" since will be covered below
  AND NOT EXISTS ( SELECT * FROM "SPECIALHOLIDAY" WHERE "SPECIALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )
  -- Do NOT perform this calculation for "NORMALHOLIDAY" since will be covered below
  AND NOT EXISTS ( SELECT * FROM "NORMALHOLIDAY" WHERE "NORMALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )

  UNION ALL

-- This Select gets calculation for "SPECIALHOLIDAY"
SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00 as "OT"

FROM "VISIT DETAILS",
     "FACTORY LIST",
     "TOUR DETAILS",
     "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND EXISTS ( SELECT * FROM "SPECIALHOLIDAY" WHERE "SPECIALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )
 
      UNION ALL

-- This Select gets calculation for "NORMALHOLIDAY"
SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   DATEDIFF('MI',"VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE")/60.00 as "OT"

FROM "VISIT DETAILS",
     "FACTORY LIST",
     "TOUR DETAILS",
     "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND EXISTS ( SELECT * FROM "NORMALHOLIDAY" WHERE "NORMALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )


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
dhanoff
General User
General User


Joined: 29 May 2011
Posts: 27

PostPosted: Sat Jun 04, 2011 1:20 pm    Post subject: Reply with quote

OK Let me explain in detail....

Complete working hours on Normal /Special holiday/Sunday is Overtime. Rates applicable are different and varies based on the grade of the worker.This is defined separately.

Only on Normal days OT is calculated before 8:30 and after 17:00. on saturdays it is before 8:30 and after 13:30.Since Datediff gives values in negative, this imposes another 3 conditions

1. OT before 8:30 only
2. OT after 17:00 only
3. OT before 8:30 and after 17:30.

What I need to do is to take
1. Employee NAme
2. Reprot start date
3. Report end date

as input and generate a report with OT , respective charges.

At the moment I am just writing code to generate report for all employees in order by date. At the moment by your help My code will look like


Code:

SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   DATEDIFF( 'MI', "VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE" ) / 60.00 AS "OT"

FROM
   "VISIT DETAILS",
   "FACTORY LIST",
   "TOUR DETAILS",
   "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
   AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
   AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
   AND EXISTS ( SELECT * FROM "SPECIALHOLIDAY" WHERE "SPECIALHOLIDAY"."SH" = "VISIT DETAILS"."START DATE" )

UNION ALL

SELECT
   "EMPLOYEE LIST"."FIRST NAME",
   "VISIT DETAILS"."START DATE",
   "VISIT DETAILS"."END DATE",
   "FACTORY LIST"."FACTORYNAME",
   "VISIT DETAILS"."PURPOSE",
   DATEDIFF( 'MI', "VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE" ) / 60.00 AS "OT"

FROM
  "VISIT DETAILS",
  "FACTORY LIST",
  "TOUR DETAILS",
  "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND EXISTS ( SELECT * FROM "NORMALHOLIDAY" WHERE "NORMALHOLIDAY"."NH" = "VISIT DETAILS"."START DATE" )

UNION ALL

SELECT
  "EMPLOYEE LIST"."FIRST NAME",
  "VISIT DETAILS"."START DATE",
  "VISIT DETAILS"."END DATE",
  "FACTORY LIST"."FACTORYNAME",
  "VISIT DETAILS"."PURPOSE",
  DATEDIFF( 'MI', "VISIT DETAILS"."START DATE", "VISIT DETAILS"."END DATE" ) / 60.00 AS "OT"
  FROM "VISIT DETAILS", "FACTORY LIST", "TOUR DETAILS", "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND DAY( "VISIT DETAILS"."START DATE" ) = 1

UNION ALL

SELECT
  "EMPLOYEE LIST"."FIRST NAME",
  "VISIT DETAILS"."START DATE",
  "VISIT DETAILS"."END DATE",
  "FACTORY LIST"."FACTORYNAME",
  "VISIT DETAILS"."PURPOSE",
  DATEDIFF( 'MI', "VISIT DETAILS"."START DATE", CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '08:30:00' ) / 60.00 +
  DATEDIFF( 'MI', CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '13:30', "VISIT DETAILS"."END DATE" ) / 60.00 AS "OT"

FROM
  "VISIT DETAILS",
  "FACTORY LIST",
  "TOUR DETAILS",
  "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND DAY( "VISIT DETAILS"."START DATE" ) = 7 AND HOUR( "VISIT DETAILS"."START DATE" ) <= 8.5
  AND HOUR( "VISIT DETAILS"."END DATE" ) >= 13.5

UNION ALL

SELECT
  "EMPLOYEE LIST"."FIRST NAME",
  "VISIT DETAILS"."START DATE",
  "VISIT DETAILS"."END DATE",
  "FACTORY LIST"."FACTORYNAME",
  "VISIT DETAILS"."PURPOSE",
  DATEDIFF( 'MI', "VISIT DETAILS"."START DATE", CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '08:30:00' ) / 60.00 AS "OT"
FROM

  "VISIT DETAILS",
  "FACTORY LIST",
  "TOUR DETAILS",
  "EMPLOYEE LIST"
WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND DAY( "VISIT DETAILS"."START DATE" ) = 7 AND HOUR( "VISIT DETAILS"."START DATE" ) <= 8.5
  AND HOUR( "VISIT DETAILS"."END DATE" ) <= 13.5

UNION ALL

SELECT
  "EMPLOYEE LIST"."FIRST NAME",
  "VISIT DETAILS"."START DATE",
  "VISIT DETAILS"."END DATE",
  "FACTORY LIST"."FACTORYNAME",
  "VISIT DETAILS"."PURPOSE",
  DATEDIFF( 'MI', CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '13:30:00', "VISIT DETAILS"."END DATE" ) / 60.00 AS "OT"

FROM
  "VISIT DETAILS", "FACTORY LIST",
  "TOUR DETAILS", "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND DAY( "VISIT DETAILS"."START DATE" ) = 7
  AND HOUR( "VISIT DETAILS"."START DATE" ) >= 8.5
  AND HOUR( "VISIT DETAILS"."END DATE" ) >= 13.5

UNION ALL

SELECT
  "EMPLOYEE LIST"."FIRST NAME",
  "VISIT DETAILS"."START DATE",
  "VISIT DETAILS"."END DATE",
  "FACTORY LIST"."FACTORYNAME",
  "VISIT DETAILS"."PURPOSE",
  DATEDIFF( 'MI', "VISIT DETAILS"."START DATE", CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '08:30:00' ) / 60.00 +
  DATEDIFF( 'MI', CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '17:00:00', "VISIT DETAILS"."END DATE" ) / 60.00 AS "OT"
FROM
  "VISIT DETAILS",
  "FACTORY LIST",
  "TOUR DETAILS",
  "EMPLOYEE LIST"

WHERE
  "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND HOUR( "VISIT DETAILS"."START DATE" ) <= 8.5
  AND HOUR( "VISIT DETAILS"."END DATE" ) >= 17
 
 UNION ALL

SELECT
  "EMPLOYEE LIST"."FIRST NAME",
  "VISIT DETAILS"."START DATE",
  "VISIT DETAILS"."END DATE",
  "FACTORY LIST"."FACTORYNAME",
  "VISIT DETAILS"."PURPOSE",
  DATEDIFF( 'MI', "VISIT DETAILS"."START DATE", CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '08:30:00' ) / 60.00 AS "OT"

FROM
  "VISIT DETAILS",
  "FACTORY LIST",
  "TOUR DETAILS",
  "EMPLOYEE LIST"
WHERE
  "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND HOUR( "VISIT DETAILS"."START DATE" ) <= 8.5
  AND HOUR( "VISIT DETAILS"."END DATE" ) <= 17

UNION ALL

SELECT
  "EMPLOYEE LIST"."FIRST NAME",
  "VISIT DETAILS"."START DATE",
  "VISIT DETAILS"."END DATE",
  "FACTORY LIST"."FACTORYNAME",
  "VISIT DETAILS"."PURPOSE",
  DATEDIFF( 'MI', CAST( "VISIT DETAILS"."START DATE" AS DATE ) || ' ' || '17:00:00', "VISIT DETAILS"."END DATE" ) / 60.00 AS "OT"

FROM
  "VISIT DETAILS",
  "FACTORY LIST",
  "TOUR DETAILS",
  "EMPLOYEE LIST"

WHERE "VISIT DETAILS"."FACT_ID" = "FACTORY LIST"."FACT_ID"
  AND "VISIT DETAILS"."TOUR_ID" = "TOUR DETAILS"."TOUR_ID"
  AND "TOUR DETAILS"."EMP_ID" = "EMPLOYEE LIST"."EMP_ID"
  AND HOUR( "VISIT DETAILS"."START DATE" ) >= 8.5
  AND HOUR( "VISIT DETAILS"."END DATE" ) >= 17

ORDER BY

  "VISIT DETAILS"."START DATE"

Now another constraint is that in Direct SQL mode I cannot give input from a form. But it happens easily when running through base.
i need to figure it out to do it.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Sat Jun 04, 2011 3:21 pm    Post subject: Reply with quote

I did mention an important point before . . . please search THIS FORUM POST for using the SQL here to create a VIEW.

In short, when you have the SQL working correctly ( returning the fields and calculations correctly - and saved ) FOR ALL EMPLOYEES and DATES . . . it is easy.

A VIEW is like a pseudo-table. That is, the VIEW does NOT contain data ( it does NOT replicate the data in your database ). But, it is instructions on how to RETRIEVE the information / calculations.

  1. Open your OpenOffice database file ( *.odb )
  2. Press Queries icon on the left
  3. Find your working Query under the Queries list
  4. Right click on it, and, from the pop-up choose: Create as View
  5. Choose a name for your view . . . My standard is to choose a descriptive name, and, AT the end of it, add . . . _VIEW . . . so it is clear the name refers to a VIEW Smile

    For example ( you can choose the best name for your needs ) . . . you could choose a name of: OverTimeCalcuation_VIEW

    Additional Note: This View will now show up on the list of Tables, and, can be used exactly as a table.

  6. Now, you can create a NEW Query, using the View you just defined . . . either with the GUI ( Create Query in Design View ) . . . and . . . you can have the Query prompt you for names and / or dates.


For example:

Code:
Select
   *
From "OverTimeCalcuation_VIEW"
Where "FIRST NAME" = :Enter_First_Name
  and "START DATE" >= :Enter_From_Date
  and "END DATE" <= "Enter_To_Date


One More Thought: Two of your SQLs above has a TIME coded as '13:30' . . . it is NOT in HH:MM:SS format, and, ¿ ¿ ¿ should it really be: '17:30:00' ? ? ?

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
dhanoff
General User
General User


Joined: 29 May 2011
Posts: 27

PostPosted: Sun Jun 05, 2011 12:22 am    Post subject: Reply with quote

Thanks a lot. It was a great help.
I have corrected the mistakes in code too.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Sun Jun 05, 2011 11:04 am    Post subject: Reply with quote

One more HINT . . . I recommend you Create another Query . . . I named mine: information_schema_views

Code:
SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VIEWS

After running this Query, the column TABLE_NAME are the names of the Views you have created, and, VIEW_DEFINITION is the SQL ( Select statement(s) ) you used to define the View.

The reason this is important is, if you want to MODIFY a View, first copy the VIEW_DEFINITION so you have the start point again, before you delete the current View. Smile

Sliderule
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
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