Financial Date

[dizzy880]

Wondering if someone can help me?

I am looking to get the Fincancial week Start date

My Fin years Starts 01 July however if 01 Jul isnt a Sunday
then I need to get the Date the Sunday falls on

eg June 26 2005 is the first day of the week in the fin year
June 27 2004
June 29 2003

this is what I have so far


SELECT open_date As "Booking Date",
DATEPART(dw,open_date)as " Week Day No",
sum(Invoiced)Invoiced,
sum(commission) commission,
count(Ledgerno) Total_Booking,

DATENAME(mm,case datepart(mm,open_date)
when 1 then datepart(mm,open_date)
else open_date - datepart(mm,open_date)
end) as "Financial Week Name",

--first_july_fin_year
CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME) first_july_fin_year,

--fin_year_start
CASE DATEPART(dw,CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME))
WHEN 1 THEN
CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME)
ELSE
CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME)
- DATEPART(dw,CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME)) + 1
END fin_year_start,


--weeks_since_fin_year_start
DATEDIFF(WEEK,
CASE DATEPART(dw,CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME))
WHEN 1 THEN
CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME)
ELSE
CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME)
- DATEPART(dw,CAST('01-JUL-'+ CAST(CASE DATEPART(m,open_date)
WHEN 7 THEN DATEPART(yy,open_date)
WHEN 8 THEN DATEPART(yy,open_date)
WHEN 9 THEN DATEPART(yy,open_date)
WHEN 10 THEN DATEPART(yy,open_date)
WHEN 11 THEN DATEPART(yy,open_date)
WHEN 12 THEN DATEPART(yy,open_date)
WHEN 1 THEN DATEPART(yy,open_date)-1
WHEN 2 THEN DATEPART(yy,open_date)-1
WHEN 3 THEN DATEPART(yy,open_date)-1
WHEN 4 THEN DATEPART(yy,open_date)-1
WHEN 5 THEN DATEPART(yy,open_date)-1
WHEN 6 THEN DATEPART(yy,open_date)-1
ELSE 1 END AS VARCHAR)AS DATETIME)) + 1
END,open_date) + 1 AS "Financial Week"
FROM ledger
group by open_date
order by 1

 

[Walid]

This situation is calling for a look up table instead of complex and error prone calculations especially with T-SQL. If you look at Date and Time Properties in Windows you will find the values you are looking for until 2099 which can help you build your look up table.

If you think that your software will be around in 2100, just let me know, I remember I have an algorithm in one of these books on one of these shelves of one of these book cases that can calculate your result, I will look for it.


Walid Magd

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[earthandfire]

You could try something like this:

declare @finyearstart datetime
declare @finweekstart datetime
declare @theyear int

set @theyear = 2000
while @theyear <=2099
begin
	set @finweekstart = 
				dateadd(d, 
					-(datepart(dw, convert(varchar(4),@theyear) + '0701') - 1), 
					convert(varchar(4),@theyear) + '0701')
	print datename(dw, @finweekstart) + ', ' + convert(varchar(10), @finweekstart, 103)
	set @theyear = @theyear + 1
end

Hope this helps.

 

[dizzy880]

Walid

thanks I will give this a go