the last years I solved tons of "problems" with plain SQL, but
I think this one will require some programming...
a stored procedure, a vb, I don't know ...
Here is the point:
I'm trying to build a simple OLAP-cube out of personal data.
I get table_person with number, name, birthdate, and so on.
I get table_costcenter with costcenternumber and description and
I get a table_person_costcenter with the following structure:
person-number, costcenter, FROM-Date (and some other data, e.g.
workinghours per week(this and headcount are the measures in the cube))
Person 1 is on costcenter CC01 FROM-Date: 01.01.1998
person changed next year
1 CC02 FROM-Date: 01.01.1999
2 CC02 01.05.1999
2 CC03 01.01.2000
and so on....
I can think of loops reading the hole table (roundabout 2000 records),
than the one, two or more records with the person-number 1 and so on
and fill in the missing Dates (every first of a month) BETWEEN the
first FROM-Date to the next FROM-Date and so on till the last record with personnumber X which will lead to CURRENT DATE....
but I never programmed in MS SQL. :-(
So every code examples are heartly wellcome...
many thanks in advance
The result-table should look like this
person costcenter date
1 CC01 01.01.1998 january
1 CC01 01.02.1998 feb
1 CC01 01.03.1998 march
1 CC02 01.01.1999
1 CC02 01.02.1998
.. when no other connections "1" to costcenter ...
1 CC02 01.02.2004 (current month)
The process below which is based on views only has worked for me earlier in similar cases. However, I suspect there are simpler and more efficient ways to do it but as long as your dataset is not very large this approach should work. What you need to do is to create an auxiliary TIME table containing the 1st day of ALL months in the time perriod you want to have in your cube, eg