Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Interesting challenge ...

  1. #1

    Exclamation Interesting challenge ...

    Hello everyone!

    This is me asking for help again!

    In the source system, I have:
    pk_code, start_date, end_date
    Where:
    pk_code indicates a person.
    start_date indicates the date on which that person left.
    end_date indicates the date on which such person returned.

    In fact table I have:
    k_timeDimension_key, k_PersonCodeDimension_key, nuDays.
    Where:
    k_timeDimension_key indicates key dimension of time that has month as grain.
    k_PersonCodeDimension_key indicates the key to the dimension of person.
    nuDays indicates how many days in the month the person was out.

    So my problem: I need to turn a record of the source on N records, each for a month in which the person was absent.

    Example (date is dd/mm/yyyy):

    System Source( pk_code, start_date, end_date):
    035444, 01/ 01/2008, 03/03/2008

    In this case, I would have three records in fact table from a record of the source system, with date (MM / YYYY) and number of days outside the month.

    01/2008, 31 days,
    02/2008, 28 days,
    03/2008, 3 days.

    Steps? Javascript? SQL? All included?I have no idea of where to start with the problem. Suggestions are veeeeryyyyyy welcome!



    Léo!

  2. #2
    Join Date
    Jun 2007
    Posts
    112

    Default If you had an extra table...

    If you had a table with one row per date (here's a link to create that with MS SQL Common Table Expressions) you could join your source table to the days table and then count up the rows. Here's some pseudocode:

    select s.pk_code, month(d.date), count(*)
    from source s inner join dates d
    on d.date >= s.start_date and d.date <= s.end_date
    group by s.pk_code, month(d.date)

    Maybe this will spark your creativity. Good luck!

  3. #3
    Join Date
    Sep 2007
    Posts
    834

    Default

    Here you have ...
    enjoy
    mc
    Attached Files Attached Files
    Last edited by Maria Roldan; 08-03-2009 at 07:05 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.