CODEWITHSUNDEEP
sqldb2
online.0227
online.0227

Reputation: 648

average number of days between 2 dates manually

I have 2 tables,

Table_1:

ID    RECENT_PURCHASE OLDEST_PURCHASE
------ --------------- ---------------
     1 12/01/2001      04/20/1999
     2 12/01/2001      08/08/1998
     3 10/06/2001      01/27/1998
     4 08/11/2001      06/13/2000
     6 10/02/2001      05/18/2000

Table_2:

ID    PURCHASE_NUMBER
------ ---------------
     1               2
     2               7
     3               2
     4               3
     6               3

So, what I want to do:

Calculate average number of days between "RECENT_PURCHASE" and "OLDEST_PURCHASE". How I will is that firstly I want to get number of days between "RECENT_PURCHASE" and "OLDEST_PURCHASE" from table_1, and then divide the result by "PURCHASE_NUMBER" from table_2. How can I do this manually?

What I did like,

select id, (H.recent_purchase - H.oldest_purchase) / I.purchase_number) from ~~

but it returned completely incorrect values.. like 10000, which doesn't make sense

Expected result would be like:

ID      AVG_PURCHASE_DATE 
------ ------------- 
1       478
2       173

... and so on

For exmaple, For ID 1:

I used folliwng website https://www.timeanddate.com/date/durationresult.html?m1=04&d1=20&y1=1999&m2=12&d2=01&y2=2001 which calculates number of days between 2 dates, I put "12/01/2001" and "04/20/1999" and it says there are 956 days. dividing that 956 / 2 (since there is 2 for id 1 in table_2) is 478 days.

For exmaple, For ID 2:

I used folliwng website https://www.timeanddate.com/date/durationresult.html?m1=04&d1=20&y1=1999&m2=12&d2=01&y2=2001 which calculates number of days between 2 dates, I put "08/08/1998" and "12/01/2001" and it says there are 1211 days. dividing that 1211 / 7 (since there is 7 for id 2 in table_2) is 173 days.

Upvotes: 0

Views: 70

Answers (1)

McNets
McNets

Reputation: 10827

According to this answer use DAYS(DATE) - DAYS(DATE) to get date diff between two dates.

SELECT ID, DAYS(RECENT_PURCHASE) - DAYES(OLDEST_PURCHASE)
FROM TABLE1

Then use a INNER JOIN with table 2 using ID and divide by PURCHASE_NUMBER

SELECT     TABLE1.ID, 
           (DAYS(RECENT_PURCHASE) - DAYES(OLDEST_PURCHASE)) / PURCHASE_NUMBER
FROM       TABLE1
INNER JOIN TABLE2
ON         TABLE1.ID = TABLE2.ID

Upvotes: 1

Related Questions