Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Unanswered: How to fill up lacking date entries

    Hi everyone,

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

    so...
    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

    Michael
    PS:
    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)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suspect that your problem can be solved with "plain sql" by using a table of sequential numbers to generate a complete list of dates and then left joining your data to it...

    ...but I'm sorry to say I do not fully understand what you are trying to do. Please make another attempt to explain.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Talking

    I got the solution!

    A colleage from New Zealand got it as follows:

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

    Month
    1.1.1998
    1.2.1998
    1.3.1998
    1.4.1998
    1.5.1998
    ...
    1.12.2004

    Then you create a view which joins your data table to this one using <= operator in join, eg

    CREATE VIEW Data_Step1 AS
    SELECT A.Person, A.CostCentre, B.Time, A.FromDate
    FROM Person_CostCentre A, TIME B
    WHERE A.FromDate <= B.Month

    Then you create another view which picks only a single record for each month and person combination and the largest FromDate:

    CREATE VIEW Data_Step2 AS
    SELECT Person, Time, MAX(FromDate) AS FromDate
    FROM Data_Step1
    GROUP BY Person, Time

    and finally you use view Data_Step2 to pick only a single record from Data_Step1 for each month, person and cost centre combination:

    CREATE VIEW Data_Final AS
    SELECT A.Person, A.CostCentre, B.Time
    FROM Data_Step1 A, Data_Step2 B
    WHERE A.Person = B.Person AND A.Time = B.Time AND A.FromDate = B.FromDate
    *****

    And an other solution came from my colleage in austria:

    *****
    create table perscc

    ( person int,

    cc char,

    ab datetime)



    insert into perscc values (1,'A','1.1.1998')

    insert into perscc values (1,'B','1.1.1999')

    insert into perscc values (1,'C','15.7.1999')

    insert into perscc values (2,'D','7.8.2001')



    -- Timetable
    create table time ( datum datetime primary key(datum))

    declare @d datetime
    set @d='1.1.1998'
    while @d < '31.12.2004'
    begin
    insert into time values (@d)
    set @d=dateadd(d,1,@d)
    end

    -- 1. Part: From to (= von bis)

    Create view pers_cc_von_bis as

    Select person, ab ,

    (Select top 1 dateadd(d,-1,e.ab) from

    (Select person, ab, cc from perscc union Select person, Getdate(), Null from perscc ) e

    where e.ab> perscc.ab and e.person=perscc.person order by e.ab)

    as bis ,cc

    from perscc



    --2. part

    create view pers_cc_einzeldatum as

    Select person, cc , time.datum from pers_cc_von_bis P cross join time where

    time.Datum>=p.ab and time.datum<=p.bis


    -- Result

    Select * from pers_cc_einzeldatum order by person,Datum


    *****

    You'll never be alone!!!

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •