OraFAQ Forum: SQL & PL/SQL » How to calculate time variation between same day times.

Home » SQL & PL/SQL » SQL & PL/SQL » How to calculate time variation between same day times. (Oracle 10g R2, Windows Server 2008 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to calculate time variation between same day times. [message #625906]

Wed, 15 October 2014 23:56

mdsirajoddin
Messages: 20
Registered: July 2011
Location: Hyderabad

Junior Member

Dear All,

I am really new to Oracle Development, kindly suggest me a best way to resolve this issue.

I have a requirement to take the employee entry punch report and produce the LOP Minutes count which is based on only In Punch timings and noway related about out punch of early quitters.

we have 3 shifts over here, 6:00, 8:30, 14:00. It is permitted for employee for next five minutes. i.e., 6:05, 8:35, 14:05. the time should pick the correct one.

the punch report will be generated monthly, and they will send us in .csv format.

if a person in punch is 08:08, it should take as 03 Minutes, if he In Punch is 9.02 then it should take 54 as LOP Minutes for that day and should produce in .csv back.

Can anyone please help me in solving the issue..

Thanks in Advance.
Mohd Sirajoddin

[Updated on: Wed, 15 October 2014 23:57]

Report message to a moderator

Re: How to calculate time variation between same day times. [message #625908 is a reply to message #625906]

Thu, 16 October 2014 00:24

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Please provide the table creation statement and sample data. Keep it simple so that anybody could use it to reproduce. The following links would help you, OraFAQ Forum Guide and How to use [code] tags.

Report message to a moderator

Re: How to calculate time variation between same day times. [message #625910 is a reply to message #625906]

Thu, 16 October 2014 01:12

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In addition, when you get help, feedback and thank people who take part of their time to help you.

Report message to a moderator

Re: How to calculate time variation between same day times. [message #625911 is a reply to message #625908]

Thu, 16 October 2014 01:43

mdsirajoddin
Messages: 20
Registered: July 2011
Location: Hyderabad

Junior Member

CREATE TABLE LOP_MONTHLY_TAB(
EMP_NO VARCHAR2( Cool

,
ATTN_YEAR NUMBER(4),
ATTN_MONTH NUMBER(2),
D1 varchar2(5) ,
D2 varchar2(5) ,
D3 varchar2(5) ,
D4 varchar2(5) ,
D5 varchar2(5) ,
D6 varchar2(5) ,
D7 varchar2(5) ,
D8 varchar2(5) ,
D9 varchar2(5) ,
D10 varchar2(5) ,
D11 varchar2(5) ,
D12 varchar2(5) ,
D13 varchar2(5) ,
D14 varchar2(5) ,
D15 varchar2(5) ,
D16 varchar2(5) ,
D17 varchar2(5) ,
D18 varchar2(5) ,
D19 varchar2(5) ,
D20 varchar2(5) ,
D21 varchar2(5) ,
D22 varchar2(5) ,
D23 varchar2(5) ,
D24 varchar2(5) ,
D25 varchar2(5) ,
D26 varchar2(5) ,
D27 varchar2(5) ,
D28 varchar2(5) ,
D29 varchar2(5) ,
D30 varchar2(5) ,
D31 varchar2(5)
)

INSERT INTO LOP_MONTHLY_TAB VALUES('00009999',2014,8,'8:30','8:31','8:32','8:33','8:34','8:35','8:36','8:37','8:38','8:39','8:40','8:41','8:42','8:43','8:44','8: 45','8:46','8:47','8:48','8:49','8:50','8:51','8:52','8:53','8:54','8:55','8:56','8:57','8:58','8:59','9:00')

INSERT INTO LOP_MONTHLY_TAB VALUES('00009998',2014,8,'8:31','8:32','8:33','8:34','8:35','8:36','8:37','8:38','8:39','8:40','8:41','8:42','8:43','8:44','8:45','8: 46','8:47','8:48','8:49','8:50','8:51','8:52','8:53','8:54','8:55','8:56','8:57','8:58','8:59','9:00','9:01')

SELECT * FROM LOP_MONTHLY_TAB

DROP TABLE LOP_MONTHLY_TAB

THE TABLE WILL BE MOSTLY LIKE THIS.

OUR INITIAL TIME IS 8.30 AND GRACE PERIOD UPTO 8:35. Hence upto 08:35 the LOP minutes count is zero. If it is 8:36, the LOP count will be 1 for 8.37 lop will be 2 and on.

Report message to a moderator

Re: How to calculate time variation between same day times. [message #625913 is a reply to message #625911]

Thu, 16 October 2014 02:17

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 16 October 2014 08:12

In addition, when you get help, feedback and thank people who take part of their time to help you.

You cannot make arithmetical operations on strings. Fix your table and data.
What should be the result for the data you gave?

Please How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.

Report message to a moderator

Re: How to calculate time variation between same day times. [message #625931 is a reply to message #625913]

Thu, 16 October 2014 06:03

mdsirajoddin
Messages: 20
Registered: July 2011
Location: Hyderabad

Junior Member

yes, we can't do arithmatic operations on strings, but can we do the same by convert them into to_timestamp(D1,'MM') then D1 - to_timestamp('8:35','MI')

and calculate these to generate an .csv format file.

Thanks for you time and cooperation

Report message to a moderator

Re: How to calculate time variation between same day times. [message #626006 is a reply to message #625931]

Sat, 18 October 2014 13:43

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

OP's question via PM :

mdsirajoddin wrote on Thu, 16 October 2014 11:18

Hi Lalit,

Can I have an exact datatype to store time like 08:25, 14:20, 22:30.

Just use DATE datatype, it will have the time part too. Please look into documentation http://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#NLSPG237

Report message to a moderator

Previous Topic:	Generating Numbers between given ranges
Next Topic:	to_date error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:30:31 CDT 2024