SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fiscal Week in TSQL


Fiscal Week in TSQL

Author
Message
TSQL Tryer
TSQL Tryer
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 708
Hi there,

I have the following script -

SELECT      DATEPART(WW, JOB.created_date) AS [Created Calendar Week], 
DATEPART(MM, JOB.created_date) AS [Created Month],
DATEPART(YYYY, JOB.created_date) AS [Created Year],
JOB.created_date AS [Created Date],
JOB.actual_startdate AS [Start Date],
JOB.completed_date AS [Completed Date],
JOB.resolutiontarget_date AS [Job Target Date],
JOB.ref AS [Job Ref],
JOB.client_ref AS [Client Ref],
USERS.recorded_by AS [Job Creator],
CLI.client_name AS [Client Name],
SIT.property_address1 + ' ' + SIT.property_address2, + ' ' + SIT.property_address3, + ' ' + SIT.property_address4 AS 'Address',
SIT.property_postcode AS 'SitePostcode',
WORKLTYPE.description AS 'WorkLogType',
WORKTYPE.description AS 'WorkType',
PRI.description AS 'Job Priority'




FROM dbo.Job
AS JOB
LEFT OUTER JOIN
(SELECT ID, job_id, event, recorded_by
FROM dbo.JobEvent
WHERE (event = 'Job Created')
) AS USERS
ON JOB.ID = USERS.job_id
INNER JOIN
dbo.Client AS CLI
ON JOB.client_id = CLI.client_id
INNER JOIN
dbo.WorkLog AS WORK
ON JOB.incident_id = WORK.ID
INNER JOIN
dbo.Site AS SIT
ON WORK.SiteID = SIT.SiteID
INNER JOIN
dbo.WorkLogType AS WORKLTYPE
ON WORK.worklogtype_id = WORKLTYPE.ID
INNER JOIN
dbo.WorkType AS WORKTYPE
ON JOB.worktype_id = WORKTYPE.ID
INNER JOIN
dbo.Priority AS PRI
ON JOB.priority_id = PRI.[ID]




WHERE (JOB.created_date >= CONVERT(DATETIME, '2013-04-01 00:00:00', 102))
AND (JOB.created_date <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102))
--AND (JOB.client_id = '1')
AND (JOB.version_status <> 'Closed')
--AND (JOB.ref = 'J04163')


ORDER BY JOB.created_date





The DATEPART(WW, JOB.created_date), , is just pulling back the calander week. Ideally what I want to pull back is the Fiscal week. So as an example week 14 would be week 1 as our financial calandar runs 01/04/13 till 31/03/14.

Thanks
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16166 Visits: 19543
Something to get the ball rolling. There are folks here who love date arithmetic and will come up with something quicker and more elegant, but you get the general idea;

;WITH MySampleData AS (
SELECT MyDate = '20120331' UNION ALL
SELECT MyDate = '20120401' UNION ALL
SELECT MyDate = '20120801' UNION ALL
SELECT MyDate = '20121231' UNION ALL
SELECT MyDate = '20130331' UNION ALL
SELECT MyDate = '20130401' UNION ALL
SELECT MyDate = '20130901' UNION ALL
SELECT MyDate = '20131231' UNION ALL
SELECT MyDate = '20140331' UNION ALL
SELECT MyDate = '20140401' UNION ALL
SELECT MyDate = GETDATE())
SELECT MyDate, FiscalWeek
FROM MySampleData
CROSS APPLY (SELECT OffsetYear = CASE WHEN MONTH(MyDate) < 4 THEN YEAR(MyDate)-1 ELSE YEAR(MyDate) END) x
CROSS APPLY (SELECT FiscalWeek = 1+DATEDIFF(WK,DATEADD(MM,3,CAST(OffsetYear AS CHAR(4))),MyDate)) y




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4974 Visits: 5478
I would highly recommend to employ a Calendar Table for this sort of "calculations".

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 599
Normally we do not need this kind of "calculations". It is a common practice to store financial year date range in some master table, as different countries can have different financial years. So anyways you will need a variable to store this detail, simply store your financial year start_date and end_date with detail.
TSQL Tryer
TSQL Tryer
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 708
I now have a date database, but this still doesn't give me the Fiscal week.

How would I when I create the date table make it so that it creates my fiscal weeks as well.

This was the code I used for my date table which I used from this site -

In order to create the table -
--Create the tables
BEGIN TRY
DROP TABLE [dim_Date]
END TRY
BEGIN CATCH
--DO NOTHING
END CATCH
CREATE TABLE [dbo].[dim_Date](
--[ID] [int] IDENTITY(1,1) NOT NULL--Use this line if you just want an autoincrementing counter AND COMMENT BELOW LINE
[ID] [int] NOT NULL--TO MAKE THE ID THE YYYYMMDD FORMAT USE THIS LINE AND COMMENT ABOVE LINE.
, [Date] [datetime] NOT NULL
, [Day] [char](2) NOT NULL
, [DaySuffix] [varchar](4) NOT NULL
, [DayOfWeek] [varchar](9) NOT NULL
, [DOWInMonth] [TINYINT] NOT NULL
, [DayOfYear] [int] NOT NULL
, [WeekOfYear] [tinyint] NOT NULL
, [WeekOfMonth] [tinyint] NOT NULL
, [Month] [char](2) NOT NULL
, [MonthName] [varchar](9) NOT NULL
, [Quarter] [tinyint] NOT NULL
, [QuarterName] [varchar](6) NOT NULL
, [Year] [char](4) NOT NULL
, [StandardDate] [varchar](10) NULL
, [HolidayText] [varchar](50) NULL
CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO



To populate the table

[--Populate Date dimension
PRINT convert(varchar,getdate(),113) --To see the exact run time.
TRUNCATE TABLE dim_Date

--IF YOU ARE USING THE YYYYMMDD format for the primary key then you need to comment out this line.
--DBCC CHECKIDENT (dim_Date, RESEED, 60000) --In case you need to add earlier dates later.

DECLARE @tmpDOW TABLE (DOW INT, Cntr INT)--Table for counting DOW occurance in a month
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(1,0)--Used in the loop below
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(2,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(3,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(4,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(5,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(6,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(7,0)

DECLARE @StartDate datetime
, @EndDate datetime
, @Date datetime
, @WDofMonth INT
, @CurrentMonth INT

SELECT @StartDate = '1/1/1900'
, @EndDate = '1/1/2050'--Non inclusive. Stops on the day before this.
, @CurrentMonth = 1 --Counter used in loop below.

SELECT @Date = @StartDate

WHILE @Date < @EndDate
BEGIN

IF DATEPART(MONTH,@Date) <> @CurrentMonth
BEGIN
SELECT @CurrentMonth = DATEPART(MONTH,@Date)
UPDATE @tmpDOW SET Cntr = 0
END

UPDATE @tmpDOW
SET Cntr = Cntr + 1
WHERE DOW = DATEPART(DW,@DATE)

SELECT @WDofMonth = Cntr
FROM @tmpDOW
WHERE DOW = DATEPART(DW,@DATE)

INSERT INTO dim_Date
(
[ID],--TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE... Comment for autoincrementing.
[Date]
, [Day]
, [DaySuffix]
, [DayOfWeek]
, [DOWInMonth]
, [DayOfYear]
, [WeekOfYear]
, [WeekOfMonth]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName]
, [Year]
)
SELECT CONVERT(VARCHAR,@Date,112), --TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE COMMENT FOR AUTOINCREMENT
@Date [Date]
, DATEPART(DAY,@DATE) [Day]
, CASE
WHEN DATEPART(DAY,@DATE) IN (11,12,13) THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 1 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 2 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 3 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'
END AS [DaySuffix]
, CASE DATEPART(DW, @DATE)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END AS [DayOfWeek]
, @WDofMonth [DOWInMonth]--Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.
, DATEPART(dy,@Date) [DayOfYear]--Day of the year. 0 - 365/366
, DATEPART(ww,@Date) [WeekOfYear]--0-52/53
, DATEPART(ww,@Date) + 1 -
DATEPART(ww,CAST(DATEPART(mm,@Date) AS VARCHAR) + '/1/' + CAST(DATEPART(yy,@Date) AS VARCHAR)) [WeekOfMonth]
, DATEPART(MONTH,@DATE) [Month]--To be converted with leading zero later.
, DATENAME(MONTH,@DATE) [MonthName]
, DATEPART(qq,@DATE) [Quarter]--Calendar quarter
, CASE DATEPART(qq,@DATE)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS [QuarterName]
, DATEPART(YEAR,@Date) [Year]

SELECT @Date = DATEADD(dd,1,@Date)
END

--You can replace this code by editing the insert using my functions dbo.DBA_fnAddLeadingZeros
UPDATE dbo.dim_Date
SET [DAY] = '0' + [DAY]
WHERE LEN([DAY]) = 1

UPDATE dbo.dim_Date
SET [MONTH] = '0' + [MONTH]
WHERE LEN([MONTH]) = 1

UPDATE dbo.dim_Date
SET STANDARDDATE = [MONTH] + '/' + [DAY] + '/' + [YEAR]

--Add HOLIDAYS --------------------------------------------------------------------------------------------------------------

--CHRISTMAS -------------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET HolidayText = 'Holiday'
WHERE [MONTH] = 12 AND [DAY] = 25

-- New Years Day ---------------------------------------------------------------------------------------------
UPDATE dbo.dim_Date
SET HolidayText = 'Holiday'
WHERE [MONTH] = 1 AND [DAY] = 1


CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])
SELECT [id], [YEAR], [DAY]
FROM dbo.dim_Date
WHERE [MONTH] = 11
AND [Dayofweek] = 'Monday'
ORDER BY YEAR, DAY

DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT

SELECT @CURRENTYEAR = MIN([YEAR])
, @STARTYEAR = MIN([YEAR])
, @ENDYEAR = MAX([YEAR])
FROM #tmpHoliday

WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([YEAR])
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR

SET @POS = 1

WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM #tmpHoliday
WHERE [YEAR] = @CURRENTYEAR
AND [WEEK] IS NULL

UPDATE #tmpHoliday
SET [WEEK] = @POS
WHERE [YEAR] = @CURRENTYEAR
AND [DAY] = @MINDAY

SELECT @POS = @POS + 1
END

SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END

UPDATE DT
SET HolidayText = 'Election Day'
FROM dbo.dim_Date DT
JOIN #tmpHoliday HL
ON (HL.DateID + 1) = DT.ID
WHERE [WEEK] = 1

DROP TABLE #tmpHoliday
GO
--------------------------------------------------------------------------------------------------------



So the financial year would run between the 01/04 to the 31/03 with 01/04 to the 07/04 being week 1 and then the following week 2......

Thanks
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16166 Visits: 19543
Try this, Ryan.

Usage is like this:
SELECT *
FROM dbo.IF_Calendar ('2000-01-01','2013-12-31','monday')

It's hardcoded for your fiscal year.

CREATE FUNCTION [dbo].[IF_Calendar] 
(
@StartDate DATE,
@EndDate DATE,
@FirstWeekDay VARCHAR(10) -- e.g. 'monday'
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
-- inline tally table
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max

iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive
SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))
rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM E3
)

-- Do some date arithmetic
SELECT
a.DateRange,
c.[Year],
c.[Month],
c.[DayOfMonth],
c.AbsWeekno,
c.[DayName],
d.Holiday,
y.FiscalWeek
FROM iTally
CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a
CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)
) b (FirstWeekDay, FirstWeekdayOffset)
CROSS APPLY (
SELECT
[Year] = YEAR(a.DateRange),
[Month] = MONTH(a.DateRange),
[DayOfMonth] = DAY(a.DateRange),
AbsWeekno = DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,
[DayName] = DATENAME(weekday,a.DateRange)
) c
CROSS APPLY (
SELECT Holiday = CASE
WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'
ELSE NULL END
) d
-- Fiscal year begins on 1st April
CROSS APPLY (SELECT OffsetYear = CASE WHEN c.[Month] < 4 THEN c.[Year]-1 ELSE c.[Year] END) x
CROSS APPLY (SELECT FiscalWeek = 1+DATEDIFF(WK,DATEADD(MM,3,CAST(OffsetYear AS CHAR(4))),a.DateRange)) y

WHERE b.FirstWeekDay = @FirstWeekDay
AND @EndDate IS NOT NULL

)




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
TSQL Tryer
TSQL Tryer
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 708
Chris I really appreciate your help but you have lost me a little.

I have run the script you have kindly supplied and can see it has created something under the database under Programmabilty - Functions - Table-valued Functions.

What is this and how do i incorporate it into my current date database.

Sorry I know i'm thick.
TSQL Tryer
TSQL Tryer
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 708
Ignore me - I think I know what I need to do.....I'll come back if I need more help :-0

Thanks Chris
SwePeso
SwePeso
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3993 Visits: 3433
Keep it simple...
DECLARE   @Sample TABLE
(
theDate DATETIME NOT NULL
);

INSERT @Sample
(
theDate
)
SELECT DATEADD(DAY, Number, '20130101')
FROM master.dbo.spt_values
WHERE [Type] = 'P';

-- SwePeso
SELECT theDate,
DATEPART(YEAR, DATEADD(MONTH, -3, theDate)) AS FiscalYear,
(DATEPART(DAYOFYEAR, DATEADD(MONTH, -3, theDate)) + 6) / 7 AS FiscalWeek
FROM @Sample
ORDER BY theDate;




N 56°04'39.16"
E 12°55'05.25"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search