SQL - Date group by [message #380433] |
Sun, 11 January 2009 23:13  |
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 #380442 is a reply to message #380439] |
Sun, 11 January 2009 23:32   |
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...
|
|
|
|
Re: SQL - Date group by [message #380447 is a reply to message #380433] |
Sun, 11 January 2009 23:48   |
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 #380453 is a reply to message #380447] |
Sun, 11 January 2009 23:58   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
The Output you want cannot be achived by your query.
Problem area.
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   |
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   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
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
|
|
|
|
|
|
|