Sql help

UDA
Enthusiast

Sql help

Hi All,
Can any body tell that what will be the sql for below table ??
Threre will be one table like SAL_DTL table and the records like this :

EMPL_ID NAME SAL_EFF_DATE SALARY
11111 CHRIS 01/25/2004 2000
11111 CHRIS 01/11/2005 3000
11111 CHRIS 01/20/2006 4000
11111 CHRIS 01/02/2007 5000
22222 STEVE 01/05/2005 3500
22222 STEVE 01/15/2006 3900
22222 STEVE 02/25/2007 4700
33333 PETE 02/16/2005 3200
33333 PETE 01/05/2006 3700
33333 PETE 03/31/2007 4800

Now I need salary amount effective on a particular date, like on 12/31/2006
for all the employees.
what would be the SQL for that
Thanks in Advance
3 REPLIES
Enthusiast

Re: Sql help

Does this help?

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from sal_dtl order by 1,2;

*** Query completed. 11 rows found. 4 columns returned.
*** Total elapsed time was 1 second.

id name dt sal
----------- ------- ---------- -----------
11111 CHRIS 01/20/2006 4000
11111 CHRIS 11/25/2006<< 4750<<
11111 CHRIS 01/02/2007 5000
11111 CHRIS 01/11/2005 3000
11111 CHRIS 01/25/2004 2000
22222 STEVE 02/25/2007 4700
22222 STEVE 01/15/2006<< 3900<<
22222 STEVE 01/05/2005 3500
33333 PETE 02/16/2005 3200
33333 PETE 03/31/2007 4800
33333 PETE 01/05/2006<< 3700<<

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select id,sal,dt from sal_dtl OT where
dt=(select max(dt) from sal_dtl where id=OT.id and dt between '01/01/2006' and '01/01/2007');

*** Query completed. 3 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

id sal dt
----------- ----------- ----------
11111 4750 11/25/2006
22222 3900 01/15/2006
33333 3700 01/05/2006

The above query gives the last modified date and salary in the year 2006
Junior Contributor

Re: Sql help

Hi ray,

select * from SAL_DTL
where SAL_EFF_DATE <= date '2006-12-31'
qualify row_number() over (partition by EMPL_ID order by SAL_EFF_DATE desc) = 1

Dieter
Enthusiast

Re: Sql help

Thanks a lot