Home » SQL & PL/SQL » SQL & PL/SQL » sql (merged)
sql (merged) [message #578006] Fri, 22 February 2013 11:43 Go to next message
Nirai Raja
Messages: 8
Registered: June 2012
Location: India
Junior Member
The below query is taken from the procedure

SELECT l_stafnum = stafnum,
category,
duty,
date,
signon,
signoff,
l_rest = rest,
valid,
FROM SESSION.valid_pf
WHERE invalid != 'I'

In the above query has set of data which each staff may have more than one duty.

For example

stafnum categoy duty date signon signoff rest valid
232 M a/c 2/3/12 5:00 8:00 12:00 I
232 M Sup 2/3/12 10:00 13:00 8:00 I
111 M Clen 2/3/12 9:00 12:00 12:00 I
232 M Sw 2/3/12 15:00 20:00 12:00 I
111 M Wp 2/3/12 10:00 14:00 06:00 I
121 M a/c 2/3/12 5:00 8:00 12:00 I

The select query will retrieve the value which mentioned this example.

Now, First I need to take how many duty has been allocated for each stafnumber.

232 staff allocated to three duty (a/c,sup,sw)

Then need to compare signoff value for first row addition of rest value for the same row
i.e, i = 08:00+1200

Then need to compare value of signon for the same staff 232 for the second row which is 10:00

if (i>10:00) then valid;

Please help me out, how I can take same staff number and find it how many duty the staff has.
And how to take signoff and rest field value of first row, for the same staff
then compare those value with the same staff number second row signon value.

I am planning to write it by using while loop. correct me if am I wrong. Suggestion are open.

Thanks inadvance
Malar
Re: sql [message #578007 is a reply to message #578006] Fri, 22 February 2013 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sql [message #578008 is a reply to message #578006] Fri, 22 February 2013 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table 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.

Before, Please read OraFAQ Forum Guide and 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.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: sql [message #578020 is a reply to message #578006] Fri, 22 February 2013 17:38 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
Sorry, but your query is invalid, please post the actual query.
sql [message #578142 is a reply to message #578006] Sun, 24 February 2013 12:13 Go to previous messageGo to next message
Nirai Raja
Messages: 8
Registered: June 2012
Location: India
Junior Member
Hi,

I have below data in a table

For example

stafnum categoy duty date signon signoff rest valid
232 M a/c 2/3/12 5:00 8:00 12:00 I
232 M Sup 2/3/12 10:00 13:00 8:00 I
111 M Clen 2/3/12 9:00 12:00 12:00 I
232 M Sw 2/3/12 15:00 20:00 12:00 I
111 M Wp 2/3/12 10:00 14:00 06:00 I
121 M a/c 2/3/12 5:00 8:00 12:00 I

First need take stafnum+rest value of first row

second again need to take only signoff value for the same stafnum second row

l_add = 232(stafnum)+12:00(rest)

l_add1 = 13:00 (signoff)


Is the above condition possible by query.

Thanks inadvance,
Malar
Re: sql [message #578143 is a reply to message #578142] Sun, 24 February 2013 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
rows in a table are like balls in a basket.
Which ball in the basket is the first ball?
Rows in a table have NO inherent order.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sql [message #578144 is a reply to message #578142] Sun, 24 February 2013 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

BlackSwan wrote on Fri, 22 February 2013 18:46
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Michel Cadot wrote on Fri, 22 February 2013 19:32
With any SQL or PL/SQL question, please, Post a working Test case: create table 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.

Before, Please read OraFAQ Forum Guide and 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.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel


Re: sql [message #578145 is a reply to message #578142] Sun, 24 February 2013 13:23 Go to previous message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, this is the same question than in your previous topic; why do you think you will have different answers when you still refuse to follow our guide and even doesn't have the correctness to answer the questions that have been asked to you?

I merge the 2 topics.

Next time find a more meaningful and useful title than "sql" in a SQL forum.

Regards
Michel

[Updated on: Sun, 24 February 2013 13:24]

Report message to a moderator

Previous Topic: Decrease precision or scale error
Next Topic: Difference between variables declared under Begin and Declare
Goto Forum:
  


Current Time: Thu Oct 23 10:50:18 CDT 2014

Total time taken to generate the page: 0.09553 seconds