Sunday, 24 March 2013

Creating a Calendar View

I like to put commonly used scripts into Views/Stored Procedures etc. It means I dont have to re-write out the logic each time, I know that there has been a consistent use of the logic and if there is a change needed to the logic I make it once rather than many times across all of my scripts/reports etc. Yeah seems obvious to do but Ive seen a lot of developers who dont bother.

Now this is view is related of course to my previous post Creating A Calendar Table but I find that having an independent view that you can use in Reports or reused SQL Scripts very helpful.

What better way to set default date values than referring to a consistent view AND if for some reason there is a change in regards to the definition of say the last day of the Previous Month - maybe it is now the last Monday for example instead of the last actual day you can change it within the view once and all of your reports/scripts are instantly updated. No more having to go through them all updating the logic.

Depending on where you are located you may want to change around a few of these values for example the Financial Years which wont apply to all.



-- =============================================

-- Create View template
-- =============================================
USE
ABC
-- Adjust accordingly

GO
IF
object_id(N'dbo.vDateDetails', 'V') IS NOT NULL


DROP VIEW dbo.vDateDetails
GO
CREATE
VIEW dbo.vDateDetails AS
SELECT
getdate
() AS [Now]
,

DATEADD(d, DATEDIFF(d,0,getdate()), 0) as Today
,

DATEADD(d, DATEDIFF(d,0,getdate()), -1) as Yesterday
,

CONVERT (varchar, getdate(), 112) As IntDate
,

DAY(getdate()) As [DayOfMonth]
,

DATEPART(dayofyear, getdate()) as [DayOfYear]
,

Month(getdate()) as MonthOfYear
,

DATENAME(month, getdate()) AS [MonthName]
,

YEAR(getdate()) as [Year]
,

case when Month(getdate()) > 6 then
right(cast(YEAR(getdate()) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)
else
right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)
end as CurrentFinancialYear
,

case when Month(getdate()) > 6 then
right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)
else
right(cast(YEAR(dateadd(yy, -2, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2)
end as PreviousFinancialYear
,

case when Month(getdate()) > 6 then
right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 2, getdate())) as varchar(4)), 2)
else
right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)
end as NextFinancialYear
,

DATEPART(quarter, getdate()) as CalQuarter
,

DATEPART(week, getdate()) as WeekOfYear
,

DATEPART(weekday, getdate()) as [Weekday]
,

DATENAME(weekday, getdate()) AS [WeekDayName]
,

DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) as MondayOfWeek
,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) as LastDayOfPreviousMonth
,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) as LastDayOfCurrentMonth
,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) as LastDayOfNextMonth
,

DATEADD(mm, DATEDIFF(m,0,getdate()),0) as FirstDayOfCurrentMonth
,

DATEADD(mm, DATEDIFF(m,0,getdate())+1,0) as FirstDayOfNextMonth
,

DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) as FirstDayOfPreviousMonth
,

DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as FirstDayOfCurrentQuarter
,

DATEADD(s,-1,DATEADD(qq, DATEDIFF(q,0,getdate())+1,0)) as LastDayOfCurrentQuarter