Custom time period - 26th of month to 25 of next month

How do I create a server time dimension which has custom defined month periods.

For example we use month that starts on 26 and ends on 25 next month.

So our January is actualy starting from 26.Dec.2006 until 25.Jan.2007,

and so on for Feb, Mar...

Any idea how I can do it?

Thank you,

Mitja

Hello! Create a new column in your time dimension called CustomMonth.

Update TimeDim

Set CustomMonth = 'Jan'

Where CalendarDate between '2006-12-26' and '2007-12-25'

--

Continue with the next month.

This is a very simple solution. It possible to make it generic but that will require more thinking.

HTH

Thomas Ivarsson

|||

Thank you Thomas!

I have tried the proposed solution,

but I get syntax error at "where" critieria.

I was looking for some pre-made solution in business intelligence wizards,

strangely enough to find there isn't one.

Any ideas?

|||

This is an example that works with the AdventureWorksDW sample database:

Alter Table DimTime

Add SpecialMonth Char(3)

Select * from DimTime

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25' --365 Check the no of records that will be updated

-

Update DimTime

Set SpecialMonth = 'Jan'

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25'

-

It is possible to build a full generic solution but it will take some time. You can use TSQL CASE for that.

This will help you to get started.

Regards

Thomas Ivarsson

custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?
Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.
|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP

custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP
--

custom identity field

I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
--
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
--
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
--
David Portas
SQL Server MVP
--