Home » SQL & PL/SQL » SQL & PL/SQL » calulating target and actual hrs (sql)
calulating target and actual hrs [message #640719] Tue, 04 August 2015 08:49 Go to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo

I would like to ask you,
is there possible to get the target and actual hrs from two differnt date?

Actually, I am trying to write query to get the two different columns from two different date.

id   step    start_date                   end_date
100  1      25.05.2014 12:02:02       25.05.2014 12:15:05
100  2      25.05.2014 12:15:05       26.05.2014 14:15:00
105  1      22.05.2014 11:52:59       22.05.2014 11:52:59
105  2      22.05.2014 11:52:59       26.05.2014 14:23:32
110  1      21.04.2014 12:38:28       22.05.2014 08:13:00

my output should be;

id  step   start_date   end_date  tar_h    act_h



Can anyone help me or any idea please?
thankx in advance

regards,
Re: calulating target and actual hrs [message #640720 is a reply to message #640719] Tue, 04 August 2015 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
my output should be;


Question

SQL> select 'id  step   start_date   end_date  tar_h    act_h' from dual;
id  step   start_date   end_date  tar_h    act_h


Is this you want?
Is not then explain.

Also, With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Re: calulating target and actual hrs [message #640721 is a reply to message #640719] Tue, 04 August 2015 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone help me or any idea please?
which rows go with which rows & why?

Please post actual & COMPLETE Test Case
Re: calulating target and actual hrs [message #640723 is a reply to message #640721] Tue, 04 August 2015 09:34 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo Michel Cadot and BlackSwan,

thankyou very much for your Feedback.
Here is my rest of the Statement:

create table mytab1( 
id          number,
step        number,
start_date  date,
end_date    date
);

insert into mytab1 (id, step, start_date, end_date) values(100, 1, '25.05.2014 12:02:02', '25.05.2014 12:15:05');
insert into mytab1 (id, step, start_date, end_date) values(100, 2, '25.05.2014 12:15:05', '26.05.2014 14:15:00');
insert into mytab1 (id, step, start_date, end_date) values(105, 1, '22.05.2014 11:52:59', '22.05.2014 11:52:59');
insert into mytab1 (id, step, start_date, end_date) values(105, 2, '22.05.2014 11:52:59', '26.05.2014 14:23:32');
insert into mytab1 (id, step, start_date, end_date) values(110, 1, '21.04.2014 12:38:28', '22.05.2014 08:13:00');


actually, I dont have yet the right Output, but i would like to have my Output as follow:
id     start_date        end_date             tar_h    act_h
100    25.05.2014 12:02  26.05.2014 14:15:00  2        3

(PS.the Output is just an example)
Regards,
Re: calulating target and actual hrs [message #640724 is a reply to message #640723] Tue, 04 August 2015 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> create table mytab1( 
id          number,
step        number,
start_date  date,
end_date    date
);

insert into mytab1 (id, step, start_date, end_date) values(100, 1, '25.05.2014 12:02:02', '25.05.2014 12:15:05');
insert into mytab1 (id, step, start_date, end_date) values(100, 2, '25.05.2014 12:15:05', '26.05.2014 14:15:00');
insert into mytab1 (id, step, start_date, end_date) values(105, 1, '22.05.2014 11:52:59', '22.05.2014 11:52:59');
insert into mytab1 (id, step, start_date, end_date) values(105, 2, '22.05.2014 11:52:59', '26.05.2014 14:23:32');
insert into mytab1 (id, step, start_date, end_date) values(110, 1, '21.04.2014 12:38:28', '22.05.2014 08:13:00');  2    3    4    5    6  
Table created.

SQL> SQL> insert into mytab1 (id, step, start_date, end_date) values(100, 1, '25.05.2014 12:02:02', '25.05.2014 12:15:05')
                                                                   *
ERROR at line 1:
ORA-01843: not a valid month


SQL> insert into mytab1 (id, step, start_date, end_date) values(100, 2, '25.05.2014 12:15:05', '26.05.2014 14:15:00')
                                                                   *
ERROR at line 1:
ORA-01843: not a valid month


SQL> insert into mytab1 (id, step, start_date, end_date) values(105, 1, '22.05.2014 11:52:59', '22.05.2014 11:52:59')
                                                                   *
ERROR at line 1:
ORA-01843: not a valid month


SQL> insert into mytab1 (id, step, start_date, end_date) values(105, 2, '22.05.2014 11:52:59', '26.05.2014 14:23:32')
                                                                   *
ERROR at line 1:
ORA-01843: not a valid month


SQL> 


Re: calulating target and actual hrs [message #640725 is a reply to message #640724] Tue, 04 August 2015 09:53 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
opps sry for the mistake :/

here is:
insert into mytab1 (id, step, start_date, end_date) values(100, 1, '25.05.2014', '25.05.2014');
insert into mytab1 (id, step, start_date, end_date) values(100, 2, '25.05.2014', '26.05.2014');
insert into mytab1 (id, step, start_date, end_date) values(105, 1, '22.05.2014', '22.05.2014');
insert into mytab1 (id, step, start_date, end_date) values(105, 2, '22.05.2014', '26.05.2014');
insert into mytab1 (id, step, start_date, end_date) values(110, 1, '21.04.2014 ', '22.05.2014');
Re: calulating target and actual hrs [message #640726 is a reply to message #640723] Tue, 04 August 2015 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i would like to have my Output as follow:


Explain ir!
What are the specifications to lead to it?

Quote:
PS.the Output is just an example


I hope this is an example which matches with your test case otherwise it is completely useless.

Re: calulating target and actual hrs [message #640728 is a reply to message #640726] Tue, 04 August 2015 10:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be clear - we have no idea how you calculate expected and actual hours from a start date and end date.
You need to explain the logic with words.

And your insert statements need to include a to_date calls with the correct format mask specified for all dates. Otherwise people with different date settings to you can't run the inserts.
Re: calulating target and actual hrs [message #640779 is a reply to message #640728] Wed, 05 August 2015 01:59 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo all..

Thanking you all for your feedback and suggestion and sorry for sharing incomplete question/test case. Actually,

I am also in confusion, how do i do this, thats why, i could not explain you in detail.
As, I have bit more explore my case and want to share it again with you, may be i will get some help/idea

regarding my problem Smile

create table mytab1( 
id          number,
step        number,
start_date  date,
end_date    date
);

Here it is my edited insert statement:
insert into mytab1 (id, step, start_date, end_date) values(100, 1, to_date('27.05.2014 09:15:50','dd.mm.yyyy 

hh:mi:ss'), to_date('27.05.2014 09:15:50', 'dd.mm.yyyy hh:mi:ss')); 
insert into mytab1 (id, step, start_date, end_date) values(100, 2, to_date('27.05.2014 09:15:50','dd.mm.yyyy 

hh:mi:ss'), to_date('27.05.2014 09:34:33', 'dd.mm.yyyy hh:mi:ss'));
insert into mytab1 (id, step, start_date, end_date) values(100, 3, to_date('27.05.2014 09:15:50','dd.mm.yyyy 

hh:mi:ss'), to_date('27.05.2014 09:40:39', 'dd.mm.yyyy hh:mi:ss'));
insert into mytab1 (id, step, start_date, end_date) values(100, 4, to_date('27.05.2014 09:15:50','dd.mm.yyyy 

hh:mi:ss'), to_date('27.05.2014 09:44:50', 'dd.mm.yyyy hh:mi:ss'));
insert into mytab1 (id, step, start_date, end_date) values(100, 5, to_date('27.05.2014 09:15:50','dd.mm.yyyy 

hh:mi:ss'), to_date('27.05.2014 09:50:47', 'dd.mm.yyyy hh:mi:ss'));
insert into mytab1 (id, step, start_date, end_date) values(100, 1, to_date('27.05.2014 09:15:50','dd.mm.yyyy 

hh:mi:ss'), to_date('27.05.2014 10:02:04', 'dd.mm.yyyy hh:mi:ss'));


and from this data, I would like to have my output as:
id     start_date           end_date           act_h
100    27.05.2014 09:15:50  27.05.2014 10:02:04      0.7705

may be i have clear you bit more? any suggestion/idea please? Smile
Regards,
Re: calulating target and actual hrs [message #640780 is a reply to message #640779] Wed, 05 August 2015 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't tell us what is the definition and how you compute "act_h" then we can help you in no way.

Re: calulating target and actual hrs [message #640783 is a reply to message #640780] Wed, 05 August 2015 02:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if you don't know the logic for computing target and actual hours then you need to ask someone at your company who does know. We can't guess the rules of your application.
Re: calulating target and actual hrs [message #640787 is a reply to message #640780] Wed, 05 August 2015 03:05 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo Michel.

Thankx for your reply.

Actually, my Problem is:
I do have one work which has different start/end time and from these time, i would like to find out, how many time has been spent for this work (i.e. act_hr.

I also really don't know how should i do this, thats why i can not post my query here, otherwise it would be easy to explain my Problem.
I tried with lead() or max() function... still have no idea, what to do next Sad

select id, step, start_date,
                 lead(start_date) over (partition by id, step order by id) as next_date
          from mytab1;

regards,
Re: calulating target and actual hrs [message #640790 is a reply to message #640787] Wed, 05 August 2015 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I do have one work which has different start/end time and from these time, i would like to find out, how many time has been spent for this work (i.e. act_hr.


This does not explain anything.

Explain, in details, 0.7705 in your previous result.
The answer should nothing but "0.7705 is computed as...".

Re: calulating target and actual hrs [message #640791 is a reply to message #640790] Wed, 05 August 2015 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
We're NOT asking you to explain it in SQL. We're asking you to explain in words.
e.g. actual hours is the time between <date column> in <row identified by criteria> and <other date column> in <row identified by criteria>
Re: calulating target and actual hrs [message #640802 is a reply to message #640791] Wed, 05 August 2015 06:19 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
create table mytab1( 
id          number,
sid        number,
start_date  date,
end_date    date,
step       number
);



Here it is my edited insert statement:
insert into mytab1 (id, sid, start_date, end_date, step) values(100, 10, to_date('27.05.2014 09:15:50','dd.mm.yyyy hh:mi:ss'), to_date('27.05.2014 09:15:50', 'dd.mm.yyyy hh:mi:ss'), 1); 

insert into mytab1 (id, sid, start_date, end_date, step) values(100, 10, to_date('27.05.2014 09:15:50','dd.mm.yyyy 
hh:mi:ss'), to_date('27.05.2014 09:34:33', 'dd.mm.yyyy hh:mi:ss')2);

insert into mytab1 (id, sid, start_date, end_date, step) values(100, 10, to_date('27.05.2014 09:15:50','dd.mm.yyyy 
hh:mi:ss'), to_date('27.05.2014 09:40:39', 'dd.mm.yyyy hh:mi:ss')3);

insert into mytab1 (id, sid, start_date, end_date, step) values(105, 20, to_date('27.05.2014 09:15:50','dd.mm.yyyy 
hh:mi:ss'), to_date('27.05.2014 09:44:50', 'dd.mm.yyyy hh:mi:ss')1);

insert into mytab1 (id, sid, start_date, end_date, step) values(105, 20, to_date('27.05.2014 09:15:50','dd.mm.yyyy 
hh:mi:ss'), to_date('27.05.2014 09:50:47', 'dd.mm.yyyy hh:mi:ss')2);


so, once again i have changed my table and data and , i tried to explore my problem, may be i have tried it better than last time Smile
the work1 with id = 100 and sid = 10 and 3 different start and end date with three different steps has been worked and it has been finalized in step 3, so it means, till step 3 it has been worked out, and from here have to find out what is the actual hours to get this work done.
similarly, the work2 with id = 105, sid = 20 and 2 different start/end date has been worked till two steps and in step two has been it done, so have to find what is the actual hours, it takes to done this job.

so, may be i have to check with id and sid with start date and end date Sad
thanking you all.
regards,


Re: calulating target and actual hrs [message #640805 is a reply to message #640802] Wed, 05 August 2015 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again, test your test case before posting it.
SQL> insert into mytab1 (id, sid, start_date, end_date, step) values(100, 10, to_date('27.05.2014 09:15:50','dd.mm.yyyy
  2  hh:mi:ss'), to_date('27.05.2014 09:34:33', 'dd.mm.yyyy hh:mi:ss')2);
hh:mi:ss'), to_date('27.05.2014 09:34:33', 'dd.mm.yyyy hh:mi:ss')2)
                                                                 *
ERROR at line 2:
ORA-00917: missing comma


Isn't this just:
SQL> select id, sid, sum(end_date-start_date)*24 act_hr
  2  from mytab1
  3  group by id, sid
  4  order by  id, sid
  5  /
        ID        SID     ACT_HR
---------- ---------- ----------
       100         10 .725555556
       105         20 1.06583333

Re: calulating target and actual hrs [message #641031 is a reply to message #640805] Mon, 10 August 2015 00:32 Go to previous message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Thank you Michel.. Smile

Previous Topic: How to get value of table
Next Topic: Need PL/SQL Script
Goto Forum:
  


Current Time: Thu Mar 28 08:55:56 CDT 2024