Query to get records of the employees who has take advance in fiscal year ?

Question

0.00/5 (No votes)

See more:

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 ?

SQL

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.

Posted 18-Jan-16 0:38am

touseef4pk

Updated 18-Jan-16 1:31am

jaket-cp

v4

Add a Solution

Comments

OriginalGriff 18-Jan-16 6:55am

What have you tried?
Where are you stuck?

touseef4pk 18-Jan-16 6:57am

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.

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)

jaket-cp · Accepted Answer · 2016-01-18T01:34:00

Solution 1

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

SQL

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.

Posted 18-Jan-16 1:34am

jaket-cp

Updated 18-Jan-16 2:11am

v3

Comments

_Asif_ 18-Jan-16 7:57am

Tested version of your solution.

DECLARE @EMP TABLE
(
EmpId int,
Name varchar(10)
)
;

DECLARE @Advance TABLE
(
advanceId int,
empId int,
AdvanceDate DateTime
)
;

INSERT INTO @EMP (EmpId, Name)
SELECT 1, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 3, 'C'
;

INSERT INTO @Advance (advanceId, empId, AdvanceDate)
SELECT 1, 1, '01-Jan-2015'
UNION ALL
SELECT 1, 1, '01-Jan-2016'
UNION ALL
SELECT 3, 1, '01-Feb-2016'
UNION ALL
SELECT 4, 2, '01-Jan-2016'
;

with AdvanceCnt as (
select
EmpId,
AdvanceDate,
ROW_NUMBER() over(partition by EmpId order by AdvanceDate) as rowid
from @Advance
WHERE AdvanceDate between '01-Jun-2015' AND '31-Jul-2016'
)
SELECT E.EMPID,
E.NAME, AC1.AdvanceDate as AdvanceDate1, AC2.AdvanceDate as AdvanceDate2
from @EMP E
LEFT OUTER JOIN AdvanceCnt AC1 on E.EMPID = AC1.EMPID AND AC1.ROWID = 1
LEFT OUTER JOIN AdvanceCnt AC2 on E.EMPID = AC2.EMPID AND AC2.ROWID = 2

_Asif_ 18-Jan-16 7:57am

+5

jaket-cp 18-Jan-16 8:00am

thanks, and also thanks for testing :)

touseef4pk 18-Jan-16 8:21am

Thanks. This is exactly what I require.

jaket-cp 18-Jan-16 8:25am

you are welcome, glad to be of help.

touseef4pk 18-Jan-16 8:30am

How can I show column of fiscal year along with the resultset so that each result should show data in each fiscal year?

jaket-cp 18-Jan-16 8:34am

What is the fiscal year?
Can it be derived from AdvancePayment.RequestedOn?

touseef4pk 18-Jan-16 8:36am

Fiscal year means current financial year Yes we can derive it from RequestedOn like year(dateadd(M,-6,RequestedOn))

jaket-cp 18-Jan-16 8:39am

okay if that is the case.
Add an additional column, something like this:
year(dateadd(M,-6,ac1.RequestedOn)) FiscalYear

Maciej Los 18-Jan-16 16:12pm

Looks perfect! A 5!

jaket-cp 19-Jan-16 5:02am

thanks :)