Home » SQL & PL/SQL » SQL & PL/SQL » SQL - Date group by (oracle 8i)
SQL - Date group by [message #380433] Sun, 11 January 2009 23:13 Go to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
My Query :

select a.emp_n, min(a.attd_d), a.lv_c, e.lv_t
from emp_attd a,emp_detl c,temp_pi_emp_detl b,lv_abs_cd e
where attd_d between '01-Jan-08' and '31-Dec-08' and a.lv_c is not null and clock1_i is null
and a.emp_n = b.emp_n
and a.emp_n = c.emp_n
and a.lv_c = e.lv_c
and a.emp_n in (35,62,78,103)
group by a.emp_n, b.ps_emp_n, c.emp_m, a.lv_c, e.lv_t
order by 1,2,3,4

and the output is...
EMP_N MIN(A.ATTD LV_ LV_T
------- ---------- --- ------------------------------
35 01-01-2008 HOL HOLIDAY
35 15-01-2008 SLV SICK LEAVE
35 04-02-2008 LWP ANNUAL LEAVE
62 01-01-2008 HOL HOLIDAY
62 17-01-2008 LWP ANNUAL LEAVE
62 11-02-2008 NPL NO PAY LEAVE
62 08-04-2008 SLV SICK LEAVE
62 13-06-2008 OPF OFF WITH PAY
78 01-01-2008 HOL HOLIDAY
78 29-01-2008 LWP ANNUAL LEAVE
103 01-01-2008 HOL HOLIDAY
103 09-02-2008 LWP ANNUAL LEAVE
103 09-09-2008 LSL LONG SERVICE LEAVE
103 04-11-2008 OPF OFF WITH PAY
103 17-12-2008 NPL NO PAY LEAVE
103 19-12-2008 SLV SICK LEAVE

but,the output should be as...how to get it?
EMP_N MIN(A.ATTD LV_ LV_T
------- ---------- --- ------------------------------
35 01-01-2008 HOL HOLIDAY
62 01-01-2008 HOL HOLIDAY
78 01-01-2008 HOL HOLIDAY
103 01-01-2008 HOL HOLIDAY

Thanks ...
-Karthik
Re: SQL - Date group by [message #380435 is a reply to message #380433] Sun, 11 January 2009 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
add another clause into WHERE to restrict rows selected
Re: SQL - Date group by [message #380436 is a reply to message #380435] Sun, 11 January 2009 23:25 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
That's my query, what clause to add in where condition?
Re: SQL - Date group by [message #380439 is a reply to message #380433] Sun, 11 January 2009 23:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What is unique in the desired output?
Re: SQL - Date group by [message #380442 is a reply to message #380439] Sun, 11 January 2009 23:32 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
There is a change in the query, HOLIDAY code is not required, so my query looks like...

select a.emp_n, min(a.attd_d), a.lv_c, e.lv_t
from emp_attd a,emp_detl c,temp_pi_emp_detl b,lv_abs_cd e
where attd_d between '01-Jan-08' and '31-Dec-08' and a.lv_c is not null and clock1_i is null
and a.emp_n = b.emp_n
and a.emp_n = c.emp_n
and a.lv_c = e.lv_c
and a.emp_n in (35,62,78,103)
and a.lv_c <> 'HOL'
group by a.emp_n, b.ps_emp_n, c.emp_m, a.lv_c, e.lv_t
order by 1,2,3,4

now the output is...
EMP_N MIN(A.ATTD LV_ LV_T
--------- ---------- --- ---------------------
35 15-01-2008 SLV SICK LEAVE
35 04-02-2008 LWP ANNUAL LEAVE
62 17-01-2008 LWP ANNUAL LEAVE
62 11-02-2008 NPL NO PAY LEAVE
62 08-04-2008 SLV SICK LEAVE
62 13-06-2008 OPF OFF WITH PAY
78 29-01-2008 LWP ANNUAL LEAVE
103 09-02-2008 LWP ANNUAL LEAVE
103 09-09-2008 LSL LONG SERVICE LEAVE
103 04-11-2008 OPF OFF WITH PAY
103 17-12-2008 NPL NO PAY LEAVE
103 19-12-2008 SLV SICK LEAVE

I wanted as...
EMP_N MIN(A.ATTD LV_ LV_T
--------- ---------- --- ---------------------
35 15-01-2008 SLV SICK LEAVE
62 17-01-2008 LWP ANNUAL LEAVE
78 29-01-2008 LWP ANNUAL LEAVE
103 09-02-2008 LWP ANNUAL LEAVE

Thanks... Smile
Re: SQL - Date group by [message #380445 is a reply to message #380433] Sun, 11 January 2009 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

Please <code tags> as described in URL above!
Re: SQL - Date group by [message #380447 is a reply to message #380433] Sun, 11 January 2009 23:48 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Let me put in a simple way...

select a.emp_n, min(a.attd_d), a.lv_c
from emp_attd a
where attd_d between '01-Jan-08' and '31-Dec-08'
and a.lv_c is not null
and a.emp_n in (35,62,78,103)
and a.lv_c <> 'HOL'
group by a.emp_n, a.lv_c
order by 1,2

the output is...
EMP_N MIN(A.ATTD LV_
--------- ---------- ---
35 14-01-2008 SLV
35 04-02-2008 LWP
62 17-01-2008 LWP
62 11-02-2008 NPL
62 29-02-2008 OWP
62 19-03-2008 LAT
62 08-04-2008 SLV
62 12-05-2008 OPF
78 29-01-2008 LWP
78 19-06-2008 ABS
103 08-01-2008 SLV
103 09-02-2008 LWP
103 18-06-2008 ABS
103 21-08-2008 OPF
103 09-09-2008 LSL
103 17-12-2008 NPL

The output should be as...
EMP_N MIN(A.ATTD LV_
--------- ---------- ---
35 14-01-2008 SLV
62 17-01-2008 LWP
78 29-01-2008 LWP
103 08-01-2008 SLV

thks..
Re: SQL - Date group by [message #380451 is a reply to message #380433] Sun, 11 January 2009 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>The output should be as...
Why?

You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

Please <code tags> as described in URL above!
Re: SQL - Date group by [message #380453 is a reply to message #380447] Sun, 11 January 2009 23:58 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
The Output you want cannot be achived by your query.

Problem area.

group by a.emp_n, a.lv_c


Grouping with a.lv_c restrict you to get the output, because a.lv_c has two different values for emp = 35, and grouping is create two groups for emp = 35 (MIN(A.ATTD_D), A.LV_C).

There must be some other condition to filter a.lv_c records, provide full details, query cannot help us to dig out the problem.

Thanks
Trivendra

[Updated on: Sun, 11 January 2009 23:59]

Report message to a moderator

Re: SQL - Date group by [message #380456 is a reply to message #380453] Mon, 12 January 2009 00:05 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I wanted the first leave(date) taken by an employee along with emp code (emp_n) and leave code (lv_c), it should not to be holiday. I hope, now you can understand. Thanks.
Re: SQL - Date group by [message #380459 is a reply to message #380456] Mon, 12 January 2009 00:11 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Try with this or give us DML so I can show the output.



SELECT emp_n, lv_c, attd_d
  FROM (SELECT a.emp_n, a.lv_c, a.attd_d,
               ROW_NUMBER () OVER (PARTITION BY emp_n ORDER BY a.attd_d ASC)
                                                                           rn
          FROM emp_attd a
         WHERE attd_d BETWEEN '01-Jan-08' AND '31-Dec-08'
           AND a.lv_c IS NOT NULL
           AND a.emp_n IN (35, 62, 78, 103)
           AND a.lv_c <> 'HOL')
 WHERE rn = 1


Thanks
Trivendra

[Updated on: Mon, 12 January 2009 00:12]

Report message to a moderator

Re: SQL - Date group by [message #380462 is a reply to message #380459] Mon, 12 January 2009 00:14 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Excellent...It's working.
Trying to understand the query...can u explain briefly?
Thanks.
Re: SQL - Date group by [message #380464 is a reply to message #380462] Mon, 12 January 2009 00:26 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Break the code into pieces and understand analytic function.

Thanks
Trivendra
Re: SQL - Date group by [message #380486 is a reply to message #380459] Mon, 12 January 2009 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't read the whole thread but:
Quote:
WHERE attd_d BETWEEN '01-Jan-08' AND '31-Dec-08'

is obvsiouly wrong.
SQL> with data as (select sysdate attd_d from dual)
  2  select * from data 
  3  WHERE attd_d BETWEEN '01-Jan-08' AND '31-Dec-08'
  4  /
WHERE attd_d BETWEEN '01-Jan-08' AND '31-Dec-08'
                                     *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: SQL - Date group by [message #380491 is a reply to message #380486] Mon, 12 January 2009 01:21 Go to previous message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Hi,
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm

This link might be helpful.

And don't depend on implicit datatype conversion.
'31-Dec-08' is string not date.
Use to_date() with proper format mask to convert it into date.

Regards,
Ram.
Previous Topic: Root cause for the white space while fetching the value from ORACLE
Next Topic: insert date and time
Goto Forum:
  


Current Time: Thu Dec 08 12:18:19 CST 2016

Total time taken to generate the page: 0.10811 seconds