查询以获取在会计年度中提前完成的员工的记录？ [英] Query to get records of the employees who has take advance in fiscal year ?

问题描述

我必须写一个查询，员工可以在一年内提前两次。现在我必须向所有在财政年度（7月到6月）提前一两次提前的员工展示。



我有这样的桌子

Empoyee（EmpId，名字）

Advance（advanceId，AdvanceDate，empId ）。



现在我必须以下列输出的形式显示列。



empId，Name，第1预付日期，第2预付日期。



我该怎么办？

  SELECT  e.EmployeeId，e.FullName，ap.RequestedOn 来自员工e  join  AdvancePayment ap  on  e.EmployeeId = ap.EmployeeId 
  WHERE  ap.EmployeeID  IN  
（ SELECT  EmployeeId  FROM  AdvancePayment 
  GROUP   BY  EmployeeID  HAVING  COUNT（年（dateadd（M，-6，RequestedOn）））>  1 ）

这给了我在一个财政年度中提前两次提前工作的所有员工的记录。但我无法得到如何获得以下输出的记录

empId，名称，第一个提前日期，第二个提前日期。

解决方案

< blockquote>如果我得到你的要求，这是一种可行的方法。

   AdvanceCnt  as （
  select  
 EmployeeId，
 RequestedOn，
 row_number  over （ partition   by  EmployeeId  order   by  RequestedOn）rowid 
 来自 AdvancePayment 
 其中 RequestedOn 
   -   请求在此处过滤日期 
）
 选择 
 e.EmployeeId e mpId，
 e.FullName Name，
 ac1.RequestedOn [1st advance  date ]，
 ac2.RequestedOn [2nd advance  date ] 
 来自员工e 
  inner  加入 
（选择 * 从 AdvanceCnt 其中 rowid =  1 ）ac1 
  on  e.EmployeeId = ac1.EmployeeId 
  left   join  
（选择 * 来自 AdvanceCnt 其中 rowid =  2 ）ac2 
  on  e.EmployeeId = ac2.EmployeeId 
; 

查询尚未经过测试，但希望它能够帮助你。

Hi,

I have to write a query where an employee can take advance twice in a year. Now I have to show all the employees who has taken advance one time or two times in fiscal year(July to June).

I have tables like this
Empoyee(EmpId,Name)
Advance(advanceId,AdvanceDate,empId).

Now I have to show columns in form of following output.

empId, Name, 1st advance date, 2nd advance date.

How can I do that ?

SELECT e.EmployeeId, e.FullName,ap.RequestedOn from Employee e join AdvancePayment ap on e.EmployeeId = ap.EmployeeId
WHERE ap.EmployeeID IN 
(SELECT EmployeeId FROM AdvancePayment 
GROUP BY EmployeeID HAVING COUNT (year(dateadd(M,-6,RequestedOn))) > 1)

This gives me records of all the employees who has taken advance twice in a fiscal year. But i am unable to get that how can I get records with following output
empId, Name, 1st advance date, 2nd advance date.

解决方案

If I get what you are asking, here is a possible way of doing it.

with AdvanceCnt as (
	select 
		EmployeeId,
		RequestedOn,
		row_number over(partition by EmployeeId order by RequestedOn) rowid
	from AdvancePayment
	where RequestedOn
	-- do RequestedOn date filtering here
)
select
	e.EmployeeId empId,
	e.FullName Name,
	ac1.RequestedOn [1st advance date],
	ac2.RequestedOn [2nd advance date]
from Employee e
inner join
	(select * from AdvanceCnt where rowid = 1) ac1
	on e.EmployeeId = ac1.EmployeeId
left join
	(select * from AdvanceCnt where rowid = 2) ac2
	on e.EmployeeId = ac2.EmployeeId
;

The query has not been tested, but hopefully it will help you out.

这篇关于查询以获取在会计年度中提前完成的员工的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！