Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fiscal Week in TSQL Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 5:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:17 AM
Points: 185, Visits: 384
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
Post #1478527
Posted Monday, July 29, 2013 6:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1478543
Posted Monday, July 29, 2013 6:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1478548
Posted Monday, July 29, 2013 11:20 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:57 AM
Points: 715, Visits: 519
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.
Post #1478825
Posted Tuesday, July 30, 2013 5:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:17 AM
Points: 185, Visits: 384
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
Post #1478932
Posted Tuesday, July 30, 2013 6:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1478949
Posted Tuesday, July 30, 2013 6:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:17 AM
Points: 185, Visits: 384
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.
Post #1478956
Posted Tuesday, July 30, 2013 6:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:17 AM
Points: 185, Visits: 384
Ignore me - I think I know what I need to do.....I'll come back if I need more help :-0

Thanks Chris
Post #1478957
Posted Wednesday, July 31, 2013 2:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369
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"
Post #1479697
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse