cant get latest value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

cant get latest value

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-28 : 02:32:07

Dear All,

The actaul situation is the employee 000772 is got the promotion in the month of 7 th 2006 from helper to tailor (C12_Positioncode)
But The same month the H10_Base_Salary is modified.

The second qry return the correct values. it tooks the latest modified values, but i need the 2 nd values only,

But Dont check the H10_Effective_Date_To is null

Qry:
Select * from H10_Empasgn Where H01_Emp_Num = '000772'
Result:
C03_Organisation_code C29_Location_code H01_Emp_Num H10_Assignment_No C02_Function_Code C12_Positioncode C08_Catg_code C09_Grade C10_Job_code H10_Primary_Tag H10_Effective_Date_From H10_Effective_Date_To H10_Rep_To_EmpNum H10_Rep_To_Asgn_No C01_Asgn_Reason_Type C01_Asgn_Reason_Subtype H10_Base_Salary C58_Rate_frequency C58_Pay_frequency H10_Currency H10_Amt_Per_Flag H10_Increase_Amt H40_Emp_Status1 H40_Emp_Status2 H40_Emp_Status3 H40_Emp_Status4 H40_Emp_Status5 H10_Dummy1 H10_Dummy2 User_id Modified_date Time_Stamp H40_Emp_Status6 H40_Emp_Status7 H40_Emp_Status8 H10_Ben_Union H10_Ben_Affiliation
--------------------- ----------------- ----------- ----------------- ----------------- ---------------- ------------- --------- ------------ --------------- ------------------------------------------------------ ------------------------------------------------------ ----------------- ------------------ -------------------- ----------------------- ------------------------------ ------------------ ----------------- ------------ ---------------- ------------------------------ --------------- --------------- --------------- --------------- --------------- ---------- ---------- ------- ------------------------------------------------------ ------------------ --------------- --------------- ------------------------- ------------- -------------------
RSWMLNJA BANG 000772 1 CUT HLR DWRK HLR C Y 2006-07-01 00:00:00.000 2006-07-04 23:59:59.000 NULL NULL SAL NONE 86.00000000 DAY MON INR NULL NULL NULL NULL NULL NULL NULL NULL NULL 9 2006-08-11 13:19:36.733 0x00000000000CEBA0 NULL NULL NULL NULL NULL
RSWMLNJA BANG 000772 1 CUT TLR DWRK TLR C Y 2006-07-05 00:00:00.000 NULL NULL NULL SAL NONE 86.50000000 DAY MON INR NULL NULL NULL NULL NULL NULL NULL NULL NULL 9 2006-08-11 13:19:36.733 0x00000000000CEBA1 NULL NULL NULL NULL NULL
RSWMLNJA BANG 000772 1 CUT HLR DWRK HLR C Y 2005-11-24 00:00:00.000 2006-06-30 23:59:59.000 NULL NULL NW NONE 79.00000000 DAY MON INR NULL NULL NULL NULL NULL NULL NULL NULL NULL 9 2006-08-11 13:06:15.543 0x00000000000CEB96 NULL NULL NULL NULL NULL

(3 row(s) affected)

Qry:
Select * from H10_Empasgn Where H01_Emp_Num = '000772'
And H10_Effective_Date_FROM <= '2006-07-31'
And ISNULL(H10_Effective_Date_To,CONVERT(datetime,'31/12/9999',103)) >= '2006-07-01'
Result:

C03_Organisation_code C29_Location_code H01_Emp_Num H10_Assignment_No C02_Function_Code C12_Positioncode C08_Catg_code C09_Grade C10_Job_code H10_Primary_Tag H10_Effective_Date_From H10_Effective_Date_To H10_Rep_To_EmpNum H10_Rep_To_Asgn_No C01_Asgn_Reason_Type C01_Asgn_Reason_Subtype H10_Base_Salary C58_Rate_frequency C58_Pay_frequency H10_Currency H10_Amt_Per_Flag H10_Increase_Amt H40_Emp_Status1 H40_Emp_Status2 H40_Emp_Status3 H40_Emp_Status4 H40_Emp_Status5 H10_Dummy1 H10_Dummy2 User_id Modified_date Time_Stamp H40_Emp_Status6 H40_Emp_Status7 H40_Emp_Status8 H10_Ben_Union H10_Ben_Affiliation
--------------------- ----------------- ----------- ----------------- ----------------- ---------------- ------------- --------- ------------ --------------- ------------------------------------------------------ ------------------------------------------------------ ----------------- ------------------ -------------------- ----------------------- ------------------------------ ------------------ ----------------- ------------ ---------------- ------------------------------ --------------- --------------- --------------- --------------- --------------- ---------- ---------- ------- ------------------------------------------------------ ------------------ --------------- --------------- ------------------------- ------------- -------------------
RSWMLNJA BANG 000772 1 CUT HLR DWRK HLR C Y 2006-07-01 00:00:00.000 2006-07-04 23:59:59.000 NULL NULL SAL NONE 86.00000000 DAY MON INR NULL NULL NULL NULL NULL NULL NULL NULL NULL 9 2006-08-11 13:19:36.733 0x00000000000CEBA0 NULL NULL NULL NULL NULL
RSWMLNJA BANG 000772 1 CUT TLR DWRK TLR C Y 2006-07-05 00:00:00.000 NULL NULL NULL SAL NONE 86.50000000 DAY MON INR NULL NULL NULL NULL NULL NULL NULL NULL NULL 9 2006-08-11 13:19:36.733 0x00000000000CEBA1 NULL NULL NULL NULL NULL

(2 row(s) affected)

Please help me
thanks and regards
KK

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-28 : 03:17:07

Somthing like this


Select * from H10_Empasgn a Where H01_Emp_Num = '000772' and 
a.H10_Effective_Date_FROM = ( Select Max(H10_Effective_Date_FROM) From 
H10_Empasgn b where a.H01_Emp_Num = b.H01_Emp_Num)

Chirag

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-28 : 03:50:23

it's working thanks Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources