我想要检索记录 [英] I want to retrieve records

问题描述

我在第一张表中有两张表，即雇员有empid，薪水，另一张表是薪资增量表，我根据增加日期为员工提供不同的工资......现在当我想要处理我想要的工资时从employee表中检索所有记录，我想提出一个条件，即如果newsart存在当前月或者在incrementdate之后，那么选择那个工资而不是oldone，如果在salaryincrement表中没有增量工资，则选择其他员工选择oldsalry









选择a.ACCODE a.SalBasic， SalCommOT，SalHouseAlw，SalT​​ranAlw，SalT​​el，SalFoodAlw，SalExpAlw，SalCommGuar，SalHardshipAlw，SalLivingAlw，SalOthAlw，

b.basicnew，b.ExpAlwNew，b.CommoTNew，b.TelNew，b.CGuarNew，b。 FoodAlwNew，

左边加入SalaryIncrement b on a.ACCODE = b.EmpId

其中a.empstatus ='值班'命令由ACCODE





得到的结果但是当我做最新的增量时它的第一个增量本身可以任何人帮助

解决方案

我在你发布你的表名之前就开始了所以道歉没有一个名字匹配。希望你能够将它映射到你自己的表和列......



我创建了一些测试数据如下

 create table employee 
（
 empid int IDENTITY（1,1），
 oldsalary float 
）
 
创建表增量
（
 empid int，
 idate datetime，
 newsalary float 
 
）
 
插入员工价值（1000）
插入员工价值（2000） 
插入员工价值（3000）
 
 
插入增量值（1，'1-JAN-2014'，1200）
插入增量值（1） ，'1-JAN-2015'，1100）
插入增量值（2，'1-JAN-2013'，1500）

并已决定在'01 -MAR-2013'的测试日期。注意我故意让工资减少而不是增加，以证明这是重要的日期而不是最高工资

所以根据上面的数据，我期待

  empid dateused salary  
 1 1-JAN-2015 1100 
 2（null）2000 
 3（null）3000 

可以按如下方式完成

 声明  @tdate   datetime  
  SELECT   @tdate  = '  01-MAR-2013' 
 
 选择 e.empid，x.idate， coalesce （x.newsalary，e.oldsalary）
 来自员工e 
  left   outer   join  
（ select  empid，max（idate） as  mdate 来自增量
 其中 idate> =  @tdate   group   by  empid）t 
  on  t.empid = e.empid 
  left   outer   join  
（ select  empid，idate，newsalary 来自增量）x 
   t.empid = x.empid 和 t .mdate = x.idate 

如果我将日期更改为01-JAN-2013，我会得到

  empid dateused salary  
 1 1-JAN-2015 1100 
 2 1-JAN-2013 2000 
 3（null） 3000 

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

解决方案

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

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

这篇关于我想要检索记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！