Feeds:
Posts
Comments

Posts Tagged ‘year’


-- 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 





Advertisements

Read Full Post »

-- Returns the Week Day Names and its correponding Week Number (taking Sunday as 1)
--==================================================
--alter proc dbo.usp_getWeekDayNames
--as
SET DATEFIRST 1 -- Monday is set to be the first day of the week

DECLARE @c INT

SET @c = 1

DECLARE @WeekDays TABLE (
	WeekDayNumber INT
	,WeekDayName VARCHAR(12)
	)

WHILE (@c & lt;= 7)
BEGIN
	INSERT @WeekDays
	SELECT datepart(weekday, dateadd(day, @c, getdate())) WeekDayNumber
		,datename(weekday, dateadd(day, @c, getdate())) WeekDayName

	SET @c = @c + 1
END

SELECT *
FROM @WeekDays
ORDER BY 1

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 »

-- TSQL -- Fractions (or float) not being returnedTSQL -- Counting Nulls »TSQL, SSRS -- Date Manipulation
--July 19, 2010 by Usama 

SELECT getdate() Date1, getdate() + 1 Date2
     , DATEDIFF(day,GETDATE() + 1, getdate()) NegetiveFigure
	, DATEDIFF(day,GETDATE(), getdate() + 1) PositiveFigure_PlaceTheBiggerOnTheRight

Select dateadd(D, 0, datediff(D, 0, GETDATE())) -- Time Stripped (without any data type conversion for optimized performance)

Select DATEADD(m, DATEDIFF(m,0,GETDATE() )-1, 0) -- Beginning of previous month
select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) , 0)) -- End of previous month

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()) -1, "1900-01-01″) -- Beginning of previous month

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″)) -- End of previous month
*/

-----------------------------------------------------------------

Select DATEADD(m, DATEDIFF(m,0,GETDATE() ), 0) -- Beginning of the month

select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) + 1, 0)) -- End of the month 

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″) -- Beginning of the month 

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today())+1, "1900-01-01″)) -- End of the month
*/ 

------------------------

-- Converting String to Datetime

declare @SelectedMonth int = 1, @year int = 2010 

-- Replace is not needed in the below line but is just illustrated if you want a leading 0 when the month is a single digit
declare @date datetime = convert(datetime, '28/' + replace(str(@SelectedMonth,2),' ‘,'0′) + ‘/' + str(@year ,4), 103)
select @date '


-------------------------------------- 

select 
DATENAME(weekday , getdate())
,  DATEADD(ms,-3,DATEADD(D, DATEDIFF(D,0,GETDATE()) , 0)) -- End of play > Yesterday 
, DATEADD(D, DATEDIFF(D,0,GETDATE())-1 , 0) -- Beginning of play > Yesterday 

Read Full Post »