Home » SQL & PL/SQL » SQL & PL/SQL » how to write a query for my necessity
icon11.gif  how to write a query for my necessity [message #296475] Sun, 27 January 2008 22:08 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi Friends,

I want to provide the privilege leave to the employees. Here i have total presentday for the whole year for every employees in the table. If present days between

     totpresent 0   -9     = 0   day
                10  -19.5  = 0.5 day
                20  -29.5  = 1 day

and so on.


please help me.


wbr
Kanish
Re: how to write a query for my necessity [message #296478 is a reply to message #296475] Sun, 27 January 2008 22:38 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi Friend,
Thank u for all

i got solution.

wbr

kanish

Re: how to write a query for my necessity [message #296481 is a reply to message #296475] Sun, 27 January 2008 22:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Please share you solution with the rest of the community .

Thumbs Up
Rajuvan.
Re: how to write a query for my necessity [message #296488 is a reply to message #296475] Sun, 27 January 2008 23:26 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

HI,

Nothing it is compare to u genious people.
Select emplcode,present,case
                when present <= 9 then 0
                when present  <= 19.5 then 0.5
                when present  <= 29.5 then 1
                when present  <= 39.5 then 1.5
                when present  <= 49.5 then 2
                when present  <= 59.5 then 2.5
                when present  <= 69.5 then 3
                when present  <= 79.5 then 3.5
                when present  <= 89.5 then 4
                when present  <= 99.5 then 4.5
                when present  <= 109.5 then 5
                when present  <= 119.5 then 5.5
                when present  <= 129.5 then 6
                when present  <= 139.5 then 6.5
                when present  <= 149.5 then 7
                when present  <= 159.5 then 7.5
                when present  <= 169.5 then 8
                when present  <= 179.5 then 8.5
                when present  <= 189.5 then 9
                when present  <= 199.5 then 9.5
                when present  <= 209.5 then 10
                when present  <= 219.5 then 10.5
                when present  <= 229.5 then 11
                when present  <= 239.5 then 11.5
                when present  <= 249.5 then 12
                when present  <= 259.5 then 12.5
                when present  <= 269.5 then 13
                when present  <= 279.5 then 13.5
                else 14 end creditpl
                from(
Select a.emplcode,sum(presentdays)+sum(compoffdays) present
from attendance a,emplmast b where a.emplcode like '0%' and yearno = 2007 
and a.emplcode = b.emplcode and b.empstatus not in ('L','D','V') 
group by A.emplcode,yearno)


wbr
Kanish
Re: how to write a query for my necessity [message #296497 is a reply to message #296475] Sun, 27 January 2008 23:54 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Good Attempt ,
But hardcording is Not a good Method at all .

You can try something like ,

SQL> SELECT x Present ,
  2        TRUNC(x,-1)/20 creditpl
  3  FROM  (
  4             SELECT 10 x FROM DUAL UNION
  5             SELECT 24 x FROM DUAL UNION
  6             SELECT 39.5 x FROM DUAL UNION
  7             SELECT 45 x FROM DUAL UNION
  8             SELECT 56 x FROM DUAL UNION
  9             SELECT 65 x FROM DUAL UNION
 10             SELECT 80 x FROM DUAL
 11      );

   PRESENT   CREDITPL
---------- ----------
        10         .5
        24          1
      39.5        1.5
        45          2
        56        2.5
        65          3
        80          4

7 rows selected.

SQL>


Thumbs Up
Rajuvan
Re: how to write a query for my necessity [message #296501 is a reply to message #296475] Mon, 28 January 2008 00:31 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi raju

Very great and nice presentation.

Thank and lot

Wbr.

kanish
Re: how to write a query for my necessity [message #296506 is a reply to message #296497] Mon, 28 January 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And maybe pointing to the right direction would be a better way for OP to learn...
I know you are sorry and you will no more do it... as usual... still in hand...

Regards
Michel
Re: how to write a query for my necessity [message #296509 is a reply to message #296475] Mon, 28 January 2008 00:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I know you will come with thus point , As usual .

OP told .

Quote:
Hi Friend,
Thank u for all

i got solution.

wbr

kanish


ie ,
1. He is satisfied with his own solution .
2. He is no more interested for any advice.
3. Its is more unlikely that OP goes for any direction given at this point.
4. He Posted his his real solution simply because i asked to post for the rest of the community.
5. But what he was having is the Hardcorded solution.
6. Thats why i gave this solution directly

( It was for the rest of community all the way as OP posted for the rest of the community )

Thats my justification.
I dont care whether you are pleased with this Or Not .


Thumbs Down
Rajuvan.



[Updated on: Mon, 28 January 2008 00:50]

Report message to a moderator

Re: how to write a query for my necessity [message #296512 is a reply to message #296475] Mon, 28 January 2008 00:56 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi raju,

one doubt your attempt is very great eventhough did u see my query very upper bound always 14 day privileges.

 when present  <= 279.5 then 13.5
                else 14 end creditpl
                from(
Select a.emplcode,sum(presentdays)+sum(compoffdays) present
from attendance a,emplmast b where a.emplcode like '0%' and yearno = 2007 
and a.emplcode = b.emplcode and b.empstatus not in ('L','D','V') 
group by A.emplcode,yearno)


wbr
kanish

Re: how to write a query for my necessity [message #296515 is a reply to message #296475] Mon, 28 January 2008 01:00 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


It was not in your actual requirement

Anyway Apply LEAST function to Trunc solution .

Hope Michel is satisfied now Smile

Thumbs Up
Rajuvan

[Updated on: Mon, 28 January 2008 01:02]

Report message to a moderator

Re: how to write a query for my necessity [message #296517 is a reply to message #296512] Mon, 28 January 2008 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search the documentation for the built-in function LEAST, and see how you can use that.

[Edit: Need to type faster; Rajuvan beat me to it]

[Updated on: Mon, 28 January 2008 01:03]

Report message to a moderator

Re: how to write a query for my necessity [message #296531 is a reply to message #296475] Mon, 28 January 2008 01:37 Go to previous message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi,

Thanks raju, i got with least

wbr.
kanish
Previous Topic: Partitioning on table
Next Topic: Error consuming https web services
Goto Forum:
  


Current Time: Thu Dec 08 14:36:52 CST 2016

Total time taken to generate the page: 0.10182 seconds