# Thread: calculation of date duration in QMF

1. Registered User
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. Registered User
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.

3. Registered User
Join Date
Jan 2003
Posts
4,310
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

4. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
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.

5. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
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. Registered User
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.