July 29, 2013 at 5:02 am
Hi there,
I have the following script -
SELECTDATEPART(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
July 29, 2013 at 6:09 am
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
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
July 29, 2013 at 6:22 am
I would highly recommend to employ a Calendar Table for this sort of "calculations".
July 29, 2013 at 11:20 pm
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.
July 30, 2013 at 5:19 am
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
July 30, 2013 at 6:23 am
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
)
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
July 30, 2013 at 6:32 am
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.
July 30, 2013 at 6:34 am
Ignore me - I think I know what I need to do.....I'll come back if I need more help :-0
Thanks Chris
July 31, 2013 at 2:34 pm
Keep it simple...DECLARE@Sample TABLE
(
theDate DATETIME NOT NULL
);
INSERT@Sample
(
theDate
)
SELECTDATEADD(DAY, Number, '20130101')
FROMmaster.dbo.spt_values
WHERE[Type] = 'P';
-- SwePeso
SELECTtheDate,
DATEPART(YEAR, DATEADD(MONTH, -3, theDate)) AS FiscalYear,
(DATEPART(DAYOFYEAR, DATEADD(MONTH, -3, theDate)) + 6) / 7 AS FiscalWeek
FROM@Sample
ORDER BYtheDate;
N 56°04'39.16"
E 12°55'05.25"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply