Creating DB from scripts: means

Showing posts with label means. Show all posts

Monday, March 19, 2012

Creating a queryable failover

Hi,
We have a failover database that is currently run using log shipping,
this means that the fail over is unusable.
What we would like to do is use a tool such as replication to instead
ensure that the fail over database is kept in sync and we can also use
the fail over database to run out reporting jobs.
We investigated SQL Server 2005 and found that with two way replication
this would work wonderfully well except for the fact that we are unable
to replicate new tables without creating a new snap shot.
As our database's are about 150GB in size creating an restoring a
snapshot takes about 2 days for SQL server, meaning that everytime we
add a new table the failover will be unavailable for 2 days, which is
unacceptable for both a fail over and reporting.
Is there any other way we can do this ? Happy to provide more details
if need.
- Barry
barry at 5thfinger dot com
How about database mirroring and creating a snapshot of the mirrored database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<blat001@.gmail.com> wrote in message news:1159315688.269386.201360@.d34g2000cwd.googlegr oups.com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>
|||Barry - a simple way of achieving this setup is to use transactional
replication with queued updating subscribers. This can take care of identity
range issues and provide you with a means of getting new articles easily
incorporated into the setup.
As Tibor mentions, Mirroring and Snapshots is also now to be considered as
an alternative in this space, assuming your reporting requirements don't
include the use of FTI. This has the (huge) added advantage of automatic
failover if you have a witness and high safety mode enabled.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||You might want to add the new articles to a different publication. This way
you would not have to create the entire snapshot. What form of replication
are you using? Merge? Bi-directional transactional replication and peer to
peer do not require the entire snapshot to be regeneretated.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<blat001@.gmail.com> wrote in message
news:1159315688.269386.201360@.d34g2000cwd.googlegr oups.com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>

Creating a queryable failover

Hi,
We have a failover database that is currently run using log shipping,
this means that the fail over is unusable.
What we would like to do is use a tool such as replication to instead
ensure that the fail over database is kept in sync and we can also use
the fail over database to run out reporting jobs.
We investigated SQL Server 2005 and found that with two way replication
this would work wonderfully well except for the fact that we are unable
to replicate new tables without creating a new snap shot.
As our database's are about 150GB in size creating an restoring a
snapshot takes about 2 days for SQL server, meaning that everytime we
add a new table the failover will be unavailable for 2 days, which is
unacceptable for both a fail over and reporting.
Is there any other way we can do this ? Happy to provide more details
if need.
- Barry
barry at 5thfinger dot comHow about database mirroring and creating a snapshot of the mirrored databas
e?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<blat001@.gmail.com> wrote in message news:1159315688.269386.201360@.d34g2000cwd.googlegroups.
com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>|||Barry - a simple way of achieving this setup is to use transactional
replication with queued updating subscribers. This can take care of identity
range issues and provide you with a means of getting new articles easily
incorporated into the setup.
As Tibor mentions, Mirroring and Snapshots is also now to be considered as
an alternative in this space, assuming your reporting requirements don't
include the use of FTI. This has the (huge) added advantage of automatic
failover if you have a witness and high safety mode enabled.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||You might want to add the new articles to a different publication. This way
you would not have to create the entire snapshot. What form of replication
are you using? Merge? Bi-directional transactional replication and peer to
peer do not require the entire snapshot to be regeneretated.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<blat001@.gmail.com> wrote in message
news:1159315688.269386.201360@.d34g2000cwd.googlegroups.com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>

Creating a queryable failover

Hi,
We have a failover database that is currently run using log shipping,
this means that the fail over is unusable.
What we would like to do is use a tool such as replication to instead
ensure that the fail over database is kept in sync and we can also use
the fail over database to run out reporting jobs.
We investigated SQL Server 2005 and found that with two way replication
this would work wonderfully well except for the fact that we are unable
to replicate new tables without creating a new snap shot.
As our database's are about 150GB in size creating an restoring a
snapshot takes about 2 days for SQL server, meaning that everytime we
add a new table the failover will be unavailable for 2 days, which is
unacceptable for both a fail over and reporting.
Is there any other way we can do this ? Happy to provide more details
if need.
- Barry
barry at 5thfinger dot comHow about database mirroring and creating a snapshot of the mirrored database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<blat001@.gmail.com> wrote in message news:1159315688.269386.201360@.d34g2000cwd.googlegroups.com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>|||Barry - a simple way of achieving this setup is to use transactional
replication with queued updating subscribers. This can take care of identity
range issues and provide you with a means of getting new articles easily
incorporated into the setup.
As Tibor mentions, Mirroring and Snapshots is also now to be considered as
an alternative in this space, assuming your reporting requirements don't
include the use of FTI. This has the (huge) added advantage of automatic
failover if you have a witness and high safety mode enabled.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||You might want to add the new articles to a different publication. This way
you would not have to create the entire snapshot. What form of replication
are you using? Merge? Bi-directional transactional replication and peer to
peer do not require the entire snapshot to be regeneretated.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<blat001@.gmail.com> wrote in message
news:1159315688.269386.201360@.d34g2000cwd.googlegroups.com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>

Thursday, March 8, 2012

creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
.mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
DionI would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:
> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > I am trying to use linked servers as a means of accessing files in an
> Access
> > database. I have unsuccessfully tried 2 methods:
> > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> server
> > for Access tables, unless the Access file exists on the same server as
> SQL.
> > This seems odd to me. Is this true? Can I create a linked server for an
> > .mdb file that exists on a different server? And can I do this using
> > sp_addlinkedserver?
> > 2) using OPENROWSET. I get an "Authentication failed" error message with
> > the following:
> > SELECT *
> > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'c:\temp\z.mdb';'';'', Untitled)
> > Do I need to specify some kind of security? What am I missing?
> >
> > My knowledge on security issues is very limited. Any help on getting me
> in
> > the right direction would be appreciated. I am using SQL 8.00.194
> >
> > Thanks
> > Dion
>
>|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
> > I would recommend looking at this thread in the
> > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
We
> > just reviewed it there.
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > I am trying to use linked servers as a means of accessing files in an
> > Access
> > > database. I have unsuccessfully tried 2 methods:
> > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > server
> > > for Access tables, unless the Access file exists on the same server as
> > SQL.
> > > This seems odd to me. Is this true? Can I create a linked server
for an
> > > .mdb file that exists on a different server? And can I do this using
> > > sp_addlinkedserver?
> > > 2) using OPENROWSET. I get an "Authentication failed" error message
with
> > > the following:
> > > SELECT *
> > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > 'c:\temp\z.mdb';'';'', Untitled)
> > > Do I need to specify some kind of security? What am I missing?
> > >
> > > My knowledge on security issues is very limited. Any help on getting
me
> > in
> > > the right direction would be appreciated. I am using SQL 8.00.194
> > >
> > > Thanks
> > > Dion
> >
> >
> >|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:
> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won't
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > So the Access file needs to be either on the local drive, or on the same
> > server as sql, right? Thanks much for the thread.
> >
> > Dion
> >
> >
> > "Simon Worth" wrote:
> >
> > > I would recommend looking at this thread in the
> > > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
> We
> > > just reviewed it there.
> > > --
> > > Simon Worth
> > >
> > >
> > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > I am trying to use linked servers as a means of accessing files in an
> > > Access
> > > > database. I have unsuccessfully tried 2 methods:
> > > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > > server
> > > > for Access tables, unless the Access file exists on the same server as
> > > SQL.
> > > > This seems odd to me. Is this true? Can I create a linked server
> for an
> > > > .mdb file that exists on a different server? And can I do this using
> > > > sp_addlinkedserver?
> > > > 2) using OPENROWSET. I get an "Authentication failed" error message
> with
> > > > the following:
> > > > SELECT *
> > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > Do I need to specify some kind of security? What am I missing?
> > > >
> > > > My knowledge on security issues is very limited. Any help on getting
> me
> > > in
> > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > >
> > > > Thanks
> > > > Dion
> > >
> > >
> > >
>
>|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
> > The .mdb database file must reside on the server. data_source is
evaluated
> > on the server, not the client, and the path must be valid on the server.
> >
> > I didn't try it with the access db on the local drive. I assume that
won't
> > work, as it's counter to what BOL says. But try it out, and see if it
> > works.
> >
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > > So the Access file needs to be either on the local drive, or on the
same
> > > server as sql, right? Thanks much for the thread.
> > >
> > > Dion
> > >
> > >
> > > "Simon Worth" wrote:
> > >
> > > > I would recommend looking at this thread in the
> > > > microsoft.public.sqlserver.dts newsgroup with subject "Access and
SQL".
> > We
> > > > just reviewed it there.
> > > > --
> > > > Simon Worth
> > > >
> > > >
> > > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > > I am trying to use linked servers as a means of accessing files in
an
> > > > Access
> > > > > database. I have unsuccessfully tried 2 methods:
> > > > > 1) using sp_addlinkedserver. I am told that I cannot create a
linked
> > > > server
> > > > > for Access tables, unless the Access file exists on the same
server as
> > > > SQL.
> > > > > This seems odd to me. Is this true? Can I create a linked
server
> > for an
> > > > > .mdb file that exists on a different server? And can I do this
using
> > > > > sp_addlinkedserver?
> > > > > 2) using OPENROWSET. I get an "Authentication failed" error
message
> > with
> > > > > the following:
> > > > > SELECT *
> > > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > > Do I need to specify some kind of security? What am I missing?
> > > > >
> > > > > My knowledge on security issues is very limited. Any help on
getting
> > me
> > > > in
> > > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > > >
> > > > > Thanks
> > > > > Dion
> > > >
> > > >
> > > >
> >
> >
> >

Friday, February 24, 2012

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!!

Sunday, February 19, 2012

Creating a 4-4-5 Time Period table

Hello:

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)