December 3, 2009 at 9:03 am
I need a dates table containing period_nbr and week_within_period_nbr and quarter for the current and previous year
WEEK_NBR LAST_YEAR_WEEK_DATE CURR_YEAR_WEEK_DATE PERIOD_NBR WEEK_WITHIN_PERIODNBR QUARTER
---------------------- ------------------------- ------------------------- ---------------------- _____________________ -------------
1 31-DEC-07 29-DEC-08
2 07-JAN-08 05-JAN-09
3 14-JAN-08 12-JAN-09
4 21-JAN-08 19-JAN-09
5 28-JAN-08 26-JAN-09
6 04-FEB-08 02-FEB-09
7 11-FEB-08 09-FEB-09
8 18-FEB-08 16-FEB-09
9 25-FEB-08 23-FEB-09
10 03-MAR-08 02-MAR-09
11 10-MAR-08 09-MAR-09
where beginning of week is Monday
and the beginning of fiscal year is the last Monday of previous year unless it falls on a Monday, in which case it is the first day of year
PDF Accounting calendars are attached.
December 3, 2009 at 11:58 am
Might I suggest reading this SSC article by Lynn Pettis
https://qa.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
December 3, 2009 at 12:27 pm
Those are helpful. Perhaps someone has script for deriving periods and weeks within those periods.
bitbucket-25253 (12/3/2009)
Might I suggest reading this SSC article by Lynn Pettishttps://qa.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
December 3, 2009 at 2:21 pm
Here is an excellent article on creating a date calendar that you could modify to insert only Mondays and work with that table.
Author Sean Smith, 2009/10/29
http://qa.sqlservercentral.com/scripts/Date/68389/%5B/url%5D
Edited 4:23 PM
OOPS - that reference will only work in SQL 2005 / 2008 since it uses a CTE ..
Sorry did not mean to lead you astray.
December 3, 2009 at 5:00 pm
This might be a beginning to create the date table you requested:
declare @ThisDate datetime;
set @ThisDate = '12/31/07';
DECLARE @Wk_Num INT;
SET @Wk_Num = 1;
DECLARE @C INT;
SET @C = 1
DECLARE @monday DATETIME;
CREATE TABLE #Dates(WK_Num INT,Mondays DATETIME,Required VARCHAR(20))
WHILE @C< 20 -- Sets # of rows inserted into #Dates
BEGIN
SET @ThisDate = dateadd(wk, datediff(wk, 0, @ThisDate), 0) -- Beginning of this week (Monday)
--SELECT @Wk_Num AS 'Wk_Num',@ThisDate AS 'Monday'
INSERT INTO #Dates(Wk_Num,Mondays)
VALUES(@Wk_Num,@ThisDate)
SET @ThisDate = DATEADD(wk,1,@ThisDate)
SET @Wk_Num = @Wk_Num + 1
IF @Wk_Num > 12
SET @Wk_Num = 1
END
UPDATE #DATES SET Required =
RIGHT('0'+ CAST(DATEPART(dd,Mondays) AS VARCHAR(2)),2) + '-'
+ LEFT(DATENAME(mm,Mondays),3) +'-'
+ RIGHT(CAST(DATEPART(yy,Mondays) AS VARCHAR(4)),2)
-- Just to view what is now in the #Dates table
SELECT * FROM #Dates
--Clean up to test modified / expanded code
DROP TABLE #Dates
I believe your review of the article by Lynn Pettis will assist you in creating the necessary year ago values
Hope what is here gets you started
If you need further help post your questions.....
Hope what is here gets you started
December 4, 2009 at 3:07 am
Are you in a position to change the structure of your dates table?
What will happen when you go into a new fiscal year - will you replace the LAST_YEAR_WEEK_DATE with the CURR_YEAR_WEEK_DATE and then recalculate the latter?
December 4, 2009 at 8:03 am
This might be a beginning to create the date table you requested:
Yes that works. Should this be implemented as a View or a table? I will try and post my final query.
I have all flavors of report daily, week-to-date, period-to-date . I would like to be able to just pass the report's business day to the stored procedure and and any other info comes from table.
My boss tells me there has been at least one occasion where a week which was at the end of a period, was in a different period or quarter the following year. I have not verified this but perhaps this sounds familiar to someone.
Simon Liddle (12/4/2009)
Are you in a position to change the structure of your dates table?What will happen when you go into a new fiscal year - will you replace the LAST_YEAR_WEEK_DATE with the CURR_YEAR_WEEK_DATE and then recalculate the latter?
My table will contain prev and curr year date paramaters. So will have to be generated each year. Did i interpret question correctly?
Edited:
I actually have a .NET class that currently implements the dates table logic. If I were at the .NET/SQL Server 2005 integeration capabilities, I'm sure I could create table from class.
December 4, 2009 at 8:39 am
Another approach would be to create your FiscalDates table with every date in it:
CREATE TABLE FiscalDates
(
[Id] INT IDENTITY(1,1),
[Date] DATETIME PRIMARY KEY,
[WeekNumber] INT,
[PeriodNumber] INT,
[Quarter] INT,
[FiscalYear] INT
)
This would allow you to quickly join to the table on the date alone to find what week/period/quarter that date falls in, find the start/end of any period easily, etc. You would also be able to quickly find what period a week fell into in other years and so on.
Does this make sense?
December 4, 2009 at 8:41 am
Good idea.
December 4, 2009 at 10:01 am
Finally got my SQL 2000 back up and running and came up with this.
DECLARE @ThisDate DATETIME;
set @ThisDate = '12/31/07';
DECLARE @NxtDate DATETIME;
DECLARE @Wk_Num INT;
SET @Wk_Num = 1;
DECLARE @C INT;
SET @C = 0
DECLARE @monday DATETIME;
DECLARE @Required VARCHAR(10)
CREATE TABLE #Dates1(WK_Num INT,Mondays DATETIME,Required VARCHAR(10), Fiscal_Year INT)
WHILE @C < 1 -- Sets # of rows inserted into #Dates
BEGIN
--=== Beginning of this week (Monday)
SET @ThisDate = dateadd(wk, datediff(wk, 0, @ThisDate), 0)
--=== Create the requested format
SET @Required = RIGHT('0'+ CAST(DATEPART(dd,@ThisDate) AS VARCHAR(2)),2) + '-'
+ LEFT(DATENAME(mm,@ThisDate),3) +'-' + RIGHT(CAST(DATEPART(yy,@ThisDate) AS VARCHAR(4)),2)
--==== Now insert into the table
INSERT INTO #Dates1(Wk_Num,Mondays,Required,Fiscal_Year)
VALUES(@Wk_Num,@ThisDate,@Required,YEAR(@ThisDate))
SET @ThisDate = DATEADD(wk,1,@ThisDate)
SET @Wk_Num = @Wk_Num + 1
IF @Wk_Num > 52
BEGIN
SET @Wk_Num = 1
END
END
SET @C = 0
CREATE TABLE #Dates2(WK_Num INT,Mondays DATETIME,Required VARCHAR(10), Fiscal_Year INT)
WHILE @C < 1 -- Sets # of rows inserted into #Dates
BEGIN
--=== Beginning of this week (Monday)
SET @ThisDate = dateadd(wk, datediff(wk, 0, @ThisDate), 0)
--=== Create the requested format
SET @Required = RIGHT('0'+ CAST(DATEPART(dd,@ThisDate) AS VARCHAR(2)),2) + '-'
+ LEFT(DATENAME(mm,@ThisDate),3) +'-' + RIGHT(CAST(DATEPART(yy,@ThisDate) AS VARCHAR(4)),2)
--==== Now insert into the table
INSERT INTO #Dates2(Wk_Num,Mondays,Required,Fiscal_Year)
VALUES(@Wk_Num,@ThisDate,@Required,YEAR(@ThisDate))
SET @ThisDate = DATEADD(wk,1,@ThisDate)
SET @Wk_Num = @Wk_Num + 1
IF @Wk_Num > 52
BEGIN
SET @Wk_Num = 1
END
END
SELECT #Dates1.Wk_Num,#Dates1.Required,#Dates2.Required FROM #Dates1
JOIN #Dates2 ON
#Dates1.Wk_Num = #Dates2.Wk_Num
--Clean up
--DROP TABLE #Dates1
--DROP TABLE #Dates2
--A portion of the results:
Last_Year Curr_Year
1 31-Dec-07 29-Dec-08
2 07-Jan-08 05-Jan-09
3 14-Jan-08 12-Jan-09
4 21-Jan-08 19-Jan-09
5 28-Jan-08 26-Jan-09
6 04-Feb-08 02-Feb-09
7 11-Feb-08 09-Feb-09
8 18-Feb-08 16-Feb-09
9 25-Feb-08 23-Feb-09
10 03-Mar-08 02-Mar-09
11 10-Mar-08 09-Mar-09
12 17-Mar-08 16-Mar-09
13 24-Mar-08 23-Mar-09
14 31-Mar-08 30-Mar-09
Disclaimer:
Did not check the above code for starting with a leap year for #Date1 construction.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply