Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Question Unanswered: how to convert from line item date ranges to a monthly fact table

    Hello,

    So this one is way beyond my abilities so thought id see if you guys could give me some pointers!
    I've got a table that looks like this...

    ID,NAME,SDATE,EDATE,TOTALDAYS,WEEKDAYS
    1,James,01/01/2010,10/01/2010,10,6
    2,John,03/02/2010,19/02/2010,16,13
    1,James,15/04/2010,23/07/2010,100,72
    3,Chris,05/08/2010,05/08/2010,1,1
    2,John,30/08/2010,03/09/2010,5,5
    3,Chris,28/09/2010,06/10/2010,9,7

    I want it in a format thats more like this...

    YEAR,MONTH,ID,NAME,TOTALDAYS,WEEKDAYS
    2010,JAN,1,James,10,6
    2010,FEB,2,John,16,13
    2010,APR,1,James,16,12
    2010,MAY,1,James,31,21
    2010,JUN,1,James,30,22
    2010,JUL,1,James,23,17
    2010,AUG,3,Chris,1,1
    2010,AUG,2,John,2,2
    2010,SEP,2,John,3,3
    2010,SEP,3,Chris,3,3
    2010,OCT,3,Chris,6,4

    As you can see i need to look at the date ranges in the source table and somehow get a new road for each month covered as well as caculating days during month within period as well was the week days.

    Any ideas on how to achieve this???

    jez.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think totaldays of second row is 17.

    ID,NAME,SDATE,EDATE,TOTALDAYS,WEEKDAYS
    1,James,01/01/2010,10/01/2010,10,6
    2,John,03/02/2010,19/02/2010,16,13
    ...

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example worked on DB2 9.7 for Windows.
    (I want to find simpler expression for weekdays.)
    Code:
    WITH
    /**************************************************
    ********** Start of sample data.         **********
    **************************************************/
    table_a
    (ID,NAME,SDATE,EDATE,TOTALDAYS,WEEKDAYS) AS (
    SELECT ID
         , NAME
         , DATE(SDATE) AS sdate
         , DATE(EDATE) AS edate
         , TOTALDAYS
         , WEEKDAYS
     FROM  (VALUES
              (1 , 'James' , '01.01.2010' , '10.01.2010' ,  10 ,  6)
            , (2 , 'John'  , '03.02.2010' , '19.02.2010' ,  16 , 13)
            , (1 , 'James' , '15.04.2010' , '23.07.2010' , 100 , 72)
            , (3 , 'Chris' , '05.08.2010' , '05.08.2010' ,   1 ,  1)
            , (2 , 'John'  , '30.08.2010' , '03.09.2010' ,   5 ,  5)
            , (3 , 'Chris' , '28.09.2010' , '06.10.2010' ,   9 ,  7)
           ) t(ID,NAME,SDATE,EDATE,TOTALDAYS,WEEKDAYS)
    )
    /**************************************************
    **********   End of sample data.         **********
    **************************************************/
    
    SELECT YEAR (sdate) AS year
         , SUBSTR( MONTHNAME(sdate , 'CLDR 1.5:en_US') , 1 , 3 ) AS month
         , id , name
         , DAYS(edate) - DAYS(sdate) AS totoaldays
         , ( DAYS(edate) - DAYOFWEEK_ISO(edate)
           - DAYS(sdate) + DAYOFWEEK_ISO(sdate)
           ) / 7 * 5
           + MIN( DAYOFWEEK_ISO(edate) , 6 )
           - MIN( DAYOFWEEK_ISO(sdate) , 6 )
           AS weekdays
     FROM  (SELECT n
                 , id , name
                 , MAX( sdate - (DAY(sdate) - 1) DAYs + n MONTHs
                      , sdate )         AS sdate
                 , MIN( LAST_DAY(sdate + n MONTHs)
                      , edate ) + 1 DAY AS edate
             FROM  table_a
             INNER JOIN
                   (VALUES 0,1,2,3,4,5,6,7,8,9,10,11) AS p(n)
               ON  n <= ( YEAR (edate) - YEAR (sdate) ) * 12
                        + MONTH(edate) - MONTH(sdate)
           ) q
     ORDER BY
           sdate
    ;
    ------------------------------------------------------------------------------
    
    YEAR        MONTH ID          NAME  TOTOALDAYS  WEEKDAYS   
    ----------- ----- ----------- ----- ----------- -----------
           2010 Jan             1 James          10           6
           2010 Feb             2 John           17          13
           2010 Apr             1 James          16          12
           2010 May             1 James          31          21
           2010 Jun             1 James          30          22
           2010 Jul             1 James          23          17
           2010 Aug             3 Chris           1           1
           2010 Aug             2 John            2           2
           2010 Sep             2 John            3           3
           2010 Sep             3 Chris           3           3
           2010 Oct             3 Chris           6           4
    
      11 record(s) selected.
    Last edited by tonkuma; 02-26-11 at 00:06. Reason: Add 1 DAY to edate in subquery.

Posting Permissions

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