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

JB1976 · #1 (**permalink**) 02-23-11, 02:49

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.

tonkuma · #2 (**permalink**) 02-23-11, 08:02

I think totaldays of second row is 17.

Quote:

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

tonkuma · #3 (**permalink**) 02-23-11, 09:23

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech