Click here to Skip to main content
15,903,033 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#
Dear,

I have leave table

The Fields are

EmpNo
EmpName
LeaveStart
LeaveEnd


I want to show the leave taken by year.


i use datediff(dd,LeaveStart,LeaveEnd) it will shows the no. of days have taken.

but i want to show yearly wise.

E.g

2014 - 10 days
2015 - 16 days


how & where i can use year function becoz have leavestart & leaveend

suppose if employee taken a leave

LeavStart- 28-Dec-2015
LeaveEnd - 05-Jan-2016

then result should

2015 - 4 days
2016 - 5 days

then how to use query.

please help on this

Thanks
Basit.


What I have tried:

not yet tried anything, searching only till now
Posted
Updated 5-Apr-16 23:52pm
Comments
Sergey Alexandrovich Kryukov 6-Apr-16 3:41am    
Software is not developed by searching. Just apply logical reasoning.
—SA
basitsar 6-Apr-16 3:55am    
Yes dear searching means doing logic. may be need to get every period start and end date and minus from that
OriginalGriff 6-Apr-16 4:18am    
Start by defining exactly what you want.
For example, if my leave starts on Dec 23rd, and I return to work on Jan 3rd (as will be normal for many in the UK this Christmas) what year is that in?
When you have that all decided, it's pretty easy to do this with a GROUP BY clause.
basitsar 6-Apr-16 4:23am    
Dear,
Thanks for reply, but see as i mention, leavestart and leaveend can be anything. if suppose leavestart and leaveend at same year then no problem i will get year(leaveend) and group by this is easy

but if
LeavStart- 28-Dec-2015
LeaveEnd - 05-Jan-2016

then result should

2015 - 4 days
2016 - 5 days

here if i took same query year(leaveend) and group then result

2016 - 9 days that's wrong.
need to be seprate.

Thanks
Basit.
Sergey Alexandrovich Kryukov 6-Apr-16 11:01am    
You really need to show what you have tried. In code. This would be the simplest way.
—SA

1 solution

This is not going to be the best looking query but here are some things to get you started.

Before attempting to put it into a query for your table use an IF[^] statement to map out what you want to happen. For example:

SQL
declare @LeavStart date = '28-Dec-2015'
declare @LeaveEnd date =  '05-Jan-2016'

if [the years are the same]
	select datediff(dd, @LeavStart, @LeaveEnd)
else
begin
	declare @EndStartYear date -- this will be the last day of the first year
	declare @StartEndYear date -- this will be the first day of the 2nd year

	[Calculate the value of @EndStartYear]
	[Calculate the value of @StartEndYear]

	select datepart(yy, @LeavStart), datediff(dd, @LeavStart, @EndStartYear) + 1
	union
	select datepart(yy, @LeaveEnd), datediff(dd, @StartEndYear, @LeaveEnd) + 1
end

I've left the bits between the square brackets for you to do, but you may find these links useful
DATEPART (Transact-SQL)[^]
Some Common Date Routines - SQLServerCentral[^]

Getting that into a single query against your table is more difficult because of the UNION that is sometimes required. I ended up doing something like this with a Common Table Expression[^]
SQL
insert into emp values
('28-Dec-2015','05-Jan-2016'),
('28-Dec-2015','31-Dec-2015'),
('21-Dec-2014','31-Mar-2016')

;with results as -- this is defining my CTE
(
    select empId, LeaveYear = datepart(yy, LeavStart), 
	Y1 = CASE WHEN [the years are the same] THEN
	     [The difference between the dates]
	ELSE
	     [The difference between the start date and the end of that year]
	END
    FROM emp

    UNION

	select empId, datepart(yy, LeaveEnd), 
	     Y1 = CASE WHEN [the years are the same] THEN NULL -- Note!
	ELSE
	     [The difference between the start of the 2nd year and the end date]
	END 
	FROM emp
)
select * from results where Y1 IS NOT NULL
ORDER BY EmpId, LeaveYear

Note the use of the NULL value so that only one row is returned when the dates are in the same year. Watch out for having to add +1 to make the dates inclusive.
And have a look at what happens for my sample data where empId = 3. You may need to sort that out as well.
These are the results I got:
1	2015	4
1	2016	5
2	2015	3
3	2014	11
3	2016	91

If you want them to look like this instead
1	2015	4   2016	5
2	2015	3
3	2014	11  2016	91

Then you will find this link useful - Simple Way To Use Pivot In SQL Query[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900