I want to retrieve records

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have two tables in that first table i.e employye has empid, salary and in another table that is salary increment table i have different salaries for the employee based on the increment date ... Now when i want to process salary i want to retrieve all the records from the employee table in that i want to put a condition that if newsalary exist for the currentmonth or after the incrementdate then select that salary instead of oldone and for other employees if there is no incremnet salary in the salaryincrement table then select oldsalry

select a.ACCODE a.SalBasic,SalCommOT,SalHouseAlw,SalTranAlw,SalTel,SalFoodAlw,SalExpAlw,SalCommGuar,SalHardshipAlw,SalLivingAlw,SalOthAlw,
b.basicnew,b.ExpAlwNew,b.CommoTNew,b.TelNew,b.CGuarNew ,b.FoodAlwNew,
b.TransAlwNew,b.HouseAlwNew,b.OtherAlwNew,b.HardshipAlwNew,b.livingAlwNew,b.dtFrom,a.EmpType,a.EmpOption,a.EmpDepartmentId,a.DtDOJ from stfmast a
left join SalaryIncrement b on a.ACCODE = b.EmpId
where a.empstatus='On Duty' order by ACCODE

am getting the result but when i make latest increment its the first increment itself can anyone help

Posted 28-Jan-15 3:05am

zuber ahmed

Updated 28-Jan-15 3:16am

v2

Add a Solution

Comments

CHill60 28-Jan-15 9:05am

What have you tried?

ZurdoDev 28-Jan-15 9:13am

Where are you stuck?

zuber ahmed 28-Jan-15 9:14am

select a.ACCODE ,a.SalBasic,SalCommOT,SalHouseAlw,SalTranAlw,SalTel,SalFoodAlw,SalExpAlw,SalCommGuar,SalHardshipAlw,SalLivingAlw,SalOthAlw,
b.basicnew,b.ExpAlwNew,b.CommoTNew,b.TelNew,b.CGuarNew ,b.FoodAlwNew,
b.TransAlwNew,b.HouseAlwNew,b.OtherAlwNew,b.HardshipAlwNew,b.livingAlwNew,b.dtFrom,a.EmpType,a.EmpOption,a.EmpDepartmentId,a.DtDOJ from stfmast a
left join SalaryIncrement b on a.ACCODE = b.EmpId
where a.empstatus='On Duty' order by ACCODE

in this am getting the output but if again there is latest increment its taking the first increment

ZurdoDev 28-Jan-15 9:20am

You may have to join to a derived table, one that returns the most recent salary for each employee.

zuber ahmed 28-Jan-15 9:24am

can u help me how to do that .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-01-28T04:06:00

I started this before you posted your table names etc so apologies that none of the names match up. Hopefully you'll be able to map this onto your own tables and columns ...

I created some test data as follows

create table employee
(
  empid int IDENTITY(1,1),
  oldsalary float
  )

create table increment
(
  empid int,
  idate datetime,
  newsalary float
  
  )

insert into employee values(1000)
insert into employee values(2000)
insert into employee values(3000)


insert into increment values(1, '1-JAN-2014', 1200)
insert into increment values(1, '1-JAN-2015',1100)
insert into increment values(2, '1-JAN-2013',1500)

And have decided on a test date of '01-MAR-2013'. Note I deliberately made the salaries reduce rather than increase to demonstrate that it is the date that is important not the maximum salary
So based on the data above I'm expecting

empid   dateused   salary
1       1-JAN-2015 1100
2       (null)     2000
3       (null)     3000

Which can be done as follows

SQL

declare @tdate datetime
SELECT @tdate = '01-MAR-2013'

select e.empid, x.idate, coalesce(x.newsalary, e.oldsalary)
from employee e
left outer join 
   (select empid, max(idate) as mdate from increment 
    where idate >= @tdate group by empid ) t 
    on t.empid = e.empid
left outer join 
   (select empid, idate, newsalary from increment) x 
    on t.empid=x.empid and t.mdate=x.idate

If I change the date to 01-JAN-2013 I get

empid   dateused   salary
1       1-JAN-2015 1100
2       1-JAN-2013 2000
3       (null)     3000