Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate contract days in financial year

Author  Topic 

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-02-04 : 08:34:59
I have a table with 3 fields: Contract_No, Start_Date, End_Date. I need to calculate how many of the days between the Start and End dates fall within each financial year (FY) beginning 1st April and ending 31st March.

So for example if the Contract Start_Date is 26/01/2012 and the End_Date is 20/05/2012 or is null then the number of days for 2012 FY is 49 (counting from 01/04/2012 to 20/05/2012).

If the End_Date is null for the same contract, then the number of days for 2012 FY would be 365.

Since the contract period from Start_Date to End_Date might span more than one or even several FY I need to be able to show this in columns seperately for each FY. What is the best way of achieving this?

Many thanks
Martyn

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-04 : 08:54:16
Post some sample data.


Declare @d1 Date = 01/04/2012 -- change start period
Declare @d2 Date = 31/03/2013 -- change end period
Set Nocount On;
Select Contract_No,
Start_Date,
End_Date,
DATEDIFF(d, @d1, @d2) days_between,
Year(Start_Date) start_year,
Year(End_Date) end_year
From mytable


We are the creators of our own reality!
Go to Top of Page

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-02-04 : 09:17:13
Here is some sample data, some of it spans the financial year start and ends, and although not shown, some End_Dates could be null.

The first line I would want to see columns as:

FY2011 - 90 days
FY2012 - 365 days
FY2013 - 106 days



Contract_No          Start_Date              End_Date                Total_Days
-------------------- ----------------------- ----------------------- -----------
01-002441 2012-01-01 00:00:00 2013-07-16 00:00:00 562
01-002965 2012-01-01 00:00:00 2013-07-26 00:00:00 572
01-001059 2012-01-01 00:00:00 2013-08-01 00:00:00 578
033739 2012-01-01 00:00:00 2013-08-05 00:00:00 582
033739 2012-01-01 00:00:00 2013-08-05 00:00:00 582
033739 2012-01-01 00:00:00 2013-08-05 00:00:00 582
01-003918 2012-01-11 00:00:00 2013-08-07 00:00:00 574
01-003885 2012-01-16 00:00:00 2013-08-16 00:00:00 578
01-003885 2012-01-16 00:00:00 2013-08-16 00:00:00 578
032372 2012-01-01 00:00:00 2013-08-23 00:00:00 600
01-000180 2012-01-01 00:00:00 2013-08-27 00:00:00 604
028548 2012-01-01 00:00:00 2013-08-30 00:00:00 607


Hope this makes sense.

Thanks
Martyn
Go to Top of Page

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-02-04 : 09:34:24
Forgot to say, ideally I would like to create a view from this.

I do have a calendar table set up complete with the fiscal years, but I've never used it. Would it help in getting my desired results? If so, how would I use it in this situation?


Thanks
Martyn
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-09 : 11:56:12
Did you get sorted on this I have been out of the office for a bit.

To simply get the number of days between start and end dates by contract_no you can do this:

SELECT contract_no, 'FY' + convert(varchar(10),year(start_date)) FY, SUM(DATEDIFF(day, Start_Date, End_Date)) AS totaldays
FROM #testdays
WHERE Start_Date >= '2012-04-01' AND end_date <= '2013-03-31' OR end_date IS NULL
GROUP BY contract_no, year(start_date)


you can change the dates or add more code for different result sets into a case statement to combine years based on date variables.

Other code examples:

declare @startfy2012 date = '2012-04-01'
declare @endfy2012 date = '2013-03-31'
declare @startfy2013 date = '2013-04-01'
declare @endfy2013 date = '2014-03-31'


--get totals per contract_no
--select [contract_no],[start_date], [end_date],
--datediff(d, start_date, end_date) daysbetween
--from #testdays
--where [start_date] >= @startfy2012
--and [end_date] <= @endfy2012

-- get total for year
select 'FY' + convert(varchar(10),datepart(year,@startfy2012)) FY,
sum(datediff(d, start_date, end_date)) daysbetween
from #testdays
where [start_date] >= @startfy2012
and [end_date] <= @endfy2012

Creating a view is the easy part once you get the code doing what you want it to do.
We are the creators of our own reality!
Go to Top of Page

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-02-11 : 09:04:15
No problem, I've only just been able to come back to this. I will try some of your examples and see how they work, then look at the view part of it afterwards.

Many thanks for your help, will post how I get on with it.


Martyn
Go to Top of Page

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-03 : 15:47:13
I've still not made much progress with this question. I do have a query that seems to give me what I want, but can anyone help me simplify this as I'm certain this is not the best way, plus this only provides data on 2 years, whereas I might want to report on up to 10 years at a time.

To reiterate, I want to see for a given contract with start and end dates: whether any part of the hire fell within a given FY (Apr to Mar), and how many days hire there were that fell in that FY.

select contract_no
,start_date
,end_date
,case when (start_date >= '2012-04-01' and end_date < '2013-04-01') then 1
when (start_date >= '2012-04-01' and end_date is null) then 1
when (start_date < '2012-04-01' and end_date is null) then 1
when (start_date < '2012-04-01' and end_date between '2012-04-01' and '2013-04-01') then 1
when (start_date < '2012-04-01' and end_date > '2013-04-01') then 1
else 0
end [FY12/13],

case when (start_date >= '2012-04-01' and end_date < '2013-04-01') then datediff(d,start_date, end_date)
when (start_date >= '2012-04-01' and end_date is null) then datediff(d, start_date, '2013-04-01')
when (start_date < '2012-04-01' and end_date is null) then datediff(d, '2012-04-01', '2013-04-01')
when (start_date < '2012-04-01' and end_date between '2012-04-01' and '2013-04-01') then datediff(d,'2012-04-01', end_date)
when (start_date < '2012-04-01' and end_date > '2013-04-01') then datediff(d,'2012-04-01','2013-04-01')
else 0
end [FY12/13 Days],

case when (start_date >= '2013-04-01' and end_date < '2014-04-01') then 1
when (start_date >= '2013-04-01' and end_date is null) then 1
when (start_date < '2013-04-01' and end_date is null) then 1
when (start_date < '2013-04-01' and end_date between '2013-04-01' and '2014-04-01') then 1
when (start_date < '2013-04-01' and end_date > '2014-04-01') then 1
else 0
end [FY13/14],

case when (start_date >= '2013-04-01' and end_date < '2014-04-01') then datediff(d,start_date, end_date)
when (start_date >= '2013-04-01' and end_date is null) then datediff(d, start_date, '2014-04-01')
when (start_date < '2013-04-01' and end_date is null) then datediff(d, '2013-04-01', '2014-04-01')
when (start_date < '2013-04-01' and end_date between '2013-04-01' and '2014-04-01') then datediff(d,'2013-04-01', end_date)
when (start_date < '2013-04-01' and end_date > '2013-04-01') then datediff(d,'2013-04-01','2014-04-01')
else 0
end [FY13/14 Days]

from contracts


And this is how I would like to see the results with some sample data:

contract_no start_date              end_date                FY12/13     FY12/13 Days FY13/14     FY13/14 Days
----------- ----------------------- ----------------------- ----------- ------------ ----------- ------------
12345 2011-05-01 00:00:00 2012-02-01 00:00:00 0 0 0 0
12346 2011-05-01 00:00:00 2012-07-01 00:00:00 1 91 0 0
12347 2011-05-01 00:00:00 NULL 1 365 1 365
12348 2011-05-01 00:00:00 2011-12-31 00:00:00 0 0 0 0
12349 2011-05-01 00:00:00 2013-12-31 00:00:00 1 365 1 274



Many thanks
Martyn

Go to Top of Page
   

- Advertisement -