Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: calculation of date duration in QMF

    I can do this in SAS, but have not managed it in QMF. I have a database of installed base for my companies various products. i would like to calculate the average time between purchases for each customer, but first i need to calculate the time between distinct purchases of that product for each customer.

    When I do this in excel it΄s a matter of sorting based on customer identifying key, and then installed date and then doing something along the lines of :
    if n+1.key = n.key then n+1.date - n.date else null. so for each set of distinct purchases, i get the duration between each purchase, and then arrive at the average purchase cycle for each customer.

    Can this sort of date time span and then average be done in QMF?

    Thanks. This is my virgin post.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SQL has extensive date arithmetic. Here is an example of calculating the difference between two dates (in days):

    db2 "select hiredate, birthdate, hiredate - birthdate as days_hired_after_birth from emp"

    HIREDATE BIRTHDATE DAYS_HIRED_AFTER_BIRTH
    ---------- ---------- ----------------------
    01/01/1995 08/24/1963 310408.
    10/10/2003 02/02/1978 250808.
    04/05/2005 05/11/1971 331025.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Marcus_A View Post
    SQL has extensive date arithmetic. Here is an example of calculating the difference between two dates (in days):

    db2 "select hiredate, birthdate, hiredate - birthdate as days_hired_after_birth from emp"

    HIREDATE BIRTHDATE DAYS_HIRED_AFTER_BIRTH
    ---------- ---------- ----------------------
    01/01/1995 08/24/1963 310408.
    10/10/2003 02/02/1978 250808.
    04/05/2005 05/11/1971 331025.
    Marcus,
    Subtracting 2 dates in DB2 does not get you the number of days between them it gives you a "date duration" in the format YYYYMMDD. Which is the years (YYYY),months (MM), and days (DD) between the two dates.

    Code:
    -->select date('01/01/2010')-date('01/01/2009') as duration from sysibm.sysdummy1
    
    DURATION
    ----------
        10000.
    Andy

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ARWinner View Post
    Marcus,
    Subtracting 2 dates in DB2 does not get you the number of days between them it gives you a "date duration" in the format YYYYMMDD. Which is the years (YYYY),months (MM), and days (DD) between the two dates.
    Andy
    Thanks for the correction. I should have posted this:

    select hiredate, birthdate, days(hiredate) - days(birthdate) as days_hired_after_birth from emp

    Which will get the number of days between two dates. Obviously, there are other options as well.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2010
    Posts
    3
    Thanks for this, but the question is a bit more complex than that. I want to do the date duration calculation in a set of data, which is sorted by customer number and ascending order of dates, so that if n and n + 1 are the same customer, in such a sorted set, then n+1 duration = the date of the previous line subtracted from the following line. In this way I can calculate the average purchase cycle time.

    Something like this can be done in excel:

    Unique Customer Date of Purchase Time between purchases
    customer A 10-8-1998
    customer A 3-10-2005 2611
    customer b 27-12-1991
    customer b 26-3-1998 2281
    customer b 14-12-1999 628
    customer b 2-3-2006 2270
    customer c 13-3-1998

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you can use LAG OLAP specification, it will be easy.

    But, your DB2 server may be on z/OS, because you are asking in QMF.
    If so, query would be more complicated.

    What DB2 version/release on what platform are you using?

    Here is an example using LAG OLAP specification.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH sample_data
    (Unique_Customer , Date_of_Purchase /*, days_between_purchases*/) AS (
    SELECT 'customer A' , DATE('10.08.1998')            FROM sysibm.sysdummy1 UNION ALL 
    SELECT 'customer A' , DATE('03.10.2005') /*, 2611*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('27.12.1991')            FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('26.03.1998') /*, 2281*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('14.12.1999') /*,  628*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('02.03.2006') /*, 2270*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer c' , DATE('13.03.1998')            FROM sysibm.sysdummy1
    )
    SELECT Unique_Customer , Date_of_Purchase
         , DAYS(Date_of_Purchase)
           -
           DAYS( LAG(Date_of_Purchase) OVER(PARTITION BY Unique_Customer
                                            ORDER BY     Date_of_Purchase) )
           AS days_between_purchases
      FROM sample_data
     ORDER BY
           Unique_Customer
         , Date_of_Purchase;
    ------------------------------------------------------------------------------
    
    UNIQUE_CUSTOMER DATE_OF_PURCHASE DAYS_BETWEEN_PURCHASES
    --------------- ---------------- ----------------------
    customer A      1998-08-10                            -
    customer A      2005-10-03                         2611
    customer b      1991-12-27                            -
    customer b      1998-03-26                         2281
    customer b      1999-12-14                          628
    customer b      2006-03-02                         2270
    customer c      1998-03-13                            -
    
      7 record(s) selected.

  7. #7
    Join Date
    May 2010
    Posts
    3
    I`m using QMF for Windows 8.1 to access our datawarehouse.

    The thing is, we are talking about 1000s of customers. I was expecting to do the initial query, sort on customer identifier and then ascending date order of purchase date, and then save this set before running a second query to do this calculation.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... I was expecting to do the initial query, sort on customer identifier and then ascending date order of purchase date, and then save this set before running a second query to do this calculation.
    I don't know that your datawarehouse(and QMF for Windows 8.1) accept LAG OLAP specification.
    But, if they accept it, try this example:
    Code:
    SELECT Unique_Customer , Date_of_Purchase
         , DAYS(Date_of_Purchase)
           -
           DAYS( LAG(Date_of_Purchase) OVER(PARTITION BY Unique_Customer
                                            ORDER BY     Date_of_Purchase) )
           AS days_between_purchases
      FROM (the initial query /*, sort on customer identifier and then ascending date order of purchase date*/
           ) AS q
     ORDER BY
           Unique_Customer
         , Date_of_Purchase
    ;

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    This can be done by joining the table to itself and nesting it. Try something like(completely untested):
    Code:
    select d.customer, avg(d.days_between)
       from
       (select a.customer
        , days(b.date of purchase) - days(a.date of purchase) as days_between
          from table a
                ,table b
        where a.customer = b.customer
            and a.date of purchase <> b.date of purchase
            and b.date of purchase = (select min(c.date of purchase)
                                                   from table c
                                                where a.customer = c.customer
                                              and a.date of purchase < c.date of purchase)
    
         ) as d
    group by d.customer
    Dave

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If your DB2 server doesn't support LAG OLAP specification,
    you can use other queries depending on suported functionalities.

    Here are some examples.

    Suport MIN OLAP specification
    (e.g. DB2 for LUW 9.1 or earlier):
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH sample_data
    (Unique_Customer , Date_of_Purchase /*, days_between_purchases*/) AS (
    SELECT 'customer A' , DATE('10.08.1998')            FROM sysibm.sysdummy1 UNION ALL 
    SELECT 'customer A' , DATE('03.10.2005') /*, 2611*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('27.12.1991')            FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('26.03.1998') /*, 2281*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('14.12.1999') /*,  628*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer b' , DATE('02.03.2006') /*, 2270*/ FROM sysibm.sysdummy1 UNION ALL
    SELECT 'customer c' , DATE('13.03.1998')            FROM sysibm.sysdummy1
    )
    /**************************************************
    MIN OLAP specification
    **************************************************/
    SELECT Unique_Customer , Date_of_Purchase
         , DAYS(Date_of_Purchase)
           -
           DAYS( MIN(Date_of_Purchase) OVER(PARTITION BY Unique_Customer
                                            ORDER BY     Date_of_Purchase
                                            ROWS BETWEEN 1 PRECEDING
                                                     AND 1 PRECEDING) )
           AS days_between_purchases
      FROM sample_data
     ORDER BY
           Unique_Customer
         , Date_of_Purchase
    ;
    ------------------------------------------------------------------------------
    
    UNIQUE_CUSTOMER DATE_OF_PURCHASE DAYS_BETWEEN_PURCHASES
    --------------- ---------------- ----------------------
    customer A      1998-08-10                            -
    customer A      2005-10-03                         2611
    customer b      1991-12-27                            -
    customer b      1998-03-26                         2281
    customer b      1999-12-14                          628
    customer b      2006-03-02                         2270
    customer c      1998-03-13                            -
    
      7 record(s) selected.

    Suport ROW_NUMBER OLAP specification
    (e.g. DB2 for z/OS Version 9 or DB2 for i V6R1):
    Code:
    WITH
     numbered_data AS (
    SELECT a.*
         , ROW_NUMBER() OVER(PARTITION BY Unique_Customer
                             ORDER BY     Date_of_Purchase) rn
      FROM sample_data a
    )
    SELECT a.Unique_Customer , a.Date_of_Purchase
         , DAYS(a.Date_of_Purchase)
           -
           DAYS(b.Date_of_Purchase)
           AS days_between_purchases
      FROM numbered_data a
      LEFT OUTER JOIN
           numbered_data b
       ON  b.Unique_Customer = a.Unique_Customer
       AND b.rn              = a.rn - 1
     ORDER BY
           a.Unique_Customer
         , a.Date_of_Purchase
    ;


    Suport ORDER BY and FETCH FIRST n ROWS in subquery
    (e.g. DB2 for z/OS Version 9 or DB2 for i V6R1):
    Code:
    SELECT a.Unique_Customer , a.Date_of_Purchase
         , DAYS(a.Date_of_Purchase)
           -
           DAYS(b.Date_of_Purchase)
           AS days_between_purchases
      FROM sample_data a
      LEFT OUTER JOIN
           TABLE
           (SELECT b.*
              FROM sample_data b
             WHERE b.Unique_Customer  = a.Unique_Customer
               AND b.Date_of_Purchase < a.Date_of_Purchase
             ORDER BY
                   b.Date_of_Purchase DESC
            FETCH FIRST 1 ROWS ONLY
            ) b
       ON  0=0
     ORDER BY
           a.Unique_Customer
         , a.Date_of_Purchase
    ;

    No such functionality support. Example 1
    Code:
    SELECT a.Unique_Customer , a.Date_of_Purchase
         , DAYS(a.Date_of_Purchase)
           -
           DAYS(b.Date_of_Purchase)
           AS days_between_purchases
      FROM sample_data a
      LEFT OUTER JOIN
           sample_data b
       ON  b.Unique_Customer  = a.Unique_Customer
       AND b.Date_of_Purchase < a.Date_of_Purchase
     WHERE b.Date_of_Purchase IS NULL
       OR
           (SELECT MAX(c.Date_of_Purchase)
              FROM sample_data c
             WHERE c.Unique_Customer  = a.Unique_Customer
               AND c.Date_of_Purchase < a.Date_of_Purchase
           ) = b.Date_of_Purchase
     ORDER BY
           a.Unique_Customer
         , a.Date_of_Purchase
    ;

    No such functionality support. Example 2
    Code:
    SELECT a.Unique_Customer , a.Date_of_Purchase
         , DAYS(a.Date_of_Purchase)
           -
           DAYS( MAX(b.Date_of_Purchase) )
           AS days_between_purchases
      FROM sample_data a
      LEFT OUTER JOIN
           sample_data b
       ON  b.Unique_Customer  = a.Unique_Customer
       AND b.Date_of_Purchase < a.Date_of_Purchase
     GROUP BY
           a.Unique_Customer
         , a.Date_of_Purchase
     ORDER BY
           a.Unique_Customer
         , a.Date_of_Purchase
    ;
    Last edited by tonkuma; 05-08-10 at 14:33.

Tags for this Thread

Posting Permissions

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