calculation of date duration in QMF

timsek · #1 (**permalink**) 05-04-10, 11:46

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.

Marcus_A · #2 (**permalink**) 05-04-10, 12:37

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.

ARWinner · #3 (**permalink**) 05-04-10, 14:13

Quote:

Originally Posted by Marcus_A

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

Marcus_A · #4 (**permalink**) 05-04-10, 14:25

Quote:

Originally Posted by ARWinner

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.

timsek · #5 (**permalink**) 05-06-10, 02:45

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

tonkuma · #6 (**permalink**) 05-06-10, 09:54

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.

timsek · #7 (**permalink**) 05-06-10, 10:06

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.

tonkuma · #8 (**permalink**) 05-06-10, 17:02

Quote:

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

dav1mo · #9 (**permalink**) 05-07-10, 21:00

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

tonkuma · #10 (**permalink**) 05-08-10, 13:29

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech