查询计算会计年度的预付款？ [英] Query to calculate advance in fiscal year ?

问题描述

我有两张表Employee和Advance。每个员工在一个会计年度可以提前两次提前工资。财政年度为7月1日至6月30日。现在我必须写一个查询来验证一名员工是否已在当前会计年度中提前两次工资，然后他是不符合条件进一步提前，如果他没有提前两次，那么他有资格进一步提前。我该怎么写呢我有这样的表格

员工（empId，姓名）

Advance（AdvanceId，empId，AdvanceDate）。

I have two tables Employee and Advance. Each employee can take advance salary twice in a fiscal year. Fiscal year is from 1st of July to 30th June. Now I have to write a query to verify if an employee has taken advance salary in current fiscal year twice then he is "Not Eligible" for further advance and if he hasn't take advance twice then he is "Eligible" for further advance. How can I write it. I have tables like this
Employee(empId, Name)
Advance(AdvanceId, empId, AdvanceDate).

推荐答案

试试这个，你可以根据你的要求进行调整。

Try this, you can tweak as per your requirement.

DECLARE @AdvancePayment TABLE
(
	EMPLOYEEID INT,
	RequestedOn DATETIME
)

INSERT INTO @AdvancePayment(EMPLOYEEID, RequestedOn)
SELECT 1, '01-Sep-2015'
UNION ALL
SELECT 1, '07-feb-2016'
UNION ALL
SELECT 2, '07-feb-2016'
;


with EmployeeAdvance(EmployeeId, TOTAL_ADVANCE, FiscalYear)
as
(
	select EmployeeId, count(EmployeeId) TOTAL_ADVANCE, year(MIN(RequestedOn)) FiscalYear
	from @AdvancePayment
	where RequestedOn BETWEEN '01-Jul-2015' AND '30-Jun-2016'
	group by EmployeeId
)
select	--e.FullName, 
        FiscalYear, 
	case when ea.TOTAL_ADVANCE > 1 then 'Not Eligible' else 'Eligible' end as Status
from	EmployeeAdvance ea 
		--INNER JOIN Employee e on ea.EmployeeID = e.EmployeeID

这样的事情应该有效：

Something like this should work:

DECLARE @Today date = GetUtcDate();
DECLARE @MonthStart date, @YearStart date, @YearEnd date;

SET @MonthStart = DateAdd(month, DateDiff(month, 0, @Today), 0);
SET @YearStart = DateAdd(month, 7 - Month(@MonthStart), @MonthStart);
If Month(@MonthStart) < 7 SET @YearStart = DateAdd(year, -1, @YearStart);
SET @YearEnd = DateAdd(day, -1, DateAdd(year, 1, @YearStart));

WITH EmployeeAdvance As
(
    SELECT
        EmployeeId,
        Count(1) As NumberOfAdvances
    FROM
        AdvancePayment
    WHERE
        RequestedOn Between @YearStart And @YearEnd
    GROUP BY
        EmployeeId
)
SELECT
    E.EmployeeId,
    E.FullName,
    CASE
        WHEN A.NumberOfAdvances > 1 THEN 'Not Eligible'
        ELSE 'Eligible'
    END As Status
FROM
    Employee As E
    LEFT JOIN EmployeeAdvance As A
    ON A.EmployeeId = E.EmployeeId
;

这篇关于查询计算会计年度的预付款？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！