Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating a 4-4-5 Time Period table

Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension.

Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?

Thank you!

Hi desibull,

this only way i though it use "dateadd" function for your issue.

check the sample code as below:

decalre @.dt_startdate datetime

set @.dt_startdate = '2007-01-01'

select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),

dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),

dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),

'next_startdate'=dateadd(day,86@.dt_startdate)

use this method for build date list.

take 'next_startdate' replace the @.dt_startdate.

hoping this can help you.

Best Regrads,

Hunt.

|||

Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.

|||

The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.

Here's the query i came up with. Nothing succedes like brute force!

You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.

Code Snippet

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iDaysInFiscalYear smallint

set @.dtFiscalYEarStart = 'January 1, 2007'

set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart)

set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd)

declare @.Numbers table(Num int, dtTemp smalldatetime)

insert into @.Numbers select 0, @.dtFiscalYEarStart

declare @.i tinyint

set @.i = 0

while @.i < 9

begin

insert into @.Numbers select Num + power(2,@.i)

, Dateadd(d, power(2,@.i), dtTemp) from @.Numbers

set @.i = @.i + 1

end

delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd

select dtTemp

, Num + 1 as FiscalDay

, Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek

, case

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11

else 12

end

as [FiscalMonth]

, case

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3

else 4

end

as FiscalQuarter

, datepart(dy,dtTemp) as CalendarDayOfYear

, Datepart(wk,dtTemp) as CalendarWeekOfYear

, Datepart(m,dtTemp) as CalendarMonth

, Datepart(q,dtTemp) as CalendarQuarter

from @.Numbers

order by Dateadd(d, Num, @.dtFiscalYearStart)

|||

Oh rusag2! I Bow To Thee!!!

I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.

Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.

As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.

Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.

Thanks again!

|||

Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.

Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.

|||

Thanks!

I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.

The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.

Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.

|||

Ok, the first answer was a bit...over the top.

Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:

Code Snippet

--A few variables:

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iTemp int

This is the table we'll populate and return at the end

declare @.tb table(DayOfFiscalYear int identity (1,1)

,CalendarDate smalldatetime

, FiscalWeek int

, FiscalMonth tinyint

, FiscalQuarter tinyint)

--Now, populate our variables:

--This can be any date you choose. We assume that the fiscal year

--begins on the first day of the "fiscal week"

--We explicity populated STart of Fiscal Year and End

set @.dtFiscalYearStart = 'December 31, 2006'

set @.dtFiscalYearEnd = 'December 29, 2007'

set @.iTemp = 0

--Here's the loop to populate our output table:

while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)

begin

insert into @.tb (CalendarDate, FiscalWeek)

select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1

set @.iTEmp = @.iTemp + 1

end

update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5

update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9

update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14

update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18

update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22

update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27

update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31

update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35

update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40

update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44

update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48

update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47

--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.

--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!

--uncomment this for a double-check of week counts

--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth

select * from @.tb

|||

Well rusag2, life is just about the get more interesting.

I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.

Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.

I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.

I will keep you posted.

Thanks a bunch again for your efforts!!

desibull

|||

rusag2:

Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?

Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.

|||

Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.

Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.

|||

I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?

For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.

|||

This is a quesion of "Through" vs. "To"

Just adjust your end date.

Or you may change this:

CalendarDate >= @.dtFiscalYearEnd

to this:

CalendarDate > @.dtFiscalYearEnd

|||

Actually, setting it to >= worked. Somehow it got changed to >.

Your code now is fully functional.

I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!

Thanks!

|||

Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.

Regardless, you are a genius!!

Creating a 4-4-5 Time Period table

Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension.

Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?

Thank you!

Hi desibull,

this only way i though it use "dateadd" function for your issue.

check the sample code as below:

decalre @.dt_startdate datetime

set @.dt_startdate = '2007-01-01'

select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),

dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),

dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),

'next_startdate'=dateadd(day,86@.dt_startdate)

use this method for build date list.

take 'next_startdate' replace the @.dt_startdate.

hoping this can help you.

Best Regrads,

Hunt.

|||

Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.

|||

The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.

Here's the query i came up with. Nothing succedes like brute force!

You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.

Code Snippet

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iDaysInFiscalYear smallint

set @.dtFiscalYEarStart = 'January 1, 2007'

set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart)

set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd)

declare @.Numbers table(Num int, dtTemp smalldatetime)

insert into @.Numbers select 0, @.dtFiscalYEarStart

declare @.i tinyint

set @.i = 0

while @.i < 9

begin

insert into @.Numbers select Num + power(2,@.i)

, Dateadd(d, power(2,@.i), dtTemp) from @.Numbers

set @.i = @.i + 1

end

delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd

select dtTemp

, Num + 1 as FiscalDay

, Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek

, case

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11

else 12

end

as [FiscalMonth]

, case

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3

else 4

end

as FiscalQuarter

, datepart(dy,dtTemp) as CalendarDayOfYear

, Datepart(wk,dtTemp) as CalendarWeekOfYear

, Datepart(m,dtTemp) as CalendarMonth

, Datepart(q,dtTemp) as CalendarQuarter

from @.Numbers

order by Dateadd(d, Num, @.dtFiscalYearStart)

|||

Oh rusag2! I Bow To Thee!!!

I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.

Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.

As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.

Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.

Thanks again!

|||

Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.

Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.

|||

Thanks!

I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.

The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.

Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.

|||

Ok, the first answer was a bit...over the top.

Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:

Code Snippet

--A few variables:

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iTemp int

This is the table we'll populate and return at the end

declare @.tb table(DayOfFiscalYear int identity (1,1)

,CalendarDate smalldatetime

, FiscalWeek int

, FiscalMonth tinyint

, FiscalQuarter tinyint)

--Now, populate our variables:

--This can be any date you choose. We assume that the fiscal year

--begins on the first day of the "fiscal week"

--We explicity populated STart of Fiscal Year and End

set @.dtFiscalYearStart = 'December 31, 2006'

set @.dtFiscalYearEnd = 'December 29, 2007'

set @.iTemp = 0

--Here's the loop to populate our output table:

while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)

begin

insert into @.tb (CalendarDate, FiscalWeek)

select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1

set @.iTEmp = @.iTemp + 1

end

update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5

update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9

update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14

update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18

update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22

update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27

update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31

update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35

update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40

update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44

update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48

update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47

--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.

--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!

--uncomment this for a double-check of week counts

--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth

select * from @.tb

|||

Well rusag2, life is just about the get more interesting.

I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.

Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.

I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.

I will keep you posted.

Thanks a bunch again for your efforts!!

desibull

|||

rusag2:

Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?

Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.

|||

Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.

Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.

|||

I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?

For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.

|||

This is a quesion of "Through" vs. "To"

Just adjust your end date.

Or you may change this:

CalendarDate >= @.dtFiscalYearEnd

to this:

CalendarDate > @.dtFiscalYearEnd

|||

Actually, setting it to >= worked. Somehow it got changed to >.

Your code now is fully functional.

I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!

Thanks!

|||

Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.

Regardless, you are a genius!!

Creating a 4-4-5 Time Period table

Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension.

Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?

Thank you!

Hi desibull,

this only way i though it use "dateadd" function for your issue.

check the sample code as below:

decalre @.dt_startdate datetime

set @.dt_startdate = '2007-01-01'

select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),

dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),

dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),

'next_startdate'=dateadd(day,86@.dt_startdate)

use this method for build date list.

take 'next_startdate' replace the @.dt_startdate.

hoping this can help you.

Best Regrads,

Hunt.

|||

Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.

|||

The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.

Here's the query i came up with. Nothing succedes like brute force!

You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.

Code Snippet

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iDaysInFiscalYear smallint

set @.dtFiscalYEarStart = 'January 1, 2007'

set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart)

set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd)

declare @.Numbers table(Num int, dtTemp smalldatetime)

insert into @.Numbers select 0, @.dtFiscalYEarStart

declare @.i tinyint

set @.i = 0

while @.i < 9

begin

insert into @.Numbers select Num + power(2,@.i)

, Dateadd(d, power(2,@.i), dtTemp) from @.Numbers

set @.i = @.i + 1

end

delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd

select dtTemp

, Num + 1 as FiscalDay

, Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek

, case

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11

else 12

end

as [FiscalMonth]

, case

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3

else 4

end

as FiscalQuarter

, datepart(dy,dtTemp) as CalendarDayOfYear

, Datepart(wk,dtTemp) as CalendarWeekOfYear

, Datepart(m,dtTemp) as CalendarMonth

, Datepart(q,dtTemp) as CalendarQuarter

from @.Numbers

order by Dateadd(d, Num, @.dtFiscalYearStart)

|||

Oh rusag2! I Bow To Thee!!!

I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.

Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.

As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.

Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.

Thanks again!

|||

Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.

Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.

|||

Thanks!

I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.

The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.

Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.

|||

Ok, the first answer was a bit...over the top.

Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:

Code Snippet

--A few variables:

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iTemp int

This is the table we'll populate and return at the end

declare @.tb table(DayOfFiscalYear int identity (1,1)

,CalendarDate smalldatetime

, FiscalWeek int

, FiscalMonth tinyint

, FiscalQuarter tinyint)

--Now, populate our variables:

--This can be any date you choose. We assume that the fiscal year

--begins on the first day of the "fiscal week"

--We explicity populated STart of Fiscal Year and End

set @.dtFiscalYearStart = 'December 31, 2006'

set @.dtFiscalYearEnd = 'December 29, 2007'

set @.iTemp = 0

--Here's the loop to populate our output table:

while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)

begin

insert into @.tb (CalendarDate, FiscalWeek)

select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1

set @.iTEmp = @.iTemp + 1

end

update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5

update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9

update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14

update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18

update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22

update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27

update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31

update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35

update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40

update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44

update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48

update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47

--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.

--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!

--uncomment this for a double-check of week counts

--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth

select * from @.tb

|||

Well rusag2, life is just about the get more interesting.

I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.

Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.

I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.

I will keep you posted.

Thanks a bunch again for your efforts!!

desibull

|||

rusag2:

Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?

Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.

|||

Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.

Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.

|||

I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?

For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.

|||

This is a quesion of "Through" vs. "To"

Just adjust your end date.

Or you may change this:

CalendarDate >= @.dtFiscalYearEnd

to this:

CalendarDate > @.dtFiscalYearEnd

|||

Actually, setting it to >= worked. Somehow it got changed to >.

Your code now is fully functional.

I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!

Thanks!

|||

Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.

Regardless, you are a genius!!

Creating a 4-4-5 Time Dimension

Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension. Is there any script out there that will help me build a Fiscal calendar such as the one described above?

I realize that this is not a direct SSIS question but I figured that some of you might have encountered this situation and hence my post.

Thank you!

Phew. That doesn't sound easy - other than just looping over the weeks I don't see how it could be done. I suggest you post this on the T-SQL forum. I bet there's some people on there that would love to have a go at this.

-Jamie

|||I agree wit Jamie, the T-SQL forum is a better place for that. We have handle those cases in the past via stored procedure, but sorry I don't have a sample now. This a fairly common scenario and I am pretty sure there have to be some sample code out there.|||

Thank you for your response! I will close this one out.

CreateSubscription with a shared schedule

In BOL I read that The valid values for the EventType argument of CreateSubscription are TimedSubscription or SnapshotUpdated.
What if I want to use an existing shared schedule, which in the reportserver database appears as type SharedSchedule? Do I still use TimedSubscription?
And how can I serialize the schedule object in order to get the xml used in CreateSubscription attribute MatchData?
In other words, how do I use an existing shared schedule with CreateSubscription? Are there any other examples, apart from the one in BOL?Try to send the SbuscriptionID of your shared schedule in the matchdata
parameter (without any XML tag)
"vsiat" <vsiat@.discussions.microsoft.com> a écrit dans le message de
news:EF725471-5320-444D-9144-5305E10ADC07@.microsoft.com...
> In BOL I read that The valid values for the EventType argument of
CreateSubscription are TimedSubscription or SnapshotUpdated.
> What if I want to use an existing shared schedule, which in the
reportserver database appears as type SharedSchedule? Do I still use
TimedSubscription?
> And how can I serialize the schedule object in order to get the xml used
in CreateSubscription attribute MatchData?
> In other words, how do I use an existing shared schedule with
CreateSubscription? Are there any other examples, apart from the one in BOL?
>|||For a subscription to use a shared schedule, pass in TimedSubscription for
the eventType and the schedule ID for the match data. You can get the
schedule ID by calling listSchedules.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:OL8Xs1dXEHA.1048@.tk2msftngp13.phx.gbl...
> Try to send the SbuscriptionID of your shared schedule in the matchdata
> parameter (without any XML tag)
>
> "vsiat" <vsiat@.discussions.microsoft.com> a écrit dans le message de
> news:EF725471-5320-444D-9144-5305E10ADC07@.microsoft.com...
> > In BOL I read that The valid values for the EventType argument of
> CreateSubscription are TimedSubscription or SnapshotUpdated.
> >
> > What if I want to use an existing shared schedule, which in the
> reportserver database appears as type SharedSchedule? Do I still use
> TimedSubscription?
> >
> > And how can I serialize the schedule object in order to get the xml used
> in CreateSubscription attribute MatchData?
> >
> > In other words, how do I use an existing shared schedule with
> CreateSubscription? Are there any other examples, apart from the one in
BOL?
> >
>

CreateSubscription - Specify the Job Name or Get the GUID Job Name

We're using the CreateSubscription method to schedule a report for automatic delivery, using SQL Server Agent. Everything is working great. But, we had a question (or two).

Is there anyway to specify the name of the SQL Server Agent job that gets created when the CreateSubscription method is called? If so, how?
If not, is there anyway to get the GUID job name back after calling CreateSubscription?

TIA

There is no way to set the name of the SQL Agent job via the CreateSubscription method.

You can use the ListSubscriptions to get the guid and GetSubscriptionProperties to get further information.

ReportingService2005 rs = new ReportingService2005();
Subscription[] subscription = rs.ListSubscriptions(ReportAndPath, UserName);

rs.GetSubscriptionProperties(

subscription[0].SubscriptionID,

out actualExtensionSettings,

out actualDescription,

out actualActive,

out actualStatus,

out actualEventType,

out actualMatchData,

out actualParameters);

|||

Brad,

Thanks for the info. However, does using the ListSubscriptions and GetSubscriptionProperties give me the GUID name of the SQL Server Agent job?

Since there is no way to set the SQL Server Agent job to something more meaningful to end-users, the next best option for us is to provide the GUID name of the SQL Server Agent job to the end-user after is has been created. Thus, our ASP.NET app. will display to the end-user something like, "Your job has been created. The job name is xxxx." (where xxxx is the GUID job name as shown in the SQL Server list of jobs). In our situation, our end-users are 'knowledgeable' enough about our product to open SQL Server, navigate to SQL Server Agent, and find the job they just tried to create. So, we need to be able to give them some help with which job name is theirs.

Thanks.

|||

No. The guid for the SQL Agent job is not exposed through the SOAP Api's.

Why not just give them the subscription information? Instead of trying to tell them the SQL Agent Job, tell them the subscription name. "Your subscription has been created. The subscription is on "ReportX" for user "Foo" and is scheduled to send at "Time Selected". In Management Studio or Report Manager, more information is stored for the Subcription in RS then for the Job in the Agent. They can get parameter values, security info, and other information.

Just a thought.

Created .dtsx file How do I schedule it?

How dow you schedule a file(.dtsx)(I think it is a package) that was created in the SQL Server BI Design Studio to run? I can locate the file I created in SQL Server Management Studio and see it in the right hand pane and even edit it. I can run it in the Design Studio, but cant see how to do that in Management Studio nor how to schedule it.

This is my first attempt at recreating a DTS package using SQL Server tools instead of the Legacy conversion wizard, and I think my brain has overheated. I just cant find it on the Management Screen or the Design. I am also not searching correctly anymore on books online because I cannot find it.

Thanks for pointing me to where I need to go or do.

Tracey

You can run it from a SQL Agent job, or from the command line with DTEXEC or DTEXECUI (which has a gui, and can be useful for getting the appropriate options to execute the package).|||You can schedule a package to execute by creating a SQL Agent job that calls the job. If you're familar with DTS, it's similar but better as all the features of DTEXECUI are available to you when you create the step.|||

Thank you so much. This is exactly what I needed. I think I had just gotten myself too flustered and you cleared it all up.