SQL Query Help 3

[gtb12314]

Hi Everyone,

Below is my sql command, now I want that this command should return the result only where the pay period end date is in the year 2007.
---------------------------------------------------
SELECT al.process_level,al.deptname,al.first_name,al.last_name,al.emp_status,al.fte_total,prt.employee,prt.hours
FROM lrsuser.v_associatelist al,lawson.prtime prt
where prt.company=al.company
AND
prt.employee=al.employee
AND
al.group_name='G:ACTIVE'
AND
prt.pay_sum_grp IN ('FLP','PTS','PTU')
AND
al.emp_status IN ('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB');
---------------------------------------------------------

In the database I have a datefield as prt.per_end_date. Please let me know how to do this in AND clause.

Please help.

Thank You!!

 

[kskid]

add the following

and to_char(prt.per_end_date,'YYYY') = '2007'

 

[Ascalonian]

Just add :

AND TO_CHAR(prt.pay_period, 'YYYY') = '2007'

Not sure what the column name is, but that was my best guess

 

[Ascalonian]

Wow, 2 seconds apart. Great minds DO think alike!

 

[jaxtell]

Well, if pay_period is indexed, you might want to do

AND prt.pay_period between to_date('01-01-2007','MM-DD-YYYY') and TO_DATE('12-31-2007','MM-DD-YYYY')

 

[SantaMufasa]

Wow, 2 seconds apart. Great minds DO think alike!

Yes, great minds do think alike, but they do so two minutes apart instead of two seconds apart...not quite at the speed of thought. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Ascalonian]

Haha, yeah. I noticed that afterwards... D'oh!