Feeds:
Posts
Comments

Posts Tagged ‘month’


-- List of days in the week
;WITH WeekNames
AS
(
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate

    UNION ALL 

    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
)
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
as
(
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
)
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
as
(
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
)
SELECT YearNumber
FROM Last10Years
ORDER BY YearNumber DESC 





Read Full Post »

-- Description: Returns a date table within the given date range to return a monthly record that contants for e.g. the start and end of each month in the range
--- Takes a start date and an end date to calculate the month table
-- This is a function to return a Monthly Date Table For Any Two given Dates
--ALTER function [csrt].[udf_All_MonthInDates]
--		(@StartDate datetime, @EndDate datetime)

--returns @MonthInDates table
--(
--	MonthInDatesID int identity,
--	MonthCalled varchar(50),
--	MonthNumber int,
--	MonthYear int,
--	MonthStart datetime,
--	MonthEnd datetime,
--	MonthPeriod varchar (50)
--)
--AS
BEGIN
	---------- Test -------------
	declare @MonthInDates table
	(
		MonthInDatesID int identity,
		MonthCalled varchar(50),
		MonthNumber int,
		MonthYear int,
		MonthStart datetime,
		MonthEnd datetime,
		MonthPeriod varchar (50)
	)

	declare @StartDate datetime, @EndDate datetime
	-- DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) + 1, 0))
	set @StartDate = '25 jan 2011' -- GETDATE()
	set @EndDate = '25 jan 2012'
	----------------------

	/*
		For best results both the @StartDate and @EndDate dates needs to be on the same day or end of the month. This is because so that when date1 is comapred to date2 it is always consistent in terms of days else there will be times where because of different days there could be some months missing 

	*/

	set @StartDate = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@StartDate) + 1, 0))
	set @EndDate = DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,@EndDate) + 1, 0))
	-- select @StartDate, @EndDate	-- test `

	while (@StartDate &lt;= @EndDate)
	begin
		insert into @MonthInDates
		select datename(month,@StartDate)MonthCalled
				, datepart(month,@StartDate) MonthNumber
				, YEAR(@StartDate) MonthYear
				, convert(datetime, '1/' + str(MONTH(@StartDate),2)
					+ '/' + str(YEAR(@StartDate) ,4), 103) MonthStart -- 103 = dd/mm/yyyy
				, DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0, @StartDate) + 1, 0)) MonthEnd
				, left(datename(month,@StartDate),3) + ' - ' + datename(year,@StartDate)MonthPeriod

		set @StartDate = dateadd(month, 1 ,@StartDate)
		print @StartDate -- test `
	end 

		select * from @MonthInDates -- test `
	--return
END

Read Full Post »

-- For use with usp_getMonth (which can be found in the Second Option in the post - http://wp.me/pAchH-4l)

-- The below can be used when in SSRS you only want to show the months which are available for the users to select. This requires the the a parameter which would return the year selected

=iif(Parameters!Year.Value = year(today()), month(today()), 12)

Read Full Post »

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! -- There are two SPs here
--First option:    Returns all month name and number
--==================================================
--create proc dbo.usp_getMonthNameAndNumber
--as
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)
DECLARE @count INT

SET @count = 0

WHILE (@count & lt;12)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1

--Second option:    Returns Month name and number upto the passed month number
--    (rows are restricted to the passed month)
--==================================================
--create proc dbo.usp_getMonth (@RestrictToMonthNumber int = null)
--as
---------====== Test Values ======
DECLARE @RestrictToMonthNumber INT

SET @RestrictToMonthNumber = 6

-------=============================
-- Note: If null is sent then all months are returned
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)

IF @RestrictToMonthNumber IS NULL
	SET @RestrictToMonthNumber = 12

DECLARE @count INT

SET @count = 0

WHILE (@count & lt;@RestrictToMonthNumber)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1 

Related post ( used IN SSRS ) :
http: / / wp.me / pAchH – 4 A

Read Full Post »

/*
Description:    Returns all the Months (& Year) in the given range along with its Start and End Dates
Takes Month number FROM and month number TO and the year to calculate the monthly table
This is a Monthly Date Table For A Single Year
*/
CREATE FUNCTION dbo.udf_ReturnSelectedMonths (
	@MonthFrom INT
	,@MonthTo INT
	,@Year INT
	)
RETURNS @SelectedMonths TABLE (
	ID INT identity
	,MonthName VARCHAR(10)
	,MonthNumber INT
	,MonthYear INT
	,StartDate DATETIME
	,EndDate DATETIME
	)
AS
BEGIN
	---------====== Test Values ======
	--    declare @MonthFrom int, @MonthTo int, @Year int
	--    set @MonthFrom = 3
	--    set @MonthTo = 5
	--    set @Year = 2011
	--    declare @SelectedMonths table
	--    (
	--        ID int identity,
	--        MonthName varchar(10),
	--        MonthNumber int,
	--        MonthYear int,
	--        StartDate datetime,
	--        EndDate datetime
	--    )
	-------=============================
	DECLARE @NoOfMonths INT

	SET @NoOfMonths = (@MonthTo - @MonthFrom) + 1

	-------- Construct the starting month selected to construct the Start and End Dates of all the months
	DECLARE @PassedDate DATETIME

	SET @PassedDate = convert(DATETIME, '1/' + str(@MonthFrom, 2) + '/' + str(@year, 4), 103) -- 103 = dd/mm/yyyy

	DECLARE @count INT

	SET @count = 0

	WHILE (@count < @NoOfMonths)
	BEGIN
		INSERT @SelectedMonths
		SELECT datename(month, DATEADD(m, @count, @PassedDate))
			,-- MonthName
			datepart(month, DATEADD(m, @count, @PassedDate))
			,-- MonthNumber
			--datepart(year,DATEADD(m, @count, @PassedDate)), --MonthYear
			@Year
			,-- The year is alwasy fixed so there is no point in deucing that like above (this wil reduce the query time)
			DATEADD(m, @count, @PassedDate) [Beginning of previous month]
			,-- StartDate
			DATEADD(ms, - 3, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, @count, @PassedDate)) + 1, 0)) -- EndDate

		SET @count = @count + 1
	END

	-----------====== Test Values ======
	--    select * from @SelectedMonths
	---------=============================
	RETURN
END
	/*---------====== Test ======

Run the below statement once the above function is created to test

select * from udf_ReturnSelectedMonths (3, 5, 2011)

---------=============================*/

Follow the link below to find out how to use this function.

http://wp.me/pAchH-3T

Read Full Post »

/*

This Function provides all the Month Year and data that can be joined with another table for trend analysis

*/
-------====== Test Values ======
DECLARE @MonthFrom INT
	,@MonthTo INT
	,@Year INT
	,@SPName VARCHAR(100) -- Parameters

SET @MonthFrom = 1
SET @MonthTo = 3
SET @Year = 2011

-----=============================
/*
to use the function you have to make sure the table it will be joined to has a date field to be joined.
The date field can either hold
i) only the first day of the month -- If the row is coming from an aggregated source then you might nned to create this field and
by defualt only store the first day of the month

ii) or any day of the month - Depending on what this date column holds the below join can be altered for optimisation

*/
DECLARE @main TABLE (
	ID INT identity
	,DateToJoin DATETIME -- These has to
	)

INSERT @main
VALUES ('1 jan 2011')

INSERT @main
VALUES ('21 jan 2011')

INSERT @main
VALUES ('6 feb 2011')

SELECT * --MonthName, MonthNumber, MonthYear, StartDate, EndDate, Dimension1, Measure1--, Dimension2, Measure2, Dimension3, Measure3
FROM udf_ReturnSelectedMonths(@MonthFrom, @MonthTo, @Year) sm
INNER JOIN @Main mn
	-- on sm.StartDate = mn.DateToJoin ---- Use this is the start of the month date is availalbe else use the below
	ON mn.DateToJoin BETWEEN sm.StartDate
		AND sm.EndDate -- This join can always be used regardless whether The first of the mont his stored or not
ORDER BY 1
	-- select * from udf_ReturnSelectedMonths (3, 5, 2011)

How to contruct the udf_ReturnSelectedMonths:

http://wp.me/sAchH-254

Read Full Post »

--- Just a note ---
SELECT @@Datefirst -- By Default this is 7 which denotes Sunday and the values for Monday is 1 whihc cat be set if needed like below:
 -- set Datefirst 1
 ---------------

-- With Datepart we can retrieve the integer value of any part of a datetime value:
--    year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.
-- with datename we can retrieve its name / string value
-- Example:
SELECT datepart(week, getdate()) WeekNumber
 ,datepart(weekday, getdate()) WeekDayNumber
 ,datepart(month, getdate()) MonthDayNumber

--Week does not have a name so its sting value is returned
SELECT datename(week, getdate()) WeekName
 ,datename(weekday, getdate()) WeekDayName
 ,datename(Month, getdate()) MonthDayName

Result:

Read Full Post »

Older Posts »