Query to calculate advance in fiscal year ?

Question

1.00/5 (1 vote)

See more:

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).

Posted 8-Jan-16 0:21am

touseef4pk

Add a Solution

Comments

Herman<T>.Instance 8-Jan-16 6:34am

What have you tried?

touseef4pk 8-Jan-16 6:43am

with EmployeeAdvance
as
(
select year(dateadd(M,-6,RequestedOn)) as FiscalYear, EmployeeId
from AdvancePayment
where RequestedOn is not null
)
select e.FullName, FiscalYear, case when count(ea.EmployeeID) > 1 then 'Not Eligible' else 'Eligible' end as Status,
count(ea.EmployeeID)
from Employee e join EmployeeAdvance ea on
e.EmployeeID = ea.EmployeeID

group by ea.FiscalYear, ea.EmployeeID,e.FullName.

But its giving me duplicate records. I need records for current fiscal year only.

Tomas Takac 8-Jan-16 7:33am

Update your question with the code and format it properly. Use Improve question.

2 solutions

Solution 1

Try this, you can tweak as per your requirement.

SQL

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

Posted 8-Jan-16 2:44am

_Asif_

Comments

Maciej Los 8-Jan-16 10:01am

5ed!

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)

Richard Deeming · Accepted Answer · 2016-01-08T02:48:00

Something like this should work:

SQL

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
;