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:
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
declare @StartEndYear date
[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[
^]
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
(
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
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[
^]