Calculate contract days in financial year

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!

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

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

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!

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources