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 |
|
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 #640723 is a reply to message #640721] |
Tue, 04 August 2015 09:34 |
|
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 |
|
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 |
|
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 #640728 is a reply to message #640726] |
Tue, 04 August 2015 10:28 |
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 |
|
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
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?
Regards,
|
|
|
|
|
Re: calulating target and actual hrs [message #640787 is a reply to message #640780] |
Wed, 05 August 2015 03:05 |
|
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
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 #640791 is a reply to message #640790] |
Wed, 05 August 2015 03:15 |
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 |
|
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
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
thanking you all.
regards,
|
|
|
Re: calulating target and actual hrs [message #640805 is a reply to message #640802] |
Wed, 05 August 2015 06:29 |
|
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
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 08:55:56 CDT 2024
|