Step
1 : Create UDF to display current week date and day – Weekly Calendar.
USE tempdb;
GO
IF EXISTS(SELECT * FROM SysObjects WHERE
NAME = 'DisplayCurrentWeekDays'
AND XTYPE = 'TF')
BEGIN
DROP FUNCTION dbo.DisplayCurrentWeekDays
END
GO
CREATE FUNCTION dbo.DisplayCurrentWeekDays
(@today SMALLDATETIME)
RETURNS
@WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
Purpose: To
return the weekly calendar for the week any date
BEGIN
DECLARE @day INT
SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday,
Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT DATEADD(dd, 1 - @day, @today) Sunday,
DATEADD(dd, 2 - @day, @today) Monday,
DATEADD(dd, 3 - @day, @today) Tuesday,
DATEADD(dd, 4 - @day, @today) Wednesday,
DATEADD(dd, 5 - @day, @today) Thursday,
DATEADD(dd, 6 - @day, @today) Friday,
DATEADD(dd, 7 - @day, @today) Saturday
RETURN
END
GO
Step
2 : Create UDF to display current month date and day – Monthly
USE tempdb;
GO
IF EXISTS(SELECT * FROM SysObjects WHERE
NAME = 'GetSQLcalendar'
AND XTYPE = 'TF')
BEGIN
DROP FUNCTION dbo.GetSQLcalendar
END
GO
CREATE FUNCTION dbo.GetSQLcalendar(@WhatDate SMALLDATETIME)
RETURNS
@WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
BEGIN
Purpose: To
return a calendar for whatever MONTH we want, driven by the date we pick AS a
variable.
DECLARE
@FourWeeksAgo SMALLDATETIME
SET
@FourWeeksAgo = DATEADD(d,-28,@WhatDate)
DECLARE
@ThreeWeeksAgo SMALLDATETIME
SET
@ThreeWeeksAgo = DATEADD(d,-21,@WhatDate)
DECLARE
@TwoWeeksAgo SMALLDATETIME
SET
@TwoWeeksAgo = DATEADD(d,-14,@WhatDate)
DECLARE
@PreviousWeek SMALLDATETIME
SET
@PreviousWeek = DATEADD(d,-7,@WhatDate)
DECLARE @ThisWeek SMALLDATETIME
SET @ThisWeek = (@WhatDate)
DECLARE @NextWeek SMALLDATETIME
SET @NextWeek = DATEADD(d,7,@WhatDate)
DECLARE @LastWeek SMALLDATETIME
SET @LastWeek = DATEADD(d,14,@WhatDate)
DECLARE
@AfterLastWeek SMALLDATETIME
SET
@AfterLastWeek = DATEADD(d,21,@WhatDate)
DECLARE
@TwoAfterLastWeek SMALLDATETIME
SET
@TwoAfterLastWeek = DATEADD(d,28,@WhatDate)
INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday,
Wednesday, Thursday, Friday, Saturday)
SELECT *
FROM
(
SELECT CASE MONTH(calendar.Sunday)
WHEN MONTH(@WhatDate)
THEN calendar.Sunday
ELSE NULL END AS 'Sunday',
CASE MONTH(calendar.Monday)
WHEN MONTH(@WhatDate)
THEN calendar.Monday
ELSE NULL END AS 'Monday',
CASE MONTH(calendar.Tuesday)
WHEN MONTH(@WhatDate)
THEN calendar.Tuesday
ELSE NULL END AS 'Tuesday',
CASE MONTH(calendar.Wednesday)
WHEN MONTH(@WhatDate)
THEN calendar.Wednesday
ELSE NULL END AS 'Wednesday',
CASE MONTH(calendar.Thursday)
WHEN MONTH(@WhatDate)
THEN calendar.Thursday
ELSE NULL END AS 'Thursday',
CASE MONTH(calendar.Friday)
WHEN MONTH(@WhatDate)
THEN calendar.Friday
ELSE NULL END AS 'Friday',
CASE MONTH(calendar.Saturday)
WHEN MONTH(@WhatDate)
THEN calendar.Saturday
ELSE NULL END AS 'Saturday'
FROM
(
SELECT *
FROM dbo.DisplayCurrentWeekDays(@FourWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@ThreeWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@TwoWeeksAgo)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@PreviousWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@ThisWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@NextWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@LastWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@AfterLastWeek)
UNION ALL
SELECT *
FROM dbo.DisplayCurrentWeekDays(@TwoAfterLastWeek)
) calendar
) dates
WHERE dates.sunday IS NOT NULL
OR dates.monday IS NOT NULL
OR dates.tuesday IS NOT NULL
OR dates.wednesday IS NOT NULL
OR dates.thursday IS NOT NULL
OR dates.friday IS NOT NULL
OR dates.saturday IS NOT NULL
RETURN
END
GO
Step
3 : Use recently created UDF to get the current month's calendar. We can pass
any date to this calendar and it will return that month and years calendar.
USE tempdb;
GO
DECLARE @WhatDay SMALLDATETIME
SELECT @WhatDay = GETDATE(); -- you can also put
here any date like 08/20/2016
SELECT ISNULL(CONVERT(NVARCHAR(20), Sunday, 103), '') AS 'Sunday',
ISNULL(CONVERT(NVARCHAR(20), Monday, 103), '') AS 'Monday',
ISNULL(CONVERT(NVARCHAR(20), Tuesday, 103), '') AS 'Tuesday',
ISNULL(CONVERT(NVARCHAR(20), Wednesday, 103), '') AS 'Wednesday',
ISNULL(CONVERT(NVARCHAR(20), Thursday, 103), '') AS 'Thursday',
ISNULL(CONVERT(NVARCHAR(20), Friday, 103), '') AS 'Friday',
ISNULL(CONVERT(NVARCHAR(20), Saturday, 103), '') AS 'Saturday'
FROM dbo.GetSQLcalendar(@WhatDay)
GO
If there is any other better option,
Thank You,
Arka Gupta.
Arka Gupta.
No comments:
Post a Comment